As a latch wait is typically quite short it is possible to see a large number of latch waits which only account for a small percentage of time.
If the TIME spent waiting for latches is significant then it is best to determine which latches are suffering from contention. Both STATSPACK and Bstat/estat reports include sections which show latch activity in the period sampled. These sections are based on
(which gives a summary of latch activity since instance startup) and can give an indication of which latches are responsible for the most time spent waiting for "latch free" thus:SELECT latch#, name, gets, misses, sleeps FROM v$latch WHERE sleeps>0 ORDER BY sleeps ; Note that this select gives the worst latches at the BOTTOM of the list.Some lines in this report are actually for multiple latches all of the same type. To determine if the latch activity is concentrated on one particular latch in the set one can query (only available from 7.3 onwards):SELECT addr, latch#, gets, misses, sleeps FROM v$latch_children WHERE sleeps>0 and latch# = &LATCH_NUMBER_WANTED ORDER BY sleeps ;This gives the system wide number of waits for each child latch of the type LATCH#. If there are no rows returned then there is only a single latch of the type you are looking at.
If there are multiple rows the important thing to note is whether the SLEEPS are reasonably distributed or if there are one or two child latches responsible for 80% of the SLEEPS. If the contention is focused on one or two child latches make a note of which children are seeing a problem - note the ADDR column. One can also look at:
- Does the same session/s keep appearing in
- Sessions with high latch waits in
(Although it is important to note that innocent sessions may show high numbers of waits if some other session is repeatedly holding the latch)
There is no general advice to reduce latch waits as the action to take depends heavily on the latch type which is causing the waits. If there is no particular latch and waits occur across all latches then check for CPU starvation or uneven O/S scheduling policies - a CPU bound system will often show numerous latch waits across many types of latch.
The latches most likely to show high sleeps are listed below along with some possible actions:shared pool latch Heavy use of literal SQL will stress this latch significantly. If your online application makes heavy use of literal SQL statements then converting these to use bind variables will give significant improvements in latch contention in this area. SeeThere is also a V$LATCH_MISSES view which may be of help to Oracle Support in more obscure cases:SELECT "WHERE", SLEEP_COUNT, WTR_SLP_COUNT, LONGHOLD_COUNT FROM v$latch_misses WHERE parent_name='&ADDR_OF_PROBLEM_LATCH' ORDER BY 1 ;This shows where-abouts in the code the latch holder and latch waiters were when the latch was requested but not obtained immediately. In some releases V$LATCH_MISSES does not have the WTR_SLP_COUNT and LONGHOLD_COUNT columns. The view does not exist prior to Oracle7.3.
Tracing User sessions Note 62160.1Note 62143.1 for issues affecting the shared pool. library cache latches From Oracle 7.2 onwards the library cache latch has child latches . Problems with these latches are typically due to heavy use of literal SQL or very poor shared pool configuration. If your online application makes heavy use of literal SQL statements then converting these to use bind variables will give significant improvements in latch contention in this area. See Note 62143.1 for issues affecting the shared pool. cache buffers lru chain latch Setting
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26706/viewspace-64583/，如需转载，请注明出处，否则将追究法律责任。