Donald K. Burleson

Oracle and Expert Systems Technology

Oracle - Understanding the Log Buffer

The log_buffer parameter specifies the number of bytes allocated to the redo log buffer in the SGA. In general, larger values reduce redo log file I/O, particularly if transactions are long or numerous. In a busy system, the value 65K or higher is reasonable. However, values above 1MB are unlikely to yield significant benefit. The default is set to 4 times the maximum database block size for the host operating system. For example, a database with 4096 block sizes would have a 16K log buffer. 

Note: The memory set aside for the log buffer may also be surrounded by 'guard' pages to help protect the redo buffer, hence the size of 'Redo Buffers' shown at instance startup may differ from the value of LOG_BUFFER set in the init.ora file.

The redo log buffer helps absorb processing spikes during high update/insert/delete periods. As we know from the DBA class, if a process is making a lot of changes, the redo it generates will be written to a memory buffer. As the buffer fills up, the output process (LGWR) is awakened to empty the buffer. LGWR will need some lead time, since a sufficiently large transaction can generate redo faster than LGWR can write it to disk. To prevent slowdowns, Oracle starts the log writer (LGWR) when the log buffer becomes 1/3 full.

The "redo log space request" statistic in v$sysstat is very important to the log buffer.  It reflects the number of times a user process waits for space in the redo log buffer. This value of redo log space requests should be 0. If this value is non zero then the size of the log buffer should be increased by increasing the value of the init.ora log_bugger parameter, up to a maximum of 1 megabyte.

The value of the log_buffer must be an exact multiple of the operating system (OS) block size. For example, if the OS block size is 4096, a value of 10*4096 or 40960 will be valid.

Now let's take a look at the init.ora parameters that are used for the redo logs.

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.  Click here for more books by Donald K. Burleson.