ITPub博客

首页 > Linux操作系统 > Linux操作系统 > "latch free" Reference Note

"latch free" Reference Note

原创 Linux操作系统 作者:yanggq 时间:2019-03-21 14:57:04 0 删除 编辑
This is a reference note for the wait event "latch free" which includes the following subsections:

From Metalink.oracle.com


See Note 61998.1 for an introduction to Wait Events.

Definition:

  • Versions:7.0 - 9.2 Documentation:
  • 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).

Individual Waits:

Parameters:

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

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 (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)

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.

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.
      See 
There 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.

Related:

Tracing User sessions Note 62160.1
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 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. Latch address9.0

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26706/viewspace-64583/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-29

  • 博文量
    30
  • 访问量
    21982