Log File Sync Wait event in Oracle

I will explain Log File Sync Wait event in Oracle in this post.

 

 

Log File Sync in Oracle

When a user session commits, all redo records generated by that session’s transaction need to be flushed from memory to the redo logfile to insure changes to the database made by that transaction become permanent.

At the time of commit, the user session will post LGWR to write the log buffer (containing the current unwritten redo, including this session’s redo records) to the redo log file. Once LGWR knows that its write requests have completed, it will post the user session to notify it that this has completed. The user session waits on ‘log file sync’ while waiting for LGWR to post it back to confirm all redo it generated have made it safely onto disk.

The time between the user session posting the LGWR and the LGWR  posting the user after the write has completed is the wait time for ‘log file sync’ that the user session will show.

 

This event is known as the time lost as a result of the LGWR process waiting while users initiate a Transaction Commit or Rollback.

If this wait event is available continuously, I/O performance of the LGWR process is probably poor, or Commit is coming too often by the application.

 

The solution to this problem is not to commit too much, if necessary, and to examine the I/O performance of the disk on which the Redo log files are located, and to use a high performance disk such as an SSD disk if necessary.

 

 

 

You can find the sql statements that are waiting on log file sync’ wait event using the following script.

select v.sql_text,v.sql_fulltext,sub.* from gv$sql v,
(select sample_time,s.sql_id sql_id, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,s.program,s.module,s.machine
from dba_hist_active_sess_history s, dba_objects o
where sample_time between
to_date('02/08/2021  07:30:02','DD/MM/YYYY HH24:MI:SS')
and
to_date('02/08/2021 15:10:02','DD/MM/YYYY HH24:MI:SS')
and event = 'log file sync'
and o.data_object_id = s.current_obj#
order by 1 desc) sub where sub.sql_id=v.sql_id;

 

 

Waits for the ‘log file sync’ event can occur at any stage between a user process posting the LGWR to write redo information and the LGWR posting back the user process after the redo has been written from the log buffer to disk (local redo logs and optionally propagated remote standby databases in SYNC mode) and the user process waking up to receive the post or poll that LGWR has written the info as requested.

 

 

 

 

Read the following post to learn more details about DB File Sequential and Scattered Read Wait event in Oracle.

DB File Sequential Read Wait event in Oracle

 

DB File Scattered Read Wait event in Oracle

 

 

 

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database