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 buffer.

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):

Physical reads:

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 popularity

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. 

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).

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 beneficial to define more latches than CPU's.

Oracle Training from Don Burleson  The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!