How to Determine the Blocking Session for Event: 'cursor: pin S wait on X' (文档 ID 786507.1)

In this Document

Goal

Solution

References

---

APPLIES TO:

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 note helps find the blocking session for mutex related wait event "cursor: pin S wait on X"

To Troubleshoot this 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.

A session waits on this event when requesting a mutex for shareable operations  related to pins (such as executing a cursor), but the mutex cannot be granted because it is being held exclusively by another session (which is most likely parsing the cursor).

The column P2RAW in v$session or v$session_wait gives the blocking session for wait event  cursor: pin S wait on X.

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 

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

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;

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.

In 11g, the blocking session can be found directly 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  
---- ------- ------------- ---------------- ----------------------- ----------  
 125    8190 3d3pd7g7dwuf6              135 VALID                   cursor: pin S wait on X

 

Likely Causes

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.

REFERENCES

BUG:7568642 - BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X"
NOTE:1349387.1 - Troubleshooting 'cursor: pin S wait on X' waits.
NOTE:1356828.1 - FAQ: 'cursor: mutex ..' / 'cursor: pin ..' / 'library cache: mutex ..' Type Wait Events
NOTE:1377446.1 - * Troubleshooting Performance Issues
NOTE:1377998.1 - Troubleshooting: Waits for Mutex Type Events

相关内容

产品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Personal Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition > RDBMS > Database Level Performance Issues (not SQL Tuning)
• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Standard Edition > Generic RDBMS > Database Level Performance Issues (not SQL Tuning)

关键字

CURSOR PIN S WAIT ON X; DATABASE HANG; V$SESSION_WAIT