Resolving Oracle Latch Contention
By Guy Harrison
Principal Software Architect, Quest Software
Contents
Resolving Oracle Latch Contention..................................................... 1
Introduction.............................................................................................................................................3
What Are Latches?..................................................................................................................................3
How Latches Work.................................................................................3
Causes of contention for specific latches...........................................4
Measuring Latch Contention ................................................................ 4
Ratio-based Techniques..........................................................................................................................4
Wait interface-based techniques.............................................................................................................6
Tuning the Application to Avoid Latch Contention............................7
Using Bind Variables..............................................................................................................................7
Avoiding Hot Blocks ...............................................................................................................................7
Is Latch Contention Inevitable? ........................................................... 7
Investigating _spin_count .................................................................... 8
Relationship Between Spin Count, Latch Contention and Throughput.................................................8
Conclusion.............................................................................................................................................10
About the Author ...................................................................................................................................11
About Quest Software ...........................................................................................................................11
Resolving Oracle Latch Contention
By Guy Harrison
Introduction
This white paper presents an overview of how the Oracle RDBMS uses latches to protect
shared memory, the typical causes of and solutions to latch contention, and summarizes some
research conducted at Quest Software that suggests that manipulating the (now)
undocumented parameter “_spin_count” can be effective in relieving otherwise intractable
latch contention problems.
What Are Latches?
Latches are serialization mechanisms that protect areas of Oracle’s shared memory (the
SGA). In simple terms, latches prevent two processes from simultaneously updating — and
possibly corrupting — the same area of the SGA.
Oracle sessions need to update or read from the SGA for almost all database operations. For
instance:
• When a session reads a block from disk, it must modify a free block in the buffer
cache and adjust the buffer cache LRU chain
1
.
• When a session reads a block from the SGA, it will modify the LRU chain.
• When a new SQL statement is parsed, it will be added to the library cache within the
SGA.
• As modifications are made to blocks, entries are placed in the redo buffer.
• The database writer periodically writes buffers from the cache to disk (and must
update their status from “dirty” to “clean”).
• The redo log writer writes entries from the redo buffer to the redo logs.
Latches prevent any of these operations from colliding and possibly corrupting the SGA.
How Latches Work
Because the duration of operations against memory is very small (typically in the order of
nanoseconds) and the frequency of latch requests very high, the latching mechanism needs to
be very lightweight. On most systems, a single machine instruction called “test and set” is
used to see if the latch is taken (by looking at a specific memory address) and if not, acquire it
(by changing the value in the memory address).
If the latch is already in use, Oracle can assume that it will not be in use for long, so rather
than go into a passive wait (e.g., relinquish the CPU and go to sleep) Oracle will retry the
operation a number of times before giving up and going to passive wait. This algorithm is
called acquiring a spinlock and the number of “spins” before sleeping is controlled by the
Oracle initialization parameter “_spin_count”.
The first time the session fails to acquire the latch by spinning, it will attempt to awaken after
10 milliseconds. Subsequent waits will increase in duration and in extreme circumstances
may exceed one second. In a system suffering from intense contention for latches, these waits
will have a severe impact on response time and throughput.
1
The LRU (Least Recently Used) chain records how often individual blocks have been accessed. If a
block is not accessed it moves towards the LRU end of the list and eventually is flushed from the cache.
Causes of contention for specific latches
The latches that most frequently affect performance are those protecting the buffer cache,
areas of the shared pool and the redo buffer.
• Library cache latches:
These latches protect the library cache in which sharable
SQL is stored. In a well defined application there should be little or no contention for
these latches, but in an application that uses literals instead of bind variables (for
instance “WHERE surname=’HARRISON’” rather that “WHERE
surname=:surname,” library cache contention is common.
• Redo copy/redo allocation latches: These latches protect the redo log buffer,
which buffers entries made to the redo log. Recent improvements (from Oracle 7.3
onwards) have reduced the frequency and severity of contention for these latches.
• Shared pool latches: These latches are held when allocations or de-allocations of
memory occur in the shared pool. Prior to Oracle 8.1.7, the most common cause of
shared pool latch contention was an overly large shared pool and/or failure to make
use of the reserved area of the shared pool
2
.
• Cache buffers chain latches:
These latches are held when sessions read or write to
buffers in the buffer cache. In Oracle8i, there are typically a very large number of
these latches each of which protects only a handful of blocks. Contention on these
latches is typically caused by concurrent access to a very “hot” block and the most
common type of such a hot block is an index root or branch block (since any index
based query must access the root block).
Measuring Latch Contention
Ratio-based Techniques
Conventional wisdom in the mid-90s was to focus on the latch “miss” rate to determine the
degree of latch contention. Looking at the view v$latch:
Name Null? Type
----------------------------------------- -------- ----------------------------
ADDR RAW(4)
LATCH# NUMBER
LEVEL# NUMBER
NAME VARCHAR2(64)
GETS NUMBER
MISSES NUMBER
SLEEPS NUMBER
IMMEDIATE_GETS NUMBER
IMMEDIATE_MISSES NUMBER
WAITERS_WOKEN NUMBER
WAITS_HOLDING_LATCH NUMBER
SPIN_GETS NUMBER
SLEEP1 NUMBER
SLEEP2 NUMBER
SLEEP3 NUMBER
SLEEP4 NUMBER
SLEEP5 NUMBER
SLEEP6 NUMBER
SLEEP7 NUMBER
SLEEP8 NUMBER
SLEEP9 NUMBER
SLEEP10 NUMBER
SLEEP11 NUMBER
2
The reserved area of the shared pool is intended to hold large contiguous entries in the shared pool (such as large
PL/SQL packages).
