When a user session (the foreground process) commits (or rolls back), the session’s redo information needs to be brushed into the redo log file. The user session will use LGWR to write all the required redo requests in the log buffer to the redo log file. When LGWR is complete, it notifies the user of the process. The user session will have this wait event while waiting for LGWR to post it back to confirm that all redo changes are safely on disk.

In other words, the time the user session/foreground process spends waiting for a refresh redo to make the COMMIT wait longer. Therefore, we can think of these waits as commit delays from the foreground process (or the usual commit client).

More wait events may indicate that LGWR is writing inefficiently or that the system commits (rolls back) too often. This typically occurs on OLTP systems.

The wait that occurs within its own scope

  1. See the “Log File Sync” parameter in the dynamic performance view V$SESSION_WAIT

P1 = buffer#

P2 = not used / sync scn

P3 = not used

Note:

BUFFER # : All changes to this buffer number (in the log buffer) must be flushed to disk and confirmed to write to ensure that the transaction has committed and will remain committed in the event of an instance crash. So wait for LGWR to refresh to this buffer#.

Sync SCN: 10.2.0.5.0 and later. Represents the basis for SCN values that need to be synchronized to disk. Wait for LGWR to refresh the SCN value.

  1. Waiting time

Waiting is entirely dependent on LGWR writing out to the necessary redo block and confirming completion back to the user session. The wait time includes writes to the log buffer and posts. In some versions, the waiter will time out and increase the serial number every once in a while while waiting (the interval was 1 second in the old version, the game ADAPTS in the new version).

  1. Looking for “Blockers”

If the SESSION continues to wait on the same BUFFER #, the SEQ# column of V$SESSIONwww.cungun.comIT may increase, depending on the wait scheme being used. If the BUFFER # value has not changed, you need to check to see what LGWR is waiting for, as it may be stuck. Also check that the wait process is still alive.

For system-wide wait generation

The system-wide number of waits on ‘Log File Sync’ shows the time spent waiting for the commit (or rollback) to complete. If this is important, then LGWR may have a problem. Also need attention:

  1. LGWR waits on “log file parallel write”
  2. “User Commit” and “User Rollback” statistics to see the number of commits being issued and rollbacks.

Ways to reduce waits and wait times

There are five ways to reduce the wait time for this event:

  1. Adjust LGWR for better disk throughput. For example, do not put redo logs on RAID 5, as RAID 5 can incur a significant performance penalty for systems that write heavily.
  2. If there are many small transactions, see if you can group the transactions together to reduce the number of different Commit operations. Each commit must ensure that the associated REDO is brushed to disk. Although Oracle can “piggybacked” a commit, batching a transaction to reduce the total number of commits can be very effective.
  3. Check to see if any of the processes can be used with the Commit Nowait option (but be sure to understand its semantics before using it).
  4. See if you can safely complete any operations using the Nologging/Unrecoverable option.
  5. Check that the redo log is large enough. Increase the redo log to switch between 15 and 20 minutes.

In addition, the total wait time for log file synchronization can be broken down into the following categories:

  1. Wake up LGWR when idle
  2. LGWR collects the redo operations to be written and issues the I/O request
  3. Log write I/O completion time
  4. LGWR I/O goes to work
  5. LGWR notifies the foreground/user session that the write is complete
  6. Front/user session wake up