Tuning the Redo Log Buffer

Problem
You’d like to know how to tune the redo log buffer, as you’ve reviewed several AWR reports that pointed out that the redo log buffer setting for your production database is too small.

Solution
You configure the size of the redo log buffer by setting the value of the initialization parameter LOG_BUFFER. This parameter is static, so any changes to it require a restart of the database. You set the parameter in your init.ora file as follows:

log_buffer=4096000

You can also change the size of the log buffer, with the following ALTER SYSTEM statement:

SQL> alter system set log_buffer=4096000 scope=spfile;
System altered
SQL>

The default value of the LOG_BUFFER parameter is way too small for many databases. Oracle states that you don’t normally need to set a value larger than 1 MB for this parameter. However, you shouldn’t hesitate to raise it to a much larger amount, if it’s warranted by circumstances.

How It Works
When the Oracle server processes change data blocks in the buffer cache, those changes are written to the redo logs in the form of redo log entries, before they are written to disk. The redo log entries enable the database to redo or reconstruct the database changes by using various operations such as INSERT, UPDATE, and DELETE, as well as DDL operations. The Oracle redo logs are thus critical for any database recovery, because it’s these redo log entries that enable the database to apply all the changes made to the database from a point in time in the past. The changed data doesn’t directly go to the redo logs, however; Oracle first writes the changes to a memory area called the redo log buffer. It’s the value of this memory buffer that you can configure with the LOG_BUFFER parameter. The Oracle log writer (LGWR) process writes the redo log buffer entries to the active redo log file (or group of files). LGWR flushes the contents of the buffer to disk whenever the buffer is one-third full, or if the database writer requests the LGWR to write to the redo log file. Also, upon each COMMIT or ROLLBACK by a server process, the LGWR process writes the contents of the buffer to the redo log file on disk.

The redo log buffer is a re-usable cache, so as entries are written out to the redo log file, user processes copy new entries into the redo log buffer. While the LGWR usually works fast enough so there’s space in the buffer, a larger buffer will always have more room for new entries. Since there’s no cost whatsoever to increasing the LOG_BUFFER size, feel free to set it to higher than the suggested maximum of 1 MB for this parameter.

If your database is processing large updates, the LGWR has to frequently flush the redo log buffer to the redo log files even in the absence of a COMMIT statement, so as to keep the buffer no more than a third full. Raising the size of the redo log buffer is an acceptable solution in this situation, and allows the LGWR to catch up with the heavy amount of entries into the redo log buffer. This also offsets a slow I/O system in some ways, if you think the performance of the LGWR process is not fast enough. There are a couple of ways in which you keep the pressure on the redo log buffer down: you can batch COMMIT operations for all batch jobs and also specify the NOLOGGING option where possible, say during regular data loads. When you specify the NOLOGGING option during a data load, Oracle doesn’t need to use the redo log files, and hence it also bypasses the redo log buffer as well.

It’s fairly easy to tune the size of the LOG_BUFFER parameter. Just execute the following statement to get the current “redo log space request ratio”:

SQL> select round(t.value/s.value,5) “Redo Log Space Request Ratio” from v$sysstat s, v$sysstat t
where s.name = ‘redo log space requests’
and t.name = ‘redo entries’

The redo log space request ratio is simply the ratio of total redo log space requests to redo entries. You can also query the V$SYSSTAT view to find the value of the statistic redo buffer allocation retries. This statistic shows the number of times processes waited for space in the redo log buffer:

SQL> select name,value from V$SYSSTAT
where name= ‘redo buffer allocation retries’;

Execute this SQL query multiple times over a period of time. If the value of the “redo buffer allocation retries” statistic is rising steadily over this period, it indicates that the redo log buffer is under space pressure and as a result, processes are waiting to write their redo log entries to the redo log buffer. You must increase the size of the redo log buffer if you continue to see this.