Oracle log_buffer tips by Donald Burleson

The Oracle log buffer is a small area of RAM where updates (DML) are processed for roll-forward and flashback functionality.

For related redo tuning notes, see here for details on optimal redo log_buffer sizing, and here for overall redo log tuning tips.  For the whole story, see my book "Oracle Tuning: The Definitive Reference".   For more details on log_buffer sizing, see Bug 4930608 and MOSC Note 604351.1.

 "The Log_buffer Cannot be Changed In 10g R2", they changed the way how log buffer is allocated. Oracle 10g R2 combines fixed SGA and redo log buffer. Free space that is left after fixed sized SGA is allocated in the memory granule, Oracle dedicates to LOG_BUFFER."

Note (10gr2 and beyond):  Per MOSC note 351857.1, starting in release 10.2 and beyond, Oracle will automatically size the log_buffer on your behalf and log_buffer cannot be changed dynamically.  The automatic log_buffer sizing is based on the granule size (as determined by to _ksmg_granule_size):

select
   a.ksppinm name,
   b.ksppstvl value,
   a.ksppdesc description
from
   x$ksppi a,
   x$ksppcv b
where
   a.indx = b.indx
and
   a.ksppinm = '_ksmg_granule_size';

NAME                           VALUE                          DESCRIPTION
------------------------------ ------------------------------ ------------------------
_ksmg_granule_size             16777216                       granule size in bytes

 Also note that if you are  Oracle's Automatic Memory Management AMM (AMM is Not recommended for some databases), the log_buffer is part of the memory_target algorithm.

A bigger log_buffer can be better

There is some debate about the optimal size for the Oracle redo log buffer, a small region of RAM that initially keeps the "after images" of row updates.  In the 8.1.7 Oracle Reference manual, Oracle suggests keeping the size of the log buffer to a tiny value, under one megabyte:

[Log Buffer] Default value: Operating system specific. Maximum: 500K or 128K * CPU_COUNT, whichever is greater

However, MOSC note 216205.1 Database Initialization Parameters for Oracle Applications 11i, recommends a log_buffer size of 10 megabytes for Oracle Applications, a typical online database:

The log writer parameters control the size of the log buffer within the SGA and how frequently the redo logs are check pointed ( all dirty buffers written to disk to create a new recovery point).

A value of 10MB for the log buffer is a reasonable value for Oracle Applications and it represents a balance between concurrent programs and online users. The value of log_buffer must be a multiple of redo block size, normally 512 bytes.

Today, most large database use a log buffer between 5 meg to 10 meg.  It's important to note that log buffer shortages do not always manifest in the top-5 timed events, especially if there are other SGA pool shortages.  Here is an example of an Oracle 10g database with an undersized log buffer, in this example 512k (This is the database as I found it, and there was a serious data buffer shortage causing excessive disk I/O):

For more notes on optimizing your Oracle redo log buffer size, click here.

Also see: