From:Steve Adams
Date:12-Oct-2000 19:53
Subject:   Latch impact

Thanks once again for making the effort to come all the way to San Francisco!

The latch impact is an approximate indication of the relative time spent sleeping for the various latch types. For example, in your output below the library cache and shared pool latches represent about 3853/5430 or 71% of the total latch free waiting time. Assuming that latch free waits are a significant performance problem, then this metric is intended to focus your tuning attention on the "worst" latch types.

However, remember that the latch level rule dictates that where you have sleeps against two or more related latch types, then you should focus your tuning attention on the higher level latch type first even if the impact of that latch type is not the worst. For example, in this case you would look firstly at the shared pool latch rather than the library cache latches. Specifically, you would look at the delta for the shared pool latch in V$LATCH_MISSES during an episode of latch contention and then tune the shared pool to address that issue in the expectation that much of the library cache latching issue would thereby be indirectly resolved as well.

How should the impact column in the output of your latch_sleeps script be interpreted? Following is the output I get on my database when I run the script.

LATCH TYPE                                 IMPACT SLEEP RATE WAITS HOLDING LEVEL
------------------------------------- ----------- ---------- ------------- -----
library cache                                3044      0.01%          6578     5
shared pool                                   809      0.06%           357     7
cache buffers chains                          520      0.00%          1407     1
redo allocation                               463      0.01%          4506     6
cache buffers lru chain                       295      0.00%          8776     3
row cache objects                             196      0.00%           363     4
user lock                                      72      1.60%             2     3
Checkpoint queue latch                          9      0.00%           152     7
redo writing                                    7      0.00%            91     5
undo global data                                4      0.00%             7     5
multiblock read objects                         3      0.00%           127     3
enqueues                                        2      0.00%            53     5
list of block allocation                        2      0.00%             0     3
session allocation                              1      0.00%            10     5
longop free list                                1      0.06%             1     8
enqueue hash chains                             1      0.00%             0     4
messages                                        1      0.00%           147     8