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"