WAITEVENT: "cursor: pin S wait on X" Reference Note (文檔 ID 1298015.1)

"cursor: pin S wait on X" Reference Note

This is a reference note for the wait event  "cursor: pin S wait on X" which includes the following subsections:

• Brief definition
• Individual wait details (eg: For waits seen in <View:V$SESSION_WAIT>)
• Systemwide wait details (eg: For waits seen in <View:V$SYSTEM_EVENT>)
• Reducing waits / wait times
• Known Bugs

See  Note:61998.1 for an introduction to Wait Events.

Definition:

• Versions:10.2 - 11.2 Documentation:10.2 11.1 11.2

• Mutexes were introduced in Oracle 10.2.

  A session waits for "cursor: pin S wait on X" when it wants a mutex in S (share) mode on a specific cursor but some other session holds that same mutex in X (exclusive) mode. Hence the current session has to wait for the X mode holder to release the mutex.

  Mutexes are local to the current instance in RAC environments.

Individual Waits:

  Parameters:

• Versions 10.2.0.1 - 11.2.0.2
  • P1 = idn
  • P2 = value
  • P3 = where (where|sleeps in 10.2)
  • 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;      

    If the SQL_TEXT shown for the cursor is of the form " table_x_x_x_x" then this is a special internal cursor - see Note:1298471.1 for information about mapping such cursors to objects. 

    P1RAW is the same value in hexadecimal and it can be used to search in tracefiles for SQL matching to that hash value.

  • value Mutex value (includes details of holder)

    This is the mutex value. The value is made up of 2 parts:

    • High order bits contain the session id of the session holding the mutex
    • Low order bits contain a reference count (which should be 0 if there is an X mode holder)

    For 32bit platforms the blocking SID (high order bits) are in the top 2 bytes of a 4 byte value, whilst on 64bit platforms it is in the top 4 bytes of an 8 byte value. You can get the SID of the X holder from the P2 value using SQL of the form:

    SELECT decode(trunc(&&P2/4294967296),
               0,trunc(&&P2/65536),
    	     trunc(&&P2/4294967296)) SID_HOLDING_MUTEX
     FROM dual;
               

    P2RAW is the same value in hexadecimal - you can manually split off the high order bits and convert them to decimal to get the blocking SID. See  Note:786507.1 for an example of interpreting the RAW value.

  • where Where in the code the mutex is requested from

    The high order bits of P3 give a number which represents a location in the Oracle code where the mutex was requested from. In 10.2 the low order bits of P3 gives a sleep value. In 11g the low order bits are all 0. 

    Warning: In 10.2 the low order sleep value can overflow into the high order bits, especially on 32bit platforms, giving a bad location value

    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.

  Wait Time:

Typically each wait is a 10mS in releases up to (and including) 11.2.0.2 

A session will wait over and over on the same wait event until it acquires the mutex in S mode.

  Finding Blockers:

This specific wait implies that there is an X mode holder of the mutex at the time that this session requested it in S mode. Mutexes are instance local so in a RAC environment the holder is on the local instance. The holding session id is exposed in several ways:

• In 11g onwards session holding the mutex is shown in the 

  BLOCKING_SESSION

   column of 

  <View:V$SESSION>

   for the waiting session.

  One can find the blocking session using SQL of the form:

  SELECT SQL_ID, ACTION, BLOCKING_SESSION, BLOCKING_SESSION_STATUS
     FROM v$session 
    WHERE SID=&SID_OF_WAITING_SESSION;
   
             

• In 10g or 11g the session holding the mutex is shown in the high order bits of the P2 (value) wait parameter - see value above for how to find the blocking session from the P2 value .

If the blocking session remains the same for a long period of time then it is likely that session is taking a long time to parse the SQL statement indicated by the hash value in  P1 (idn).

Systemwide Waits:

There are 3 main scenarios that cause this event to show up system-wide:

• One blocker with many waiting sessions wanting the same mutex

  This should be handled like any long duration hang scenario. 

  ie: Find the blocker and find out what they are doing that is taking a long time and investigate why. 

  eg: If many sessions issue an identical SQL statement but for some reason the statement takes an excessively long time to parse then many concurrent sessions can end up waiting for the blocker to finish its parse.

• Changing blocker with many waiting sessions wanting the same mutex

  If the blocking session is changing frequently for the same  P1 (idn) value then it is likely that the SQL is not sharing for some reason and multiple sessions are having to build versions of the cursor. Find which SQL this is (See  P1 (idn) above) and then investigate if that cursor has a high VERSION_COUNT. 

  Eg: A typical example which could cause this would be a system using CURSOR_SHARING=SIMILAR for a heavily used SQL statement with a range predicate comparing a column to a literal (eg: where COL >  N where  N is a number which is different for each session). Such a statement would need a different child cursor for each distinct literal value - the statement itself does not take excessive time to parse, but lots of concurrent sessions are competing on the statement.

• Changing blockers and changing mutexes

  This might occur if the shared pool is under load (or under sized), or if there are frequent cursor invalidations / flushes leading to lots of hard parsing of lots of different SQL statements which are executed by many sessions concurrently.

Information to help drill into systemwide waits can be obtained from:

• V$ACTIVE_SESSION_HISTORY 

  Find common P1 (idn) values in the wait history for the "cursor: pin S wait on X" waits to home in on SQL which may be an issue. 

• V$MUTEX_SLEEP_HISTORY

  The MUTEX_IDENTIFIER column is the same as the P1 (idn) value exposed in V$SESSION_WAIT - it can be used to help identify problem SQL. 

• AWR and / or V$SQLAREA

  SQL with a high VERSION_COUNT can be a cause of these waits although it is advisable to ensure there is a link to the mutex waits using the above views. eg: A statement may correctly have a high VERSION_COUNT but never contribute to cursor pin waits.

Reducing Waits / Wait times:

As this wait involves a wait for an X holder then the main approach to reducing waits is to identify the reason for either long or frequent acquisition of the mutex/es in X mode and tackle that.

• For SQL with long parse times try to isolate that statement and use hints, outlines, plan management or other options to reduce the parse time. 
• For SQL with high version counts try to reduce the need for multiple versions. In extreme cases where many child cursors must be used then it can help to spread the contention by making the SQL from different clients (or groups of clients) slightly different. eg: By adding a literal comment to the SQL so that each session (or group of sessions) map to a different hash_value and different parent cursor.

Known Bugs

You can restrict the list below to issues likely to affect one of the following versions by clicking the relevant button: 

NB	Bug	Fixed	Description
5650841	Hang / deadlock from ANALYZE of cluster index
16191248	12.1.0.1.1, 12.1.0.2, 12.2.0.0	Hang from concurrent drop of on-commit materialized views or using DBMS_REDEFINITION
14295250	11.2.0.4, 12.1.0.1	Long parse time for large query with many nested views due to much time in epxression analysis code
14191508	11.2.0.3.8, 11.2.0.3.BP16, 11.2.0.4, 12.1.0.1	Slow row cache load due to SEG$ and INDSUBPART$ queries
14176247	11.2.0.4, 12.1.0.1	Many child cursors using Adaptive Cursor Sharing with binds (due to BIND_EQUIV_FAILURE)
16400122	12.2.0.0	Spikes in library cache mutex contention for SQL using SQL Plan Baseline
15850031	11.2.0.4, 12.2.0.0	Rare instance hang: deadlock between 'row cache lock' and 'cursor: pin S wait for X'
14469756	12.2.0.0	Partition pruning causes delay in TBL$OR$IDX$PART$NUM
14302813	11.2.0.4, 12.2.0.0	QC blocked / parse hang for parallel DML executed from remote stored procedure
14029891	11.2.0.4, 12.1.0.1	mutex deadlock having SQL baselines on recursive dictionary cursor
11927619	11.2.0.1.BP11, 11.2.0.2.BP07, 11.2.0.3, 12.1.0.1	DBMS_STATS slow on interval composite partitions
11855965	11.2.0.3, 12.1.0.1	Truncate partition takes long time doing recursive delete on MLOG$
10213073	11.2.0.2.8, 11.2.0.2.BP18, 11.2.0.3, 12.1.0.1	CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects
10171273	11.2.0.2.8, 11.2.0.2.BP08, 11.2.0.3, 12.1.0.1	Long parse time with non-equi subpartitioning under interval partitioning
9944129	11.2.0.1.BP12, 11.2.0.2, 12.1.0.1	SQL not shared due to INST_DRTLD_MISMATCH with global transaction
9935787	11.2.0.3, 12.1.0.1	Long parse time for large inlists - can cause 'cursor: pin S wait on X' waits
9694101	10.2.0.5.7, 11.2.0.2, 12.1.0.1	Hang / deadlock between "cursor: pin S wait on X" and "library cache lock" involving dictionary objects
9499302	10.2.0.5.5, 11.1.0.7.7, 11.2.0.1.BP08, 11.2.0.2, 12.1.0.1	Improve concurrent mutex request handling
9472669	11.2.0.1.BP12, 11.2.0.2, 12.1.0.1	'cursor: pin S wait on X' waits for invalid SQL over DB link
8508078	11.2.0.2, 12.1.0.1	Contention from many concurrent bad SQLs - superseded
12432089	11.2.0.3	library cache lock/cursor: pin Ss wait on S with parallel partition stats gathering
8441239	11.2.0.1	Library cache lock waits if long running TRUNCATE in progress
8348464	11.1.0.7.2, 11.2.0.1	CREATE SYNONYM and CREATE PACKAGE may incorrectly invalidate objects
7234778	11.2.0.1	Unnecessary "cursor: pin S wait on X" waits
5485914	10.2.0.4	Mutex self deadlock on explain / trace of remote mapped SQL
6143420	10.2.0.5, 11.1.0.6	Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"
6011045	10.2.0.5.5	DBMS_STATS causes deadlock between 'cursor: pin S wait on X' and 'library cache lock'
7462072	10.2.0.4.3, 10.2.0.5	Unnecessary "cursor: pin S wait on X" waits
5983020	10.2.0.4	MMON deadlock with user session executing ALTER USER
7226463	10.2.0.5	EXECUTE IMMEDIATE no releasing mutex or library cache pin
+	5907779	10.2.0.4	Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS)

• '*' indicates that an alert exists for that issue.
• '+' indicates a particularly notable issue / bug.
• 'I' indicates an install issue / bug included for completeness.
• 'P' indicates a port specific bug.
• Fixed versions use "BPnn" to indicate Exadata bundle nn.
• "OERI:xxxx" may be used as shorthand for ORA-600 [xxxx].

Note:

The above bug list shows only bugs tagged specifically as having caused "cursor: pin S wait on X" waits for customers. Any bug issue which causes high version counts or causes long parse times could also lead to such waits if there is some degree of concurrency .

Related:

Note:786507.1  Finding the blocking session for a "cursor: pin S wait on X" wait
  Note:1349387.1 Troubleshooting: 'cursor: pin S wait on X' waits
  Note:1377998.1 Troubleshooting: Waits for Mutex Type Events
      

相關内容

産品

• Oracle Database Products > Oracle Database > Oracle Database > Oracle Database - Enterprise Edition

關鍵字

BUGTAG_ADDBUGS