Tuning REDO log buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery, if necessary.The redo entries take up continuous, sequential space in the buffer. The background process LGWR writes the redo log buffer to the active redo log file (or group of files) on disk.

To tune the value for LOG_BUFFER first determine the space request ratio, this is the ratio of redo log space requests to redo log requests:

Select Round(e.value/s.value,5) "Redo Log Ratio"
From v$sysstat s, v$sysstat e
Where s.name = 'redo log space requests'
and e.name = 'redo entries'


The statistic REDO BUFFER ALLOCATION RETRIES reflects the number of times a user process waits for space in the redo log buffer. This statistic can be queried through the dynamic performance view V$SYSSTAT.
Use the following query to monitor these statistics over a period of time while your application is running:

SELECT NAME, VALUE
FROM V$SYSSTAT
WHERE NAME = 'redo buffer allocation retries';

The value of redo buffer allocation retries should be near zero over an
interval. If this value increments consistently, then processes have had to wait for space in the redo log buffer. The wait can be caused by the log buffer being too small or by checkpointing. Increase the size of the redo log buffer, if necessary, by changing the value of the initialization parameter LOG_BUFFER. The value of this parameter is expressed in bytes. Alternatively, improve the checkpointing or archiving process.

Written by admin on December 5th, 2010 with comments disabled.
Read more articles on Oracle Tuning.

Related articles

Comments disabled

Comments on this article have been disabled.