Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

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. 

                                   Avg
                                             %Time  Total Wait    wait     Waits
Event                                 Waits  -outs    Time (s)    (ms)      /txn
---------------------------- -------------- ------ ----------- ------- ---------
log file sync                        73,876     .1       1,977      27       1.0
enq: HW - contention                    554   47.8         874    1578       0.0
enq: SQ - contention                  3,169    3.7         680     215       0.0
latch: library cache                  8,022     .0         172      21       0.1
buffer busy waits                     4,316    2.9         169      39       0.1
SQL*Net more data to dblink          18,425     .0         146       8       0.3
Streams capture: waiting for             85   50.6         112    1321       0.0
log file parallel write              10,451     .0          99       9       0.1
log file switch (checkpoint             181   39.2          97     535       0.0
rdbms ipc reply                         339    5.9          48     143       0.0
control file parallel write             359     .0          13      35       0.0
latch free                            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).

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                % Total
Event                                          Waits    Time (s) Ela Time
--------------------------------------- ------------ ----------- --------
latch free                                    10,969         666    51.28
buffer busy waits                             15,379         159    12.23
CPU time                                                     149    11.47
PL/SQL lock timer                                 51         105     8.05
db file sequential read                       25,163          96     7.36

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!

Oracle training
 
 

 


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational