找到’cursor: pin S wait on X’ 等待事件的阻塞者session(即：持有者session)
来源于：How to Determine the Blocking Session for Event: ‘cursor: pin S wait on X’ (Doc ID 786507.1)
适用于：Oracle Database – Enterprise Edition – Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Oracle Database – Personal Edition – Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Oracle Database – Standard Edition – Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]Information in this document applies to any platform.
目标：本文对找到’cursor: pin S wait on X’ 等待事件的阻塞者session 有帮助关于该等待事件，，请看：Document 1377998.1 Troubleshooting: Waits for Mutex Type EventsDocument 1349387.1 Troubleshooting ‘cursor: pin S wait on X’ waitsDocument 1356828.1 FAQ: ‘cursor: mutex ..’ / ‘cursor: pin ..’ / ‘library cache: mutex ..’ Type Wait EventsDocument 1377446.1 Troubleshooting Performance Issues
解决方案：Cursor: pin S wait on X当一个session为一个与pin相关的共享操作（such as executing a cursor）请求一个mutex时,该session会有Cursor: pin S wait on X等待事件。但是该mutex不能被授权，因为该mutex正在被其他session以排他模式持有（比如 parsing the cursor）
v$session or v$session_wait中的p2raw列 给出了 cursor: pin S wait on X等待事件的阻塞者session（持有者session）
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.
更简单的换算：
SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sidfrom v$sessionwhere event = 'cursor: pin S wait on X'; P2RAWSID —————- — 0000001F0000000031 64 bit platforms8 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).SQL> select p1, p2raw, count(*) from v$sessionwhere event ='cursor: pin S wait on X'and wait_time = 0group by p1, p2raw;p1 = the mutex IdThis has the same definition as v$mutex_sleep_history.mutex_identifierp2raw = holding Session Id | Ref CountThe 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,EVENTfrom v$session where SID=31;As a result of Bug 7568642 BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X" the blocking_session is not populated in 10.2.The bug is fixed in 11g R1.–这是一个bug
在11g中，阻塞者session可以用下面的sql 查询到：
SQL> select sid,serial#,SQL_ID,BLOCKING_SESSION,BLOCKING_SESSION_STATUS,EVENTfrom v$session where event ='cursor: pin S wait on X' SID SERIAL# SQL_IDBLOCKING_SESSION BLOCKING_SESSION_STATUS EVENT —- ——- ————- —————- ———————– ———- 125 8190 3d3pd7g7dwuf6135 VALIDcursor: pin S wait on X
可能的原因：One of the most likely causes of cursor: pin S wait on X is high parsing time. Therefore the reason for the high parse time should be investigated.
绊脚石乃是进身之阶。
