Inside Oracle LRU latches
Oracle Database Tips by Donald Burleson
Question: I noted that the Oracle
internal latches are not documented and there are several parameters
where you specify LRU latches. How are latches different from
locks, and how does a DBA learn about Oracle latch management?
Answer: Latches are like locks for RAM memory
structures to prevent concurrent access and ensure serial execution of kernel
code. The LRU (least recently used) latches are used when seeking, adding, or
removing a buffer from the buffer cache, an action that can only be done by one
process at a time.
Contention on an LRU latch usually means that there is a
RAM data block that is in high demand. If a latch is not available a 'latch
free miss' statistics is recorded.
Oracle internal latches
Latches are in-memory locks that ensure one-at-a-time,
serial access for when an Oracle process modifies a RAM structure.
For example, the data buffer latches (sometimes called LRU
latches) ensure that Oracle processes are 'serialized', such that only one
process may alter the data buffer address chain. This twiddling of RAM
addresses happens very fast (RAM speed is expressed in nanoseconds), yet busy
Oracle databases may experience waits on these events.
Note: Protecting Oracle's competitive advantage
All database vendors have a vested interest in
protecting their competitive advantage, especially within the software
kernel, and much of Oracle's competitive advantage lies in their superb
code. Thus, it's no surprise that Oracle does not publish the internal
machinations of their kernel code and it is against the licensing rules to
decompile the Oracle code to reveal these mechanisms, so we must speculate
as to how Oracle manages his internal locks and latches. Remember, Oracle
is not being uncooperative by not releasing these details; it's done to
protect their competitive advantage.
Oracle buffer management
In most databases, an address list is used to sequence the
data blocks within the RAM regions (db_cache_size, db_keep_cache_size,
db_32k_cache_size, etc.) because it is faster to sequence by address than to
shift the actual data blocks. To prevent corruption within Oracle's internal the
RAM regions, a database must ensure that only one process at a time manipulates
the 'address list' (a list of pointers to the RAM addresses of the data
blocks). In a database like Oracle, we know that the list of data blocks are
shifted during many operations, but bat far the most common is the 'consistent
get', a logical I/O where Oracle requests a data block and finds it within the
In order to ensure that frequently-referenced data blocks
remain in the buffers, Oracle probably uses this sequence of events for initial
reads (physical reads) and logical buffer reads (consistent gets):
When Oracle does a disk read, the database must buffer-up
the data block and adjust the RAM pointers:
1 - Oracle marks the LRU address as eligible for
over-writing and Oracle perform a disk read and load the data block into
this RAM slot, the buffer marked as LRU (least recently used). Note: This
causes an ?age out?, as Oracle overwrites the contents of the old block.
2 - Once loaded, the DBMS notes the RAM heap address
and does a midpoint insertion, shifting the data block address list to place
the new block address in the middle of the list. During this operation the
database needs to ensure that the process managing the read has exclusive
control of the buffer address list:
Oracle's data buffer causes blocks to stay cached based on
Logical buffer reads:
When a request is made for a data block that is already in
the data buffer, it is 'promoted' to the head of the data buffer address list,
thereby helping to ensure caching of frequently-referenced blocks:
1 - Oracle issues an 'LRU latch' to ensure that the
process has exclusive control over the address list.
2 - The managing process then directs the DBMS to shift
the requested block address to the MRU (Most Recently Used) end of the list.
3 - The latch is released and the process de-references
the pointer (going to that located in the heap) and the row is accessed by
the calling program.
Latch and buffer contention
Oracle records a 'latch miss' when a process must wait for
a latch to become available, and we also see 'buffer busy waits' when a process
must wait for a freelist. You can reduce buffer busy waits by adding additional
FREELISTS or FREELIST GROUPS. For low-update databases you can also implement
bitmap freelists (ASSM, Automatic Segment Storage Management) with the create
tablespace clause 'segment space management auto'.
You can also see the "latch free" wait event in a STATSPACK
or AWR report to note how long the database is waiting on latches.
%Time Total Wait wait
Event Waits -outs Time (s) (ms)
---------------------------- -------------- ------ ----------- -------
log file sync 73,876 .1 1,977 27
- contention 554 47.8 874 1578 0.0
enq: SQ - contention 3,169 3.7 680 215
latch: library cache 8,022 .0 172 21
buffer busy waits 4,316 2.9 169 39
SQL*Net more data to dblink 18,425 .0 146 8
capture: waiting for 85 50.6 112 1321 0.0
log file parallel write 10,451 .0 99 9
log file switch (checkpoint 181 39.2 97 535
rdbms ipc reply 339 5.9 48 143
control file parallel write 359 .0 13 35
1,463 .0 10 7 0.0
The latch free Oracle metric occurs when the process is
waiting for a latch held by another process (this wait event does not apply to
processes that are spinning while waiting for a latch; when a process is
spinning, it is not waiting).
Waits Time (s) Ela Time
------------ ----------- --------
buffer busy waits
PL/SQL lock timer
db file sequential read
We also see buffer latch contention in STATSPACK and AWR
reports, reported in the latch section. Much of the latch information in this
section is undocumented.
Multiple LRU latches
According to Oracle, the DBA can define multiple data
buffer LRU latches to provide high concurrency for high I/O databases. In
Oracle8, Oracle automatically sets the number of LRU latches to be one half the
number of CPUs on the system. After Oracle8, Oracle has the hidden parm
_db_block_lru_latches to control the number of latches per buffer, but this
should only be altered at the direction of Oracle technical support.
Oracle provides for multiple LRU latches specifications as a second parameter
value for buffers parms, like this:
BUFFER_POOL_KEEP = (28,2)
What are LRU latches exactly and how do I determine the
number of LRU latches for a data buffer?
The LRU latches parm specifies the number of LRU latches to
assign to the pool, but note that the minimum number of buffers assigned to the
pool is 50 times the number of assigned latches When configuring LRU
latches and DBWR processes, remember that the latches are assigned to the pools
sequentially and to the DBWR processes in round-robin fashion. The number of LRU
processes should be equal to or a multiple of the value of DBWR processes to
ensure that the DBWR load is balanced across the processes.
For symmetric multiprocessor (SMP) systems, Oracle
automatically sets the number of LRU latches to a value equal to one half the
number of CPUs on the system. However, Craig also notes that it can be
define more latches than CPU's.
Oracle Training from Don Burleson
The best on site
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!