"Translated from MoS article" find the ' cursor:pin S wait on X ' Wait event blocker session (i.e.: Holder session)

Find the ' Cursor:pin S wait on X ' waiting event blocker session (i.e.: Holder session)

From:
How to determine the Blocking Session for Event: ' Cursor:pin S wait on X ' (Doc ID 786507.1)

Suitable for:
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.

Goal:
This article helps to find the blocking session for the ' Cursor:pin S wait on X ' wait event
For this wait event, see:
Document 1377998.1 troubleshooting:waits for Mutex Type Events
Document 1349387.1 Troubleshooting ' cursor:pin S wait on X ' waits
Document 1356828.1 FAQ: ' Cursor:mutex ... '/' Cursor:pin: '/' Library Cache:mutex ... ' Type Wait Events
Document 1377446.1 Troubleshooting performance issues

Solution:
Cursor:pin S wait on X
When a session requests a mutex for a shared operation associated with a pin (such as executing a cursor), the session will have Cursor:pin S wait on X waiting event.
However, the mutex cannot be authorized because the mutex is being held in exclusive mode by another session (e.g. parsing the cursor)

The P2raw column in V$session or V$session_wait gives the blocker session of the Cursor:pin S wait on X wait event (holder 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.

More Simple conversions:

Sql> Select P2raw,to_number (substr (To_char (Rawtohex), P2raw), ' 1,8 ') SID from XXXXXXXX where event = ' Cursor:pin S wait on X '; P2raw SID-------------------0000001f00000000 bit platforms8 bytes is used. Top 4 bytes hold the session ID (if the mutex was held X) Bottom 4 bytes hold the ref count (if the mutex is held S). Platforms 4 bytes is used. Top 2 bytes hold the session ID (if the mutex was held X) Bottom 2 bytes hold the ref count (if the mutex is held S). Sql> Select P1, P2raw, COUNT (*) from v$session where event = ' Cursor:pin S wait on X ' and wait_time = 0 g Roup by P1, P2raw;? P1 = The mutex idthis has the same definition as V$mutex_sleep_history.mutex_identifier?p2raw = 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's 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; As a result of the Bug 7568642 blocking_session EMPTY for "Cursor:pin S WAIT on X" The blocking_session was not populated in 10.2.The Bug is a fixed in 11g r1.--this is a bug

In 11g, the blocker session can be queried using the following SQL:

Sql> Select Sid,serial#,sql_id,blocking_session,blocking_session_status,event from      v$session where EVENT = ' Cursor:pin S wait on X ' SID serial# sql_id        blocking_session blocking_session_status EVENT  --------------------- ----------------------------------------------------    8190 3d3pd7g7dwuf6              135 VALID                   Cursor:pin S wait on X

Possible causes:
One of the most likely causes of Cursor:pin S to wait on X are high parsing time. Therefore the reason for the high-parse time should be investigated.

"Translated from MoS article" find the ' cursor:pin S wait on X ' Wait event blocker session (i.e.: Holder session)