Oracle7 Server Tuning

Reducing Contention for Redo Log Buffer Latches

Contention for redo log buffer access rarely inhibits database performance. However, Oracle provides you with methods to monitor and reduce any latch contention that does occur. This section explains

how to detect and reduce contention for space in the redo log buffer

how to detect contention for latches

how to reduce contention for latches

Space in the Redo Log Buffer

When LGWR writes redo entries from the redo log buffer to a redo log file, user processes can then copy new entries over the entries that have been written to disk. LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the redo log is heavy.

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 information in V$SYSSTAT can also be obtained through SNMP.

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. In this case, increase the size of the redo log buffer. The size of the redo log buffer is determined by the initialization parameter LOG_BUFFER. The value of this parameter is expressed in bytes. Try increasing the size of the redo log buffer by increments of 5% until the value of redo log space requests nears 0.

Redo Log Buffer Latches

Access to the redo log buffer is regulated by latches. Two types of latches control access to the redo log buffer:

the redo allocation latch

redo copy latches

The Redo Allocation Latch

The redo allocation latch controls the allocation of space for redo entries in the redo log buffer. To allocate space in the buffer, an Oracle user process must obtain the redo allocation latch. Since there is only one redo allocation latch, only one user process can allocate space in the buffer at a time. The single redo allocation latch enforces the sequential nature of the entries in the buffer.

After allocating space for a redo entry, the user process may copy the entry into the buffer while holding the redo allocation latch. Such a copy is referred to as "copying on the redo allocation latch". A process may only copy on the redo allocation latch if the redo entry is smaller than a threshold size. After copying on the redo allocation latch, the user process releases the latch.

The maximum size of a redo entry that can be copied on the redo allocation latch is specified by the initialization parameter LOG_SMALL_ENTRY_MAX_SIZE. The value of this parameter is expressed in bytes. The minimum, maximum, and default values vary depending on your operating system.

Redo Copy Latches

If the redo entry is too large to copy on the redo allocation latch, the user process must obtain a redo copy latch before copying the entry into the buffer. While holding a redo copy latch, the user process copies the redo entry into its allocated space in the buffer and then releases the redo copy latch.

If your computer has multiple CPUs, your redo log buffer can have multiple redo copy latches. Multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The number of redo copy latches is determined by the initialization parameter LOG_SIMULTANEOUS_COPIES . The default value of LOG_SIMULTANEOUS_COPIES is the number of CPUs available to your Oracle instance.

On single-CPU computers, there should be no redo copy latches since only one process can by active at once. In this case, all redo entries are copied on the redo allocation latch, regardless of size.

Examining Redo Log Activity

Heavy access to the redo log buffer can result in contention for redo log buffer latches. Latch contention can reduce performance. Oracle collects statistics for the activity of all latches and stores them in the dynamic performance table V$LATCH. By default, this table is only available to the user SYS and to other users who have SELECT ANY TABLE system privilege, such as SYSTEM.

Each row in the V$LATCH table contains statistics for a different type of latch. The columns of the table reflect activity for different types of latch requests. The distinction between these types of requests is whether the requesting process continues to request a latch if it is unavailable:

willing-to-wait If the latch requested with a willing-to-wait request is not available, the requesting process waits a short time and requests the latch again. The process continues waiting and requesting until the latch is available.
immediate If the latch requested with an immediate request is not available, the requesting process does not wait, but continues processing.

These columns of the V$LATCH table reflect willing-to-wait requests:

GETS This column shows the number of successful willing-to-wait requests for a latch.
MISSES This column shows the number of times an initial willing-to-wait request was unsuccessful.
SLEEPS This column shows the number of times a process waited and requested a latch after an initial willing-to-wait request.

For example, consider the case in which a process makes a willing-to-wait request for a latch that is unavailable. The process waits and requests the latch again and the latch is still unavailable. The process waits and requests the latch a third time and acquires the latch. This activity increments the statistics in these ways:

The GETS value increases by one, since one request for the latch (the third request) was successful.

The MISSES value increases by one, since the initial request for the latch resulted in waiting.

The SLEEPS value increases by two, since the process waited for the latch twice, once after the initial request and again after the second request.

These columns of the V$LATCH table reflect immediate requests:

IMMEDIATE GETS This column shows the number of successful immediate requests for each latch.
IMMEDIATE MISSES This column shows the number of unsuccessful immediate requests for each latch.

Monitor the statistics for the redo allocation latch and the redo copy latches over a period of time with this query:

SELECT ln.name, 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#;

The output of this query might look like this:

NAME               GETS     MISSES IMMEDIATE_GETS IMMEDIATE_MISSES

------------ ---------- ---------- -------------- ----------------

redo allo...    252867         83              0                0

redo copy             0          0          22830                0

From the output of the query, calculate the wait ratio for each type of request.

Contention for a latch may be affecting performance if either of these conditions is true:

if the ratio of MISSES to GETS exceeds 1%

if the ratio of IMMEDIATE_MISSES to the sum of IMMEDIATE_GETS and IMMEDIATE_MISSES exceeds 1%

If either of these conditions is true for a latch, try to reduce contention for that latch.

These contention thresholds are appropriate for most operating systems, though some computers with many CPUs may be able to tolerate more contention without performance reduction.

Reducing Latch Contention

Most cases of latch contention occur when two or more Oracle processes concurrently attempt to obtain the same latch. Latch contention rarely occurs on single-CPU computers where only a single process can be active at once.

Reducing Contention for the Redo Allocation Latch

To reduce contention for the redo allocation latch, you should minimize the time that any single process holds the latch. To reduce this time, reduce copying on the redo allocation latch. Decreasing the value of the LOG_SMALL_ENTRY_MAX_SIZE initialization parameter reduces the number and size of redo entries copied on the redo allocation latch.

Reducing Contention for Redo Copy Latches

On multiple-CPU computers, multiple redo copy latches allow multiple processes to copy entries to the redo log buffer concurrently. The default value of LOG_SIMULTANEOUS_COPIES is the number of CPUs available to your Oracle instance.

If you observe contention for redo copy latches, add more latches . To increase the number of redo copy latches, increase the value of LOG_SIMULTANEOUS_COPIES . It can help to have up to twice as many redo copy latches as CPUs available to your Oracle instance.

willing-to-wait	If the latch requested with a willing-to-wait request is not available, the requesting process waits a short time and requests the latch again. The process continues waiting and requesting until the latch is available.
immediate	If the latch requested with an immediate request is not available, the requesting process does not wait, but continues processing.

GETS	This column shows the number of successful willing-to-wait requests for a latch.
MISSES	This column shows the number of times an initial willing-to-wait request was unsuccessful.
SLEEPS	This column shows the number of times a process waited and requested a latch after an initial willing-to-wait request.

IMMEDIATE GETS	This column shows the number of successful immediate requests for each latch.
IMMEDIATE MISSES	This column shows the number of unsuccessful immediate requests for each latch.