Last updated on November 14th, 2015 at 02:46 pm
Latches are low level serialization mechanisms used to protect shared data structures in the SGA. The implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.
A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. Latches have an associated level that is used to prevent deadlocks. Once a process acquires a latch at a certain level it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait).
Oracle latch is actually a spin mutexes on process level which usedin most cases to guard the SGA fixed and heap based data structures from being changed every time by more than just a single process
Latch present a contrast with enqueues which always protect structures which has some disk based equivalent such as database objects, data blocks or table rows.Another significant difference between latch and enqueue In latches there is no ordered queue of waiters like in enqueues. Latch waiters may either use timers to wakeup and retry or spin (only in multiprocessors). Since all waiters are concurrently retrying (depending on the scheduler), anyone might get the latch and conceivably the first one to try might be the last one to get.
A process acquires a latch when working with a structure in the SGA (System Global Area). It continues to hold the latch for the period of time it works with the structure. The latch is dropped when the process is finished with the structure. Each latch protects a different set of data, identified by the name of the latch. Oracle uses atomic instructions like “test and set” for operating on latches. Processes waiting to execute a part of code for which a latch has already been obtained by some other process will wait until the latch is released. Examples are redo allocation latches, copy latches, archive control latch etc. The basic idea is to block concurrent access to shared data structures. Since the instructions to
set and free latches are atomic, the OS guarantees that only one process gets it. Since it is only one instruction, it is quite fast. Latches are held for short periods of time and provide a mechanism for cleanup in case a holder dies abnormally while holding it. This cleaning is done using the services of PMON.
Latches request can be made in two modes: “willing-to-wait” or “no wait”. Normally,
latches will be requested in “willing-to-wait” mode. A request in “willing-to-wait” mode will loop, wait, and request again until the latch is obtained. In “no wait” mode the process request the latch. If one is not available, instead of waiting, another one is requested. Only when all fail does the server process have to wait.
Examples of “willing-to-wait” latches are: shared pool and library cache latches
A example of “no wait” latches is the redo copy latch.
If a required latch is busy, the process requesting it spins, tries again and if still not available, spins again. The loop is repeated up to a maximum number of times determined by the initialization parameter _SPIN_COUNT.
If after this entire loop, the latch is still not available, the process must yield the CPU and go to sleep. Initially is sleeps for one centisecond. This time is doubled in every subsequent sleep. This causes a slowdown to occur and results in additional CPU usage, until a latch is available. The CPU usage is a consequence of the “spinning” of the process. “Spinning” means that the process continues to look for the availability of the latch after certain intervals of time,
during which it sleeps.
More information on latches
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
V$LATCHNAME key information:
GETS Number of successful willing-to-wait requests for
MISSES Number of times an initial willing-to-wait request
SLEEPS Number of times a process waited a requested a latch
after an initial wiling-to-wait request.
IMMEDIATE_GETS Number of successful immediate requests for each latch.
IMMEDIATE_MISSES Number of unsuccessful immediate requests for each latch.
Calculating latch hit ratio
To get the Hit ratio for latches apply the following formula:
“willing-to-wait” Hit Ratio=(GETS-MISSES)/GETS
“no wait” Hit Ratio=(IMMEDIATE_GETS-IMMEDIATE_MISSES)/IMMEDIATE_GETS
This number should be close to 1. If not, tune according to the latch name
Useful SQL scripts to get latch information
-Display System-wide latch statistics.
column name format A32 truncate heading “LATCH NAME”
column pid heading “HOLDER PID”
from v$latch a, v$latchholder b, v$latchname c
where a.addr = b.laddr(+)
and a.latch# = c.latch#
order by a.latch#;
-Given a latch address, find out the latch name.
column name format a64 heading ‘Name’
select name from v$latchname a, v$latch b
where b.addr = ‘&addr’
-Display latch statistics by latch name.
column name format a32 heading ‘LATCH NAME’
column pid heading ‘HOLDER 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 ‘&latch_name%’ order by a.latch#;
List of all the latches
Oracle versions might differ in the latch# assigned to the existing latches.
The following query will help you to identify all latches and the number assigned.
column name format a40 heading ‘LATCH NAME’
select latch#, name from v$latchname;
SPIN_COUNT controls how many times the process will re-try to obtain the latch before backing off and going to sleep. This basically means the process is in a tight CPU loop continually trying to get the latch for SPIN_COUNT attempts. On a single CPU system if an Oracle process tries to acquire a latch but it is held by someone else the process will release the CPU and go to sleep for a short period before trying again. However, on a multi processor system (SMP) it is possible that the
process holding the latch is running on one of the other CPUs and so will potentially release the latch in the next few instructions (latches are usually held for only very short periods of time). Performance can be adjusted by changing the value of SPIN_COUNT. If a high value is used, the latch will be attained sooner than if
you use a low value. However, you may use more CPU time spinning to get the latch if you use a high value for SPIN_COUNT.