Understanding Oracle Log Buffer Space Wait Events

By: Richard Niemiec

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

  • Sessions wait on the log buffer space event when they are unable to copy redo entries into the log buffer due to insufficient space.
  • The LGWR process is responsible for writing out the redo entries and making room for new ones.

 Common Causes, Diagnosis, and Actions

In addition to the log buffer space wait event statistics, Oracle also maintains a session- and system-level redo buffer allocation retries statistic in the V$SESSTAT and V$SYSSTAT view, respectively. The redo buffer allocation retries statistic keeps track of the number of times a process has to wait for space in the log buffer. However, the log buffer space wait event is a better indicator because it contains the TIME_ WAITED statistic.

If a session spends a lot of time on the log buffer space wait event, it is normally due to one or both of the following reasons:

  • The log buffer in the SGA is too small.
  • The LGWR process is too slow.

 Undersized Log Buffer

A small log buffer (less than 512K) in a busy batch processing database is bound to cause sessions to contend for space in the log buffer. As mentioned in the preceding section, you can operate a database with a large log buffer if you set the _LOG_IO_ SIZE accordingly. Therefore, check the current LOG_BUFFER setting and make the proper adjustment if necessary. The log buffer is not a dynamic component in the SGA, so you must bounce the instance before the new value is effective.

 Slow I/O Subsystem

A slow I/O subsystem can cause the LGWR process to be unable to cope with the rate of redo generation, and this in turn causes processes to wait on the log buffer space event. New redo entries cannot override entries that have not been written to disks. Make sure the average wait time of the log file parallel write wait event is acceptable. Otherwise, you can improve the I/O throughput using the method discussed in the log file sync section.

So far we have focused on the database. You should also take a look at the application to see if it can be changed to reduce the logging activity. If the log buffer is the place of contention, then perhaps the best thing to do is not to go there, or go there less frequently. Where appropriate, use NOLOGGING operations, bearing in mind that objects created with NOLOGGING are unrecoverable unless a backup is immediately taken. If you intend to use NOLOGGING operations, check to see if the FORCE LOGGING option is turned on as it overrides the NOLOGGING specification. The FORCE LOGGING option can be enabled at object, tablespace, and database levels. Also, look out for bad application behavior that fetches and updates the entire row when only a few columns are actually changed. This can be hard to catch. You may discover this behavior by mining the DMLs from the V$SQL view or redo log files using Oracle Log Miner.

Leave a Reply