Waits event “log file sync” caused performance issue Oracle

Suppose the application is performing block load operations and frequently committing, which causes log file sync wait events. To fix this, the application team needs to modify the code, but if it’s from a third party, they plan to change it in the next release. Oracle offers a temporary solution to adjust commit behavior using the parameters COMMIT_WRITE, COMMIT_LOGGING, and COMMIT_WAIT, though changing these parameters is not recommended.

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 parameters, COMMIT_WAIT and COMMIT_LOGGING, are used separately.
Note: These parameters were added in Oracle 11g release 2 to replace 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.