oracle游标等待,cursor: pin S wait on X 等待事件

cursor: pin S整体描述

cursor: pin S A session waits on this event when it wants to update a shared mutex pin and another session

is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should

rarely be seen because a shared mutex pin update is very fast.(Wait Time: Microseconds)

--Parameter说明

P1 Hash value of cursor

P2 Mutex value

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

P3 Mutex where (an internal code locator) OR'd with Mutex Sleeps

--查询sql

SELECT a.*, s.sql_text

FROM v$sql s,

(SELECT sid,

event,

wait_class,

p1 cursor_hash_value,

p2raw Mutex_value,

TO_NUMBER (SUBSTR (p2raw, 1, 8), 'xxxxxxxx') hold_mutex_x_sid

FROM v$session_wait

WHERE event LIKE 'cursor%') a

WHERE s.HASH_VALUE = a.cursor_hash_value

cursor: pin S wait on X描述

- In previous versions of Oracle, library cache pin is protected by “library cache pin latch”.

- But in recent versions of Oracle(I believe it’s 10.2.0.2),

library cache pin for the cursor LCO is protected by mutext.

- Mutex is allocated per LCO, so it enables fine-grained access control.

“cursor: pin S wait on X” wait event is mostly related to mutex and hard parse.

- When a process hard parses the SQL statement, it should acquire exclusive

library cache pin for the corresponding LCO.

- This means that the process acquires the mutex in exclusive mode.

- Another process which also executes the same query needs to acquire the mutex

but it’s being blocked by preceding process. The wait event is “cursor: pin S wait on X”.

--发生cursor: pin S wait on X原因

Frequent Hard Parses

If the frequency of Hard Parsing is extremely high, then contention can occur on this pin.

High Version Counts

When Version counts become excessive, a long chain of versions needs to

be examined and this can lead to contention on this event

Known bugs

Bug 5907779 - Self deadlock hang on "cursor: pin S wait on X" (typically from DBMS_STATS) [ID 5907779.8]

Bug 7568642: BLOCKING_SESSION EMPTY FOR "CURSOR: PIN S WAIT ON X"