LGWR wait for redo copy

Note 147471.1 of metalink:

Bookmark Go to End

Purpose:

This article provide detailed information to understand How the Redolog Buffer Cache Works and How to detect and resolve tuning problems related with this SGA structure.
Contents:

1. What is the Redolog Buffer
2. Redolog Latches

Redo Copy latch
Redo allocation latch
Redo writing latch
3. Instance Parameters Related with the Redolog Latches
4. Tuning Redolog Buffer Performance
Latch contention
Request for space contention

TUNING THE REDOLOG BUFFER

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 executing the following steps:
Ensure that no other processes has generated a higher SCN
Find for space available to write the redo record. If there are not space available a the LGWR must write to disk or issue a log switch
Allocate the space needed in the redo log buffer
Copy the redo record to the log buffer and link it to the appropriate structures for recovery purposes.
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. 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 serializes the writing of entries to the log buffer cache of the SGA. 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 low switch is needed to get free space this latch is released as well with the redo copy latch.
Redo writing latch
This 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.
3. Instance Parameters Related with the Redolog Latches

There are two parameters that modify the behavior of the latch allocation in the redolog buffer: LOG_SIMULTANEOUS_COPIES (This parameter controls the number of redo copy latches when the system has more than one CPU), and LOG_SMALL_ENTRY_MAX_SIZE.
In Oracle7 and Oracle8, When LOG_SIMULTANEOUS_COPIES is set to a non-zero value, and the size of the transaction entry is smaller than the value of the LOG_SMALL_ENTRY_MAX_SIZE parameter then the copy of the transaction entry into the log buffer cache is performed by the redo allocation latch. If the size of the transaction entry exceeds LOG_SMALL_ENTRY_MAX_SIZE, then the transaction entry is copied into the log buffer cache by the redo copy latch.

In Oracle8i, a redo copy latch is always required regardless of the redo size so the check is no longer performed. The parameter LOG_SMALL_ENTRY_MAX_SIZE is now obsolete.

For further detail on the change of this parameters in Oracle 8i see

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. 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 Oracle7 and Oracle8
If the contention is caused by redo allocation latch decrease the value of LOG_SMALL_ENTRY_MAX_SIZE. The recommended value is the average of redo size which can be calculated as (redo size/redo entries) from V$SYSSTAT.

If you find redo copy latch contention, you can increase the parameter LOG_SIMULTANEOUS_COPIES to have more latches available. The recommended value es twice the numbers of CPUs.

In Oracle8i
If the contention is caused by redo allocation latch you can either use the NOLOGGING option to reduce the amount of redo log entries for certain operations (See ) or reduce the load on the latch increasing the LOG_BUFFER PARAMETER.

@You can reduce the load on the latch as well using the parameter _LOG_IO_SIZE
@described in the

If you find redo copy latch contention, you can increase the parameter _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 buffer. 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 by the log buffer being too small, or it could be caused by
the checkpointing or log switching. 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, expressed in bytes, must be a
multiple of DB_BLOCK_SIZE. Alternatively, improve the checkpointing or
archiving process.