The address of the latch that the process is waiting for. The hexadecimal value of P1 (P1RAW) can be used to determine which latch is waited for thus:Latch numberSELECT name, 'Child '||child#, gets, misses, sleeps FROM v$latch_children WHERE addr='&P1RAW' UNION SELECT name, null, gets, misses, sleeps FROM v$latch WHERE addr='&P1RAW' ;This will show "Child N" in the second column if the latch is a child latch.
This is the latch number that indexes the V$LATCHNAME view:TriesSELECT * FROM v$latchname WHERE latch# = &P2 ;Note that the latch number for a given set of latches can vary between releases / platforms so it is best to avoid hard coding P2 or LATCH# in SQL scripts.
This is basically a counter that counts the number of times we tried to get the latch (slow with spinning) and the process had to sleep. See the"Wait Time" notes below.
When a session waits on latch free it effectively sleeps for a short time then re-tests the latch to see if it is free . If it still cannot be acquired then P3 is incremented and the session waits again. The wait time increases exponentially and does not include spinning on the latch (active waiting). The maximum wait time depends on the number of latches that the process is holding. There is an exponential back off (up to 2 seconds) between each attempt to get the latch.
For certain latches a waiting session may be posted once the latch is free. Oracle9i uses this "latch posting" far more than Oracle8i (and earlier) releases.
The SECONDS_IN_WAIT figure in
shows the total time spent waiting for the latch including all sleeps.
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/，如需转载，请注明出处，否则将追究法律责任。