Shulou(Shulou.com)06/01 Report--

Cursor pin S wait on X1. Wait event description 1.1 wait event description

A cursor wait is associated with parsing in some form. A session may wait for this event when it is trying to get a mutex

Pin in Share mode but another session is holding the mutex pin on the same cursor object in exclusive. Frequently, waits

For 'Cursor: pin S wait on X' is a symptom and not the cause. There may be underlying tuning requirements or known

Issues.

In general, this wait event is just a phenomenon and not a cause, usually caused by deeper optimization or other known problems.

The three waiting events cursor: pin S, cursor: pin X, cursor: pin S wait on X are actually library cache pin instead of cursor. Pin S represents share pin, pin X represents exclusive pin, pin S wait on X represents waiting for parsing operation, pin S wait on X must be waiting for X exclusive operation for modification. If it is a multi-version examination (view) parent cursor, the parent cursor cursor pin S will occur.

It needs to be emphasized here that they only replace the library cache pin that accesses cursor, while for entity objects such as procedure, it is still the traditional library cache pin, so you can take advantage of this feature to simulate library cache pin/lock.

A session waits for this event when it is requesting a shared mutex pin and another

Session is holding an exclusive mutex pin on the same cursor object.

Wait Time: Microseconds

Parameter Description

P1 Hash value of cursor

P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive

Mode, and bottom two bytes usually hold the value 0)

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

1.2 cursor pin S wait on X reason

The main reasons for Cursor: pin S wait on X are as follows:

Whether the size of ü shared pool is appropriate.

If the shared pool size is usually smaller than the load, it is usually represented as Cursor: pin S wait on X. If AMM is enabled, this is usually not a problem.

Frequent hard parsing

If the frequency of hard parsing is very high, this wait event usually occurs along with cursor mutex X/cursor mutex S/latch: shared pool

ü High Version Counts

When the number of SQL versions is too high due to some reasons (session parameters, database parameters, histograms, etc.), a very long subcursor chain (handle list) will be examined each time SQL is executed, which will lead to high hard parsing costs and high soft parsing costs, resulting in this wait event for other non-parsing sessions.

Bug is known to cause.

U parsing fails, and the resolution failure statistics in AWR will be very high.

You can find the parsing failure statement by querying x$kglob or event 10035.

Document 1353015.1 How to Identify Hard Parse Failures

two。 Wait for event Diagnostic method 2.1 collect AWR/ADDM report SQL > @ $ORACLE_HOME/rdbms/admin/awrrpt.sqlSQL > @ $ORACLE_HOME/rdbms/admin/addmrpt.sql2.2 system state dump

If awr as well as addm and ash, there is no obvious problem sql,system state dump can help capture blocked sessions and locate potential problems.

(a) Non-Racsqlplus "/ as sysdba" oradebug setmypidoradebug unlimitoradebug dump systemstate 258wait 90 secondsoradebug dump systemstate 258wait 90 secondsoradebug dump systemstate 258quit (b) RAC$ sqlplus'/ as sysdba'oradebug setmypidoradebug unlimitoradebug setinst alloradebug-g all hanganalyze 4oradebug-g all dump systemstate 258quit2.3 errorstack

You can use errorstack to get process information and use errorstack for blocked sessions that have been located to help locate the problem.

$sqlplusSQL > oradebug setospid oradebug dump errorstack 3 > oradebug dump errorstack 3 > oradebug dump errorstack 3exit2.4 how to determine which session caused cursor: pinS wait on X

The p2raw of v$session or v$session_wait gives the session that causes cursor: pin S wait on X. Different bytes on different platforms represents sid and needs to be converted to decimal:

SQL > select p2raw recording number (substr (to_char (rawtohex (p2raw)), 1mai 8), 'XXXXXXXX') sidfrom v$sessionwhere event =' cursor: pin S wait on Xerox pin S wait on 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).

You can use the following sql to find blocking sessions and execute sqlSELECT s.sid, t.sql_textFROM v$session s, v$sql tWHERE s.event LIKE'% cursor: pin S wait on X%'AND t.sql_id = s.sql_id3. Simulation test creation table: create table t (id number); session1:select * from v$mystat where statistic#=0;DECLARE a number;BEGIN FOR c IN 1.. 10000 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj =' | | c; EXECUTE IMMEDIATE 'SELECT count (*) FROM t' into a; END LOOP;END;/ session2:select * from v$mystat where statistic#=0;DECLARE a number;BEGIN FOR c IN 1.. 10000 LOOP EXECUTE IMMEDIATE 'ALTER SESSION SET optimizer_index_cost_adj =' | | c EXECUTE IMMEDIATE 'SELECT count (*) FROM t' into a; END LOOP;END;/ session3:col event for a30col p1 for 999999999999999999999col p2 for 999999999999999999999col p3 for 999999999999999999999col sid for 999col bs for 99999select event,p1,p1raw,p2,p2raw,p3,sid,blocking_session bs from v$session where sid in (59 Magi 65) EVENT P1 P1RAW P2 P2RAW P3 SID BS -cursor: mutex S 978779761 000000003A56FE71 279172874241 00004100000001 150323855360 59library cache lock 1969550192 000000007564F370 2096862432 000000007CFB94E0 5373955 65 20:09:33 SYS@honor1 > / EVENT P1 P1RAW P2 P2RAW P3 SID BS- -library cache lock 1969550192 000000007564F370 1700133696 000000006555FB40 5373955 59library cache lock 1969550192 000000007564F370 209861920 000000007CFB92E0 5373954 65 20:09:34 SYS@honor1 > / EVENT P1 P1RAW P2 P2RAW P3 SID BS -cursor: mutex S 978779761 000000003A56FE71 279172874240 00004100000000 150323855360 59cursor: pin S wait on X 978779761 000000003A56FE71 253403070464 0000003B00000000 21474836480 653.1 parsing wait event

Cursor: mutex S:

The query causes the sql: select sql_id,sql_text,version_count,executions from v$sqlarea where hash_value=&p1; # of cursor: mutex S to see the mutex type. Select * from x$mutex_sleep_history where mutex_identifier=&p1 and blocking_session= (select to_number ('& p2headed topbytes) from dual)

Library cache loclk

Select kglnaobj,kglnaown,kglhdadr from x$kglob where kglhdadr='&p1raw' KGLNAOBJ KGLNAOWN KGLHDADR -5cc6ce3e3a56fe71 $BUILD$ 000000007564F370 Oracle introduced the Cursor Build Lock mechanism in version 11.2 This mechanism serializes the work of creating child cursors under a parent cursor. When you get the Build Lock, you need to hold the Library Cache Lock, so version 11.2 is more likely to have library cache lock. 3.2 the cause of each waiting event

Cursor: mutex S: when a session examination (view) retrieves the parent cursor, the S sharing mode of the mutex dynamically created by the library cache holding the parent cursor is required, and other sessions are also viewed, resulting in cursor: mutex S

Library cache lock: when hard parsing, you need to obtain that build lock,build lock is exclusive, so that creating child cursors under the parent cursor is serialized. In this case, if other sessions also create child cursors, library cache lock waits for build lock.

Cursor pin S wait on X: when a session is about to share a child cursor and other sessions are parsing, the session needs to wait for the other session to finish parsing, and then share the cursor, and cursor pin S wait on X occurs.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.