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.
