分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
"latch free" Reference Note
This is a reference note for the wait event "latch free" which includes the following subsections:- Brief definition
- Individual wait details (eg: For waits seen in <<View:V$SESSION_WAIT>>)
- Systemwide wait details (eg: For waits seen in <<View:V$SYSTEM_EVENT>>)
- Reducing waits / wait times
- Known Bugs
Definition:
- Versions: 7.0 - 11.2
Documentation: 11.2 11.1 10.2 10.1 - Latches are like short duration locks that protect critical bits of code. This wait indicates that the process is waiting for a latch that is currently busy (held by another process).
- For versions prior to 10g, there is an umbrella wait event called "latch free" that covers all latch waits. In these older releases typically has to look at the wait parameters and latch sleep information to get an idea of which latches are causing waits. In Oracle 10g or later, finding which latches are causing waits is easier because new wait event names have been introduced for the more common latch waits (eg. a session will wait on "latch: shared pool" instead of "latch free"). However, some latch waits do still use the "latch free" wait event and you will need to follow the procedure from Doc ID 413942.1 How to Identify Which Latch is Associated with a "latch free" wait , to obtain more information.
Individual Waits:
Parameters:
- P1 = Latch address
- P2 = Latch number
- P3 = Tries
- Latch address
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:
SELECT 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.
- Latch number
This is the latch number that indexes the V$LATCHNAME view:
SELECT * 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.
- Tries
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.
Wait Time:
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 may increase exponentially and does not include spinning on the latch (active waiting). The exact latch wait behaviourFor certain latches a waiting session may be posted once the latch is free. Oracle9i and later use this "latch posting" far more than Oracle8i (and earlier) releases.
The SECONDS_IN_WAIT figure in <<View:V$SESSION_WAIT>> shows the total time spent waiting for the latch including all sleeps.
Systemwide Waits:
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 <<View:V$LATCH>> (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 <<View:V$LATCH_CHILDREN>> :
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 <<View:V$LATCH_HOLDER>>
- Sessions with high latch waits in <<View:V$SESSION_EVENT>>
(Although it is important to note that innocent sessions may show high numbers of waits if some other session is repeatedly holding the latch)
Reducing Waits / Wait times:
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.There is also a V$LATCH_MISSES view which may be of help in more obscure cases:
These notes most apply to pre-10g but are kept here for now
In 10g onwards please see the individual "latch: xxx" wait.
The latches most likely to show high sleeps in pre-10g versions 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. See Note: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 <<Parameter:DB_BLOCK_LRU_STATISTICS>> to TRUE can adversely affect this latch - always ensure DB_BLOCK_LRU_STATISTICS is set to FALSE. From Oracle 7.3 it is possible to have multiple of these latches by specifying DB_BLOCK_LRU_LATCHES although this really needs multiple CPU's to be of most benefit. Heavy contention for this latch is generally due to heavy buffer cache activity which can be caused, for example, by: Repeatedly scanning large unselective indexes Oracle7/8.0 only: Sorting in buffer cache and not using SORT_DIRECT_WRITES (From Oracle8i onwards direct writes are always used for sorts) See Note:62172.1 for things you can do to reduce contention in the buffer cache. cache buffers chains latches Individual block contention can show up as contention for one of these latches. Each cache buffers chains latch covers a list of buffers in the buffer cache. If one or two child latches stand out from V$LATCH_CHILDREN then: In Oracle8: SELECT File# , dbablk, class, state FROM x$bh WHERE hladdr='&ADDR_OF_CHILD_LATCH' ; In Oracle7: SELECT dbafil FILE# , dbablk, class, state FROM x$bh WHERE hladdr='&ADDR_OF_CHILD_LATCH' ; If this list is short (3 to 10 buffers) then one of the buffers in this list is probably very 'hot' - ie: suffers from lots of concurrent access attempts. Repeatedly monitoring X$BH for this latch should show which blocks are always there and which are transient. In Oracle8i there are often far fewer "cache buffers chains" latches (especially with large buffer caches) and so there can be many buffers covered by a single hash latch. There is a touch-count column in X$BH in Oracle8i (X$BH.TCH) which can be used to see the HOT buffers. Hot buffers will typically have a high touch count.
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.
Known Issues / Bugs:
You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button:
'*' indicates that an alert exists for that issue. '+' indicates a particularly notable issue / bug. See Note:1944526.1 for details of other symbols used
NB Prob Bug Fixed Description II 18080199 "FOB s.o list latch" contention D II 14810472 11.2.0.4, 12.1.0.2, 12.2.0.0 "KKCN reg stat latch" contention when HA notification enabled II 17346196 12.1.0.1.1 ORA-29770 / RAC crash due to LCK stuck III 14095982 11.2.0.3.8, 11.2.0.3.BP18, 11.2.0.4, 12.1.0.1 "latch free" waits when result cache is used I 13843646 11.2.0.3.8, 11.2.0.3.BP21, 11.2.0.4, 12.1.0.1 Long latch wait on "gcs resource validate list" with many LMS processes II 12797620 11.2.0.3.10, 11.2.0.3.BP23, 11.2.0.4, 12.1.0.1 High gets on "kokc descriptor allocation latch" with high users II 18430495 11.2.0.4.6, 11.2.0.4.BP15, 12.1.0.2, 12.2.0.0 Contention for "ges group table" latches even with fix 18306487 present I 18306487 12.2.0.0 High 'latch free' for 'ges group table' latch seen with distributed transaction - superseded II 9168070 12.2.0.0 Database hangs when process is killed while acquiring latch 'process allocation' II 14665745 11.2.0.3.BP18, 11.2.0.4, 12.1.0.1 Excessive "Result Cache: RC Latch" latch gets using DBMS_RESULT_CACHE.memory_report II 14382262 11.2.0.4, 12.1.0.1 latch free wait event in"kokc descriptor allocation latch" I 12830174 11.2.0.3.BP12, 11.2.0.4, 12.1.0.1 spwaning many processes w/ heavy i/os in parallel may cause asm latch contention - 11842991 11.2.0.2.5, 11.2.0.2.BP13, 11.2.0.2.GIPSU05, 11.2.0.3, 12.1.0.1 Spin during space allocation for concurrent SecureFile / DBFS writes II 10623249 11.2.0.2.4, 11.2.0.2.BP06, 11.2.0.3, 12.1.0.1 High "latch free" waits when Resource Manager is used II 10359307 11.2.0.3.10, 11.2.0.3.BP23, 11.2.0.4, 12.1.0.1 High number of sleeps for the "kokc descriptor allocation" latch II 10130415 11.2.0.3, 12.1.0.1 Latch contention on "resmgr group change latch" II 10095393 11.2.0.3, 12.1.0.1 "latch fee" waits on "enqueue" latch - 9720727 11.2.0.2, 12.1.0.1 Active session feature missing optimization when CPU scheduling is turned off II 18961101 11.2.0.4.BP11 Increased latch free waits on "resmgr:schema config" latch when using Resource Manager I 7500792 10.2.0.5, 11.1.0.7.2, 11.2.0.1 High gets on "kokc descriptor allocation latch" II 5918642 10.2.0.4, 11.1.0.7, 11.2.0.1 Heavy latch contention with DB_CACHE_ADVICE on III 5751672 10.2.0.4, 11.1.0.6 "In memory undo latch" / "undo global data" latch contention from kturimugur - 4758819 9.2.0.8, 10.2.0.3, 11.1.0.6 Latch contention possible on buffer cache hash latches II 4742607 9.2.0.8, 10.2.0.3, 11.1.0.6 "cache buffer chains" latch contention from concurrent index range scans + - 4480100 10.2.0.2, 11.1.0.6 Latch contention for "row cache objects" latch covering DC_USERS - 4057920 10.2.0.1 Waits for "longops free list" latch after kill session - 3885860 9.2.0.7, 10.1.0.4, 10.2.0.1 Latch contention form concurrent reconfiguration and SYSTEMSTATE dump I 3650599 9.2.0.7, 10.1.0.5, 10.2.0.1 "row cache objects" latch contention for dc_segments with many space operations II 3611471 9.2.0.6, 10.1.0.4, 10.2.0.1 High latch waits for "cache buffers chain" latch possible - 3452409 9.2.0.6, 10.1.0.3, 10.2.0.1 STATISTICS_LEVEL >= TYPICAL can cause latch contention under load II 3443747 9.2.0.6, 10.1.0.3, 10.2.0.1 A session can spin holding a library cache latch during fixed table lookup - 3561040 9.2.0.6, 10.1.0.2 LMS process may hang waiting for "KJC: wait for msg sends to complete" II 2530125 9.2.0.7, 10.1.0.2 Hang possible with "enqueue hash chains" latch held during deadlock detection - 4401702 9.2.0.8 Database hang after OERI[4519] error - 3875044 9.2.0.8 "cache buffer chains" latch held by non existing process I 4241125 9.2.0.7 Sessions may block on "transaction branch allocation" latch - 3870235 9.2.0.7 Hang waiting for "enqueues" latch when using Resource Manager