WAITEVENT: "latch free" Reference Note (文档 ID 34576.1)

***Checked for relevance on 25-Oct-2011***

"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

See  Note:61998.1  for an introduction to Wait Events.

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 behaviour

For 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.

---

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.

---

There is also a V$LATCH_MISSES view which may be of help 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.

Known Issues / Bugs:

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 
                           

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

• '*' indicates that an alert exists for that issue.
• '+' indicates a particularly notable issue / bug.
• See Note:1944526.1 for details of other symbols used