首页 » ORACLE 9i-23ai » Troubleshooting wait event :cursor: pin S wait on X

Troubleshooting wait event :cursor: pin S wait on X

  • 2011/11/30
  • ORACLE 9i-23ai
  • 2,726 views
  • Troubleshooting wait event :cursor: pin S wait on X已关闭评论

从oracle10g(10.2.0.2)开始 ，开始启用mutex来替换以前的library cache bin latch，这是一种os级更低层的轻量级锁，但是也不是那么完美，有时会出现events  cursor: pin S wait on X
通过隐藏参数来禁用mutex

alter system set _kks_use_mutex_pin=false scope=spfile;

shutdown immediate ;

startup;

Top 5 Timed Events

Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
cursor: pin S wait on X 1,771,367 19,072 11 55.1 Concurrency
CPU time 15,750 45.5
log file parallel write 2,240 7 3 .0 System I/O
control file parallel write 2,078 6 3 .0 System I/O
log file sync 1,508 4 3 .0 Commit

cursor: pin SA 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)
Parameter Description

P1 Hash value of cursor

idn Mutex identifier (gives hash_value of SQL)

This is the mutex identifier value which matches to the HASH_VALUE of the SQL statement that we are waiting to get the mutex on. The SQL can usually be found using the IDN value in a query of the form:

 SELECT sql_id, sql_text, version_count 
   FROM V$SQLAREA where HASH_VALUE=&IDN;

P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)

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

 

The top bytes of p2raw is the blocker.  It is in hex so needs to be converted in decimal.

SQL> select p2raw from v$session where event = ‘cursor: pin S wait on X’;

P2RAW
—————-
0000001F00000000
<SID>  <RefCnt>

The top bytes of p2raw is the blocker.
Taking 0000001F (the first 8 bytes) and converting to decimal gives session id 31.

Or simply:

SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),’XXXXXXXX’) sid
from v$session
where event = ‘cursor: pin S wait on X’;P2RAW               SID
—————-    —
0000001F00000000     31
SQL> select p1, p2raw, count(*) from v$session
where event =’cursor: pin S wait on X’
and wait_time = 0
group by p1, p2raw;

 

  • p1 = the mutex Id
    This has the same definition as v$mutex_sleep_history.mutex_identifier
  • p2raw = holding Session Id | Ref Count
    The most significant bytes always store the Holding Session Id (Holding SId).
    The least significant bytes always store the Ref Count.

The blocking session can be queried to see what it is doing and if anyone is blocking it.

SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENT
from v$session where SID=31;
P3 Mutex where (an internal code locator) OR’d with Mutex Sleeps

The high order bits of P3 can be mapped to a location name thus:

 SELECT decode(trunc(&&P3/4294967296),
	    0,trunc(&&P3/65536),
	      trunc(&&P3/4294967296)) LOCATION_ID
 FROM dual;

Use the LOCATION_ID returned above in this SQL:

 SELECT MUTEX_TYPE, LOCATION 
   FROM x$mutex_sleep
  WHERE mutex_type like 'Cursor Pin%' 
    and location_id=&&LOCATION_ID;

The location names can be quite cryptic but are sometimes useful in diagnosing the cause of waits.

 

What causes ‘Cursor: pin S wait on X’ waits?

  • 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
  • Known bugs

——-extracts

每个child cursor下面都有一个mutexes这样的简单内存结构，当有session要执行该SQL而需要pin cursor操作的时候，session只需要以shared模式set这个内存位+1，表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock；但在同一时间，只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。如果因为session并行太多，而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。
当看到系统有很多session等待cursor: pin S事件的时候，要么是CPU不够快，要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是Capacity的问题，则可以升级硬件。如果是因为SQL的并行太多,减少软解析

Some fix-ups

1. Some backgrounds
– 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 10.2.0.2), library cache pin for the cursor LCO is protected by mutext.
– Mutex is allocated per LCO, so it enables fine-grained access control.

2. “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”.

Some bugs would make the contention worse as many metalink notes describe.

3. Cursor mutex is a replacement of library cache pin latch for cursor, not library cache latch.

4. As of 11g, library cache latch is also replaced with mutex. Now, each library cache bucket is protected by independent mutexes, which enables fine-grained access control.

打赏

,

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：