首页 > Linux操作系统 > Linux操作系统 > latch: row cache objects 和cursor: pin S wait on X共同出现

latch: row cache objects 和cursor: pin S wait on X共同出现

原创 Linux操作系统 作者:gaopengtttt 时间:2013-11-13 19:21:01 0 删除 编辑

 Snap Id Snap Time Sessions Cursors/Session
Begin Snap: 5980 11-Nov-13 16:00:37 1403  39.6
End Snap: 5981 11-Nov-13 16:31:29 1422  42.8
Elapsed:    30.86 (mins)   
DB Time:    3,660.60 (mins)   

Per Second Per Transaction
Redo size:  1,185,514.75  6,235.28
Logical reads:  683,361.22  3,594.17
Block changes:  2,972.67  15.63
Physical reads:  6,201.95  32.62
Physical writes:  621.67  3.27
User calls:  8,220.82  43.24
Parses:  2,793.81  14.69
Hard parses:  319.52  1.68
Sorts:  1,180.36  6.21
Logons:  12.61  0.07
Executes:  31,893.98  167.75
Transactions:  190.13 

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
latch: row cache objects  6,500,460  56,331  9  25.6 Concurrency
CPU time    50,195    22.9 
cursor: pin S wait on X  2,073,123  40,372  19  18.4 Concurrency
db file sequential read  5,758,293  17,702  3  8.1 User I/O
enq: TX - row lock contention  6,570  12,384  1,885  5.6 Application

latch: row cache objects
row cache objects kqrpre: find obj 0 2,922,441 2,564,411
row cache objects kqreqd: reget 0 1,873,773 1,905,652
row cache objects kqreqd 0 1,682,121 2,008,663
WAITEVENT: "latch: row cache objects" Reference Note (Doc ID 1550722.1)
row cache objects kqrpre: find obj,row cache objects kqreqd: reget,row cache objects kqreqd进行入手
文档源:Contention on 'row cache objects' Latch After Upgrade from 10.2 to (Doc ID 1485410.1)
The 'row cache objects' latch is called most frequently from 'kqrpre: find obj'. This module tries to find details of an object being parsed in the row cache. During parse, the row cache is searched. The process searches through a linked list protected by the 'row cache objects' latch to find the object in the cache that it needs. When other processes are also parsing and looking through the row cache this may cause contention; especially if the parse activity is excessive or unnecessary.

Top 5 Timed Foreground Events

Event                    Waits      Time(s) Avg wait (ms)  DB time Wait Class
latch: row cache objects 58,291,873 130,700             2    63.18 Concurrency
DB CPU                       60,563   29.27  
db file sequential read   2,994,299  14,461             5     6.99 User I/O
library cache lock            3,179   1,662           523     0.80 Concurrency
gc cr grant 2-way         1,233,503     981             1     0.47 Cluster

 Latch Name        Where	         NoWait Misses Sleeps     Waiter Sleeps
 row cache objects kqrpre: find obj                  0 10,332,326     8,906,124
 row cache objects kqreqd: reget                     0  7,888,165     8,503,625
 row cache objects kqreqd                            0  7,677,074     8,489,832
 row cache objects kqrso                             0      4,127         3,622
 row cache objects kqrpre: init complete             0        143            88

1) Rewrite the application to use bind variables to lower the hard parse rate from 350/second
2) Enable the cursor_sharing = force
3) Do not use first_rows_x optimization and use all_rows instead
4) Set optimizer_feature_enabled =
5) Set event 10089 level 1 to disable index sorting.

 Latch Name        Where          NoWait Misses Sleeps     Waiter Sleeps
 row cache objects kqrpre: find obj                  0 10,332,326     8,906,124
 row cache objects kqreqd: reget                     0  7,888,165     8,503,625
 row cache objects kqreqd                            0  7,677,074     8,489,832
 row cache objects kqrso                             0      4,127         3,622
 row cache objects kqrpre: init complete             0        143            88
cursor: pin S wait on X 
cursor: pin S整体描述
cursor: pin S A session waits on this event when it wants to update a shared mutex pin and another session
is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should
rarely be seen because a shared mutex pin update is very fast.(Wait Time: Microseconds)
P1 Hash value of cursor
P2 Mutex value
64 bit platforms
8 bytes are used.
Top 4 bytes hold the session id (if the mutex is held X)
Bottom 4 bytes hold the ref count (if the mutex is held S).
32 bit platforms
4 bytes are used.
Top 2 bytes hold the session id (if the mutex is held X)
Bottom 2 bytes hold the ref count (if the mutex is held S).
P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps
SELECT a.*, s.sql_text
  FROM v$sql s,
       (SELECT sid,
               p1 cursor_hash_value,
               p2raw Mutex_value,
               TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid
          FROM v$session_wait
         WHERE event LIKE 'cursor%') a
 WHERE s.HASH_VALUE = a.cursor_hash_value
cursor: pin S wait on X描述
- In previous versions of Oracle, library cache pin is protected by “library cache pin latch”.
- But in recent versions of Oracle(I believe it’s,
  library cache pin for the cursor LCO is protected by mutext.
- Mutex is allocated per LCO, so it enables fine-grained access control.
“cursor: pin S wait on X” wait event is mostly related to mutex and hard parse.
- When a process hard parses the SQL statement, it should acquire exclusive
  library cache pin for the corresponding LCO.
- This means that the process acquires the mutex in exclusive mode.
- Another process which also executes the same query needs to acquire the mutex
  but it’s being blocked by preceding process. The wait event is “cursor: pin S wait on X”.
--发生cursor: pin S wait on X原因
Frequent Hard Parses
If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.
High Version Counts
When Version counts become excessive, a long chain of versions needs to
be examined and this can lead to contention on this event

可以看出MUTEX在某些情况下会代替LIBRARY CACHE PIN来保护LCO,如果要进行硬解析和ALTER 操作必须以X模式占用

1) Rewrite the application to use bind variables to lower the hard parse rate from 350/second
2) Enable the cursor_sharing = force
这里我们可以看到硬解析 350每秒 代表一个ORACLE认为不能容忍的程度,而我们这里是319,虽然没有达到350但是也达到了一个

来自 “ ITPUB博客 ” ,链接:,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
wxh gp_22389860 <<深入理解MySQL主从原理专栏>> 发布 可加WX了解


  • 博文量
  • 访问量