Log file sync wait

Time taken to flush the log buffer ie copy from log buffer to log file at disk.

When a user session(foreground process) COMMITs (or rolls back), the session's redo information needs to be flushed to the redo logfile. The user session will post the LGWR to write all redo required from the log buffer to the redo log file. When the LGWR has finished it will post the user session. The user session waits on this wait event while waiting for LGWR to post it back to confirm all redo changes are safely on disk.

This may be described further as the time user session/foreground process spends waiting for redo to be flushed to make the commit durable. Therefore, we may think of these waits as commit latency from the foreground process (or commit client generally).

• Log file sync flow is as under:

• Foreground process posts LGWR and goes to sleep
• the "log file sync" wait starts
• posting is done via a semaphore operation on unix

• LGWR wakes up and gets onto CPU
• issue the IO requests
• LGWR goes to sleep waiting for "log file parallel write" wait

• Hardware comples the IO and OS wakes up LGWR
• LGWR gets into CPU
• Marks "log file parallel write" event complete and post the foreground process

• Foreground process is woken up by LGWR posts
• Foreground process gets into CPU and completes the "log file sync" waits

 If you log file sync is in top 5 events,. find out the time spend in log file parallel write. Usually log file parallel write takes up significant time of log file sync waits. Other components like scheduling latency, IPC etc are small.