Waits event “log file sync” caused performance issue Oracle

Waits event “log file sync” caused performance issue Oracle

Suppose application is executing the block load operation and having frequently commit operation which caused the log file sync wait event. To overcome this problem application team need to change the code but if its product from third party they want to change it in next release then oracle provide temporary solution to change the commit behavior with parameter COMMIT_WRITE, COMMIT_LOGGING & COMMIT_WAIT. But it’s not recommended to change these parameter.

Problem: Waits on event “log file sync” and Wait Class “Commit” in Oracle.

Caused: Wait class “Commit” was consuming significant database time. (90% impact [21990 seconds])

Solution:

For Release 11.1 or lower version
Parameter COMMIT_WRITE having both value ‘{IMMEDIATE | BATCH},{WAIT |NOWAIT}’

alter system set commit_write ='IMMEDIATE,WAIT'; (default behavior)
alter session set commit_write ='IMMEDIATE,WAIT';
-- For change the commit behavior and fixed performance issue caused due to commit operation.
alter system set commit_write='BATCH,NOWAIT';
alter session set commit_write='BATCH,NOWAIT';

For Release 11.2 or higher version
Two Parameter is used separately COMMIT_WAIT and COMMIT_LOGGING
Note: Parameters were introduced in Oracle 11g release 2 as a replacement for the commit_write parameter.

--- At system level
alter system set commit_logging=batch;
alter system set commit_logging=immediate; -- default
-- At session Level
alter session set commit_logging=batch;
alter session set commit_logging=immediate; --default

-- At system level
alter system set commit_wait=nowait;
alter system set commit_wait=wait;   -- default
alter system set commit_wait=force_wait;
-- At Session level
alter session set commit_wait=nowait;
alter session set commit_wait=wait;    --default
alter session set commit_wait=force_wait;

Explain the values: ‘{IMMEDIATE | BATCH},{WAIT |NOWAIT}’
• IMMEDIATE – The commit process start LGWR process by sending a message, so that the redo is written immediately to the redo logs files at disk.(Default behavior)
• BATCH – Oracle Write the redo log data in batches, caused less traffic but instance crash may caused loss of data and violate ACID property of database.
• WAIT – The commit command is synchronous. It doesn’t return until the relevant redo information is written to the online redo log files.(Default behavior)
• NOWAIT – The commit command is asynchronous. It doesn’t wait for LGWR process to write each transaction on redo log files, It may return before the relevant redo information is written to the disk.
In 11g due to parameter change, One new parameter is introduced:
• FORCE_WAIT, the behavior is very similar to the wait setting. This causes ALL transactions in ALL session to wait for the LGWR I/O to complete.

Default Commit Behavior
By default when user issues commit statement LGWR background process is triggered to write redo entries to disk, I/O operation completed. Only after the all redo information for current transaction will be written to disk, call will be returned to the user with “Commit complete”.
These parameters used to controls the transactions commit behavior.
If BATCH NOWAIT combination will be used, then redo entries buffered in memory will be written to disk later, no I/O will be forced, but call will be returned to user immediately.

Note: Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s