Log file sync log wait

Time:2021-10-18

When the user session (foreground process) commits (or rolls back), the redo information of the session needs to be brushed into the redo log file. The user session will use LGWR to write all redo requests required in the log buffer to the redo log file. When the LGWR completes, it will notify the user of the process. This wait event will occur in the user session while waiting for LGWR to send it back to confirm that all redo changes are safely on disk.

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

The more waiting events, the lower write efficiency of LGWR or the more frequent system commit (rollback). It usually occurs on OLTP systems.

For waiting generated within its own scope

  1. View dynamic performance view v $session_ “Log file sync” parameter involved in 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 the write confirmed to ensure that the transaction has been committed and will remain committed when the instance crashes. So wait for LGWR to refresh to this buffer #.

Sync SCN: 10.2.0.5.0 and later will be used. Represents the base of the SCN value that needs to be synchronized to disk. Wait for LGWR to refresh the SCN value.

  1. waiting time

Wait for the LGWR to write out to the necessary redo block and confirm completion and return to the user session. The waiting time includes writing to the log buffer and post. In some versions, the waiter times out and increases the serial number at regular intervals when waiting (the interval in the old version is in 1s, and in the new version is in 1s)gameAdaptive).

  1. Looking for “blockers”

If the session continues to wait on the same buffer # then the seq# column of V $sessionwww.cungun.comit may increase, depending on the waiting scheme used. If the buffer # value does not change, you need to check what the LGWR is waiting for, because it may be stuck. Also check that the waiting process is still alive.

For system wide wait

The system wide number waiting on “log file sync” shows the time spent waiting for the commit (or rollback) to complete. If this is important, there may be a problem with LGWR. Attention should also be paid to:

  1. Another waiting event for LGWR is “log file parallel write”
  2. Statistics of “user commit” and “user rollback” to view the number of committed and rollback being issued.

Methods of reducing waiting and waiting time

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

  1. Adjust LGWR for better disk throughput. For example, do not put the redo log on RAID 5, because RAID 5 will bring great performance loss to the system written frequently.
  2. If there are many small transactions, see whether the transaction batch can be put together to reduce different commit operations. Each submission must confirm that the relevant redo is brushed to the disk. Although Oracle can “piggybacked” commit, reducing the total number of commits through batch transactions can have a very good effect.
  3. See if any processing can use the commit nowait option (but be sure to understand its semantics before using it).
  4. See if you can safely do anything with the nologing / unrecoverable option.
  5. Check that the redo log is large enough. Increase the redo log so that the log switches between 15 and 20 minutes.

In addition, the total waiting time for log file synchronization can be divided into the following types:

  1. Wake up LGWR when idle
  2. LGWR collects redo operations to be written and issues I / O requests
  3. Log write I / O completion time
  4. LGWR I / O starts working
  5. LGWR notifies the foreground / user session that the write is completed
  6. Foreground / user session wake up