What is a Latch in Oracle Database?

Latch is a low level locking mechanism provided by oracle. In this article we will look at the latch concept and how to tune the server to reduce latch contention.

What is a Latch ?

A Latch is a low level serialization mechanism that ( released as quickly as it is acquired ) protects shared data structures. A process acquires and holds the latch as long as the the data structure is in use. The basic idea is to prevent concurrent access to shared data structures in in the SGA. In case the process dies without releasing the latch, the PMON process will clean up the lock on the data structure and releases the latch.

If a process is not able to obtain a latch, it must wait for the latch to be freed up by process holding it. This causes additional spinning ( looking for availability at fixed intervals of time ) of the process, there by causing extra load on the CPU. This process will spin until the latch is available. A dba has to monitor the latches for contention and make sure that CPU cycles are not being burnt on process spinning.

The Tunable Trio ( .. 3 latches I monitor )
You cannot monitor and tune all the available internal latches in oracle. The few latches I monitor are Redo Allocation Latch, Redo Copy Latch and Row Cache objects Latch.
• Redo Allocation Latch controls the allocation of space for redo entries in the redo log buffer.
• Redo Copy Latch is used when the size of a redo entry is greater than the value of LOG_SMALL_ENTRY_MAX_SIZE.
• Row Cache Latch is used when a process attempts to access the data dictionary information from the cache.

select c.name,a.gets,a.misses,a.sleeps, a.immediate_gets,a.immediate_misses,b.pid
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
and (c.name like 'redo%' or c.name like 'row%'
order by a.latch#;

When you execute the above script, the output will display the name of the latch and various other statistics about the redo and row cache latches. The values of gets, misses and sleeps are willing to wait operations, i.e, the requesting process will wait a short time, if the latch is busy, and request the latch again. The immediate_gets and immediate_misses columns represent the number of successful and unsuccessful immediate requests for the latches.

To make the database perform well, try to adjust parameters to keep the miss ratio [(misses/gets)*100 ] for each of these latches to a minimum.( < 1 )

Tuning Process ( What to do If I have Contention )
In case of redo allocation latch contention, try to decrease the log_small_entry_max_size so that the redo copy latch is used more.

If the query shows heavy redo copy latch contention, set the value of log_simultaneous_copies to the number of cpu's. If it still does not help and the contention on the allocation latch is low, then try increasing the log_small_entry_max_size to shift some load to the allocation latch.

In order to decrease the contention on the row cache latch, increase the shared_pool_size since the data dictionary is a part of the shared pool.

When tuning Oracle, one has to remember that there are no absolute values for tuning and the best balance between the values has to be obtained by incremental tuning.