log file switch completion

The log file switch completion wait event has no wait parameters. In Oracle Database 10 g , this wait event falls under the Configuration wait class. Keep the following key thought in mind when dealing with the log file switch completion wait event.

Excessive log switches caused by small log files and transactions that generate a lot of redo entries

Common Causes, Diagnosis, and Actions

If a session spends much of its processing time waiting on the log file switch completion event, this is because the redo log files are too small for the amount of redo entries that are being generated, causing too many log switches. The Oracle session constantly has to wait for the LGWR process to complete the write to the current log file and open a new one.

The log file switch completion latency can be minimized or eliminated by reducing the number of log switches. This means you need to create new log groups with larger log files and drop the old log groups. Larger log files will reduce the number of log switches and expensive conventional checkpoints.

How large should the log files be? The answer depends on the current log file size , the current rate of switching, and your goal for the number of switches per hour during peek processing time. You can find out how frequently the logs are switching using the query provided in the ‚“ log buffer space ‚½ section earlier in this chapter. For example, let ‚ s say the logs are switching at the rate of 60 logs per hour during peak processing time; your target is no more than 4 per hour, and the current log file size is 20M. Based on these numbers , your new log file size should be 300M (60 * 20M / 4).

Note ‚

Larger log files may result in longer instance recovery time. Oracle provides the FAST_START_MTTR_TARGET parameter to maintain the DBA-specified crash recovery time by regulating checkpoints starting in Oracle9i Database. In Oracle8 i Database, you can use the FAST_START_IO_TARGET parameter. In prior versions, you can tune checkpoints with the LOG_CHECKPOINT_INTERVAL and LOG_CHECKPOINT_TIMEOUT parameters.