The log buffer space wait event occurs when server processes write data into the log buffer faster than the LGWR process can write it out. The LGWR process begins writing entries to the online redo log file if any of the following conditions are true:
- The log buffer reaches the _log_io_size threshold. By default, this parameter is set to one-third of the log buffer size.
- A server process performing a COMMIT o ROLLBACK posts to the LGWR process.
- The DBWR process posts to the LGWR process before it begins writing.
As the LGWR process writes entries to disk, user processes can reuse the space in the log buffer for new entries. If the Log Buffer is too small, user processes wait for Log Buffer Space until the LGWR flushes the redo information in memory to disk.
Redo log buffer size
The size of the Redo Log Buffer is controlled by the initialization parameter, LOG_BUFFER, which is expressed in bytes. Oracle’s default setting for LOG_BUFFER is the maximum size of 512k (.5m) or (128k * # of CPUs). Most of the time, the default setting is appropriate.
If your applications insert, update or delete large amounts of data within the database, and user processes experience many Log Buffer Space waits, you may want to consider increasing the LOG_BUFFER parameter. However, careful analysis should be performed when changing this parameter. A LOG_BUFFER sized too large will cause the _log_io_size threshold also to be large. This may cause the LGWR process to write more data less often, causing yet another log-related Oracle wait event, Log File Sync.
For example, a LOG_BUFFER size of 10m will result in a _log_io_size threshold of over 3m. Therefore, the LGWR process will not begin writing until the 3m threshold is met, a checkpoint happens, or a commit or rollback is issued. If a commit or rollback is issued, the LGWR may take longer to write all the data from the buffer (up to last commit mark), especially if it hasn’t yet reached the larger 3m threshold. The larger write at commit time, will result in individual processes waiting on the log file sync event. The log file sync wait event occurs when processes commit or rollback, then wait for the information to be flushed from the log buffer (memory) to the online redo log file (disk).
When changing the LOG_BUFFER parameter, the optimal size should reduce the number of log-related wait events, not increase them.
Other tuning considerations
When sizing the LOG_BUFFER produces no additional benefit, you may want to look at other tuning opportunities to reduce the Log Buffer Space wait event, including: poor I/O performance, application design and materialized views.
Poor I/O Performance
The Log Buffer Space wait event could be an indication of slow disks and/or slow log file switches. Investigate moving the log files to faster disks such as file system direct I/O or raw devices. Also, look at improving the checkpointing or archiving process for better overall throughput.
Application Design
If your application performs many commits or has heavy logging activity, try reducing the activity by using the NOLOGGING option where appropriate. Also, look for poor application behavior, such as, fetching and updating all columns in a table when only a few columns need changed.
Materialized Views
When using materialized views, verify that appropriate methods of refresh have been chosen. If possible, perform fast refreshes instead of complete refreshes. If using 10g and performing complete refreshes, review the setting of the ATOMIC_REFRESH parameter on the refresh statement. In 10g, the default changed to DELETE FROM the materialized view (causing redo creation) instead of using the TRUNCATE command (a nologging operation). For further details on this setting, see Metalink Note 306502.1.
Other tuning options
When processes wait on the Log Buffer Space event, the log buffer is filling up faster than what the LGWR process can write to disk. If this wait event is frequent, throughput is reduced for the entire database. To address this problem, look at increasing the LOG_BUFFER to an optimal size.
Other tuning alternatives include:
- Put log files on faster disks.
- Look at tuning checkpoint or archive processes (log files switches).
- Review application design, use NOLOGGING operations where appropriate, and avoid changing more data than required.
- Finally, check refresh methods when using Materialized Views to reduce logging activity.