cursor: pin S wait on X

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

Systemwide wait details (eg: For waits seen in )

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7194105/viewspace-704492/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7194105/viewspace-704492/