log file switch and log file sync

10.3.15log file switch

There are two wait events commonly encountered:

• log file switch (archiving needed)

• log file switch (checkpoint incomplete)

In both of the events, the LGWR is unable to switch into the next online redo log, and all the commit requests wait for this event.

10.3.15.1Actions

For thelogfileswitch(archivingneeded) event, examine why the archiver is unable to archive the logs in a timely fashion. It could be due to the following:

• Archive destination is running out of free space.

• Archiver is not able to read redo logs fast enough (contention with the LGWR).

• Archiver is not able to write fast enough (contention on the archive destination, or not enough ARCH processes). If you have ruled out other possibilities (such as slow disks or a full archive destination) consider increasing the number of ARCn processes. The default is 2.

• If you have mandatory remote shipped archive logs, check whether this process is slowing down because of network delays or the write is not completing because of errors.

Depending on the nature of bottleneck, you might need to redistribute I/O or add more space to the archive destination to alleviate the problem. For thelogfileswitch(checkpointincomplete) event:

• Check if DBWR is slow, possibly due to an overloaded or slow I/O system. Check the DBWR write times, check the I/O system, and distribute I/O if necessary. SeeChapter 8, "I/O Configuration and Design".

• Check if there are too few, or too small redo logs. If you have a few redo logs or small redo logs (for example two x 100k logs), and your system produces enough redo to cycle through all of the logs before DBWR has been able to complete the checkpoint, then increase the size or number of redo logs. See"Sizing Redo Log Files".

10.3.16log file sync

When a user session commits (or rolls back), the session's redo information must be flushed to the redo logfile by LGWR. The server process performing theCOMMITorROLLBACKwaits under this event for the write to the redo log to complete.

Actions

If this event's waits constitute a significant wait on the system or a significant amount of time waited by a user experiencing response time issues or on a system, then examine the average time waited.

If the average time waited is low, but the number of waits are high, then the application might be committing after everyINSERT, rather than batchingCOMMITs. Applications can reduce the wait by committing after 50 rows, rather than every row.

If the average time waited is high, then examine the session waits for the log writer and see what it is spending most of its time doing and waiting for. If the waits are because of slow I/O, then try the following:

• Reduce other I/O activity on the disks containing the redo logs, or use dedicated disks.

• Alternate redo logs on different disks to minimize the effect of the archiver on the log writer.

• Move the redo logs to faster disks or a faster I/O subsystem (for example, switch from RAID 5 to RAID 1).

• Consider using raw devices (or simulated raw devices provided by disk vendors) to speed up the writes.

• Depending on the type of application, it might be possible to batchCOMMITs by committing everyNrows, rather than every row, so that fewer log file syncs are needed.