TUNING THE REDOLOG BUFFER (from ML 147471.1)
1. What is the Redolog 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 . Redo entries are used for
database
recovery, if necessary.
Redo entries are copied by Oracle server processes from the user's
memory
space to the redo log buffer in the SGA. The redo entries take up
continuous,
sequential space in the buffer. The background process LGWR writes the
redo log buffer to the active online redo log file (or group of files)
on disk.
The initialization parameter LOG_BUFFER determines the size (in bytes)
of the redo log buffer. In general, larger values reduce log file I/O,
particularly if transactions are long or numerous. The default setting
is four times the maximum data block size for the host operating system.
2. Redolog Latches
When a change to a data block needs to be done, it requires to create
a redo record in the redolog buffer
The database has three redo latches to handle this process:
- Redo Copy latch
The redo copy latch is acquired for the whole duration of the
process described above. The init.ora LOG_SIMULTANEOUS_COPIES
determines the number of redo copy latches. It is only released when a
log switch is generated
to release free space and re-acquired once the log switch ends.
- Redo allocation latch
The redo allocation latch is acquired to allocate memory space in the
log buffer. Before Oracle9.2, the redo allocation latch is unique and
thus
serializes the writing of entries to the log buffer cache of the SGA.
In Oracle 9.2. Entreprise Edition, the number of redo allocation
latches is determined by init.ora LOG_PARALLELISM. The redo
allocation latch allocates space in the log buffer cache for each
transaction entry. If transactions are small, or if there is only
one CPU on the server, then the redo allocation latch also copies the
transaction data into the log buffer cache. If a logswitch is needed to
get free space this latch is released as well with the redo copy
latch.
- Redo writing latch
This unique latch prevent multiple processes posting the LGWR
process
requesting log switch simultaneously. A process that needs free space
must
acquire the latch before of deciding whether to post the LGWR to
perform
a write, execute a log switch or just wait.
In Oracle9.2, multiple redo allocation latches become possible with
init.ora
LOG_PARALLELISM. The log buffer is split in multiple LOG_PARALLELISM
areas that each have a size of init.ora LOG_BUFFER. The allocation job
of each area
is protected by a specific redo allocation latch. The number of redo
copy latches
is still determined by the number of cpus
4. Detecting and Resolving
Redolog Buffer Performance Problem
Contention in the redolog buffer will impact the performance of the
database since all DML and DDL must record a entry before of being
executed.
Contention can be seen as a latch contention or as excessive request
for
free space in the log buffer.
Note: In general log buffer contention is not frequent problem unless
the latches already mentioned are consistently in the top wait events.
Experience usually shows redo IO throughput is the main culprit of redo
contention.
The database allow you to detect both types of contention as described
below:
- Latch contention
The following query determines the miss ratio and the "immediate"
miss
ratio for redolog latches.
SELECT substr(ln.name,
1, 20), gets, misses, immediate_gets, immediate_misses
FROM v$latch l, v$latchname ln
WHERE ln.name in ('redo
allocation', 'redo copy')
and ln.latch# = l.latch#;
If the ratio of MISSES to GETS exceeds 1%, or the ratio of
IMMEDIATE_MISSES
to (IMMEDIATE_GETS + IMMEDIATE_MISSES) exceeds 1%, there is latch
contention.
Note: Oracle recommends to tune first the redo allocation
latch
rather than the redo copy latch.
In Oracle 9.2:
If the contention is caused by redo allocation latch you can
try to increase their number via init.ora LOG_PARALLELISM
If you find redo copy latch contention, you can increase the
hidden init.ora _LOG_SIMULTANEOUS_COPIES to have more latches
available. The default is twice the numbers of CPUs.
- Request for space contention
The statistic "redo log space requests" reflects the number of times
a user process waits for space in the redo log file, not the buffer
space
.. This statistic is available through the dynamic performance
table V$SYSSTAT. By default, this table is only available to the user
SYS and to users granted SELECT ANY TABLE system privilege, such as
SYSTEM. Monitor this statistic over a period of time while
your application is running with this query:
SELECT name,
value
FROM v$sysstat
WHERE name = 'redo
log space requests';
The value of "redo log space requests" should be near 0. If this
value increments consistently, processes have had to wait for space in
the
buffer. This may be caused the checkpointing or log switching.
Improve thus the checkpointing or archiving process.