Wait Event : Log File Sync

 What Exactly Is A 'Log File Sync' Wait event?

------------------------------------------------

Log file sync wait event, which in many cases is caused by poor storage performance.

user session issuing a commit command must wait until the LGWR (Log Writer) process writes the log entries associated with the user transaction to the log file on the disk.

Oracle must commit the transaction’s entries to disk (because it is a persistent layer) before acknowledging the transaction commit.

 

At commit time, a process creates a redo record (containing commit opcodes) and copies that redo record into the log buffer. Then, that process signals LGWR to write the contents of log buffer. LGWR writes from the log buffer to the log file and signals user process back completing a commit. A commit is considered successful after the LGWR write is successful.

 

 

A commit is not complete until LGWR writes the log buffers, including the commit redo records to the log files. In a nutshell, after posting LGWR to write, user or background processes wait for LGWR to signal back with a 1-second timeout. The User process charges this wait time as a ‘log file sync’ event.

 

The log file sync wait event represents the time the session is waiting for the log buffers to be written to disk.

 

For example, the following user transaction consists of Insert, Select and Update statements, and completes with a commit:

User Transaction:

Insert…

Select…

Update…

Commit…

Root Causes for 'Log File Sync' Waits

---------------------------------------------------

LGWR is unable to complete writes fast enough for one of the following reasons:

Disk I/O performance to log files is not good.

LGWR is starving for CPU resource. If the server is very busy, LGWR can starve for CPU too. 

LGWR is unable to complete writes fast enough due to file system or Unix buffer cache limitations.

Guidelines For Resolving 'Log File Sync' Waits

----------------------------------------------

If CPU starvation is an issue, then reducing CPU starvation is the correct step to resolve it.

If commit rate is higher, then decreasing commits is correct step but, in a few cases, if that is not possible, increasing the priority of LGWR (using nice) or increasing the priority class of LGWR to RT might provide some relief.

Solid State Disk devices also can be used if the redo size is extreme. in some case , it is also preferable to decrease redo size.

check redo switch per hours , normally it should be 4-5 switch per hours , Sometimes redo logs are very less in size , Oracle recommends, maximum 8 switch per hour .It may be change as per environment to environment  . 

User commit %  and user rollback % can be seen from AWR report which could help to troubleshoot the issue . 

If Redo logs are undersized or huge size both are not good , so tune properly. 

Test in Non-Prod before do any changes in production .