Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients


Free Oracle Tips


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


Remote Oracle

Custom Oracle Training

Donald K. Burleson

Oracle Tips


Display Latches in Oracle

There have been volumes written about Oracle latches.  But, in case you do not know what they are, latches protect the many memory structures in Oracle's SGA.  They ensure that one and only one process at a time can run or modify any memory structure at the same instant.  Much more restrictive than locks (which at least allow for some collective user interaction), latches have no queuing mechanism, so either you get it or you don't, and you are forced to continually retry if you don’t.

Common indicators of latch contention are a latch miss ratio, which records willing-to-wait mode latch requests, and latch immediate miss ratio, which records no-wait mode latch requests. 

These statistics reflect how often latch requests were made and satisfied without waiting.  If either of these exceeds 1%, then you should drill down further into latching details to identify what latches are responsible for the contention. 

To drill down into latch miss details, you can use the latchdet.sql script:

See Code depot for complete script


. . .
. . .
order by
      2 desc;
Figure 5.11 – Output showing latch details

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:





Oracle performance tuning book



Oracle performance tuning software

Oracle performance tuning software
Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
email BC:

Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.