log file switch and log file sync
log file switch and log file sync
10.3.15 log 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.1 Actions
For the log file switch (archiving needed) 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 the logfile switch (checkpoint incomplete) 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. See Chapter 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.16 log 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 theCOMMIT or ROLLBACK waits 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 every INSERT, rather than batching COMMITs. 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 batch COMMITs by committing every N rows, rather than every row, so that fewer log file syncs are needed.
