The stats$latch Table

Oracle Tips by Burleson Consulting
Don Burleson

Internal to Oracle, latches are used to serialize transactions and are closely tied to the OS semaphores. If the user is planning to perform operations such as accessing data, this user must first obtain all latch data from the table and then become the owner of the latch. If the user's process is forced to wait for a latch because there isn't enough space available, a slowdown will occur and we would experience internal contention for latches. For a complete description of the v$ views, get the "free 11g poster" sponsored by UNISYS.

As we can see from the listing of the v$latch view next, there are many different kinds of latches that are used internally within Oracle. Regardless of the types of latches the important thing is the hit ratio.

The key latches are:

Cache buffers lru chain latch

Enqueues latch

Redo allocation latch

Redo copy latch

Library cache latch

If the hit ratio for any of these key latches is lower than 99 percent, there is substantial latch contention within the database. The contention for these key latches can be reduced in a variety of ways, including tuning the database writer, tuning the redo log files, and reducing buffer cache latch contention. We will be going into detail on these techniques in Chapter 9.

The latch hit ratio is the ratio of the total number of latch misses to the number of latch gets for all latches. A low value for this ratio indicates a latching problem, whereas a high value is generally good. However, as the data is rolled up over all latches, a high latch hit ratio can artificially mask a low get rate on a specific latch. Oracle tuning professionals will cross-check this value with the top 5 wait events to see if latch free is in the list, and refer to the latch sections of the report.

SQL> desc STATS$LATCH;
Name                                      Null?    Type
----------------------------------------- -------- -------------------
SNAP_ID                                   NOT NULL NUMBER(6)
DBID                                      NOT NULL NUMBER
INSTANCE_NUMBER                           NOT NULL NUMBER
NAME                                      NOT NULL VARCHAR2(64)
LATCH#                                    NOT NULL NUMBER
LEVEL#                                             NUMBER
GETS                                               NUMBER
MISSES                                             NUMBER
SLEEPS                                             NUMBER
IMMEDIATE_GETS                                     NUMBER
IMMEDIATE_MISSES                                   NUMBER
SPIN_GETS                                          NUMBER
SLEEP1                                             NUMBER
SLEEP2                                             NUMBER
SLEEP3                                             NUMBER
SLEEP4                                             NUMBER
WAIT_TIME                                          NUMBER

��