cursor pin S wait on X

cursor pin S wait on X

1.等待事件说明

1.1 等待事件说明

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. 

# 通常，这个等待事件只是一个现象并不是原因，通常是需要更深层次的优化或者已知的其他问题导致。

cursor: pin S ， cursor: pin X ， cursor: pin S wait on X 这三个等待事件，实际上就是替代了 cursor 的 library cache pin ， pin S 代表执行（ share pin ）， pin X 代表解析（ exclusive pin ）， pin S wait on X 代表执行正在等待解析操作， pin S wait on X 一定是等待以修改为目的的 X 排他操作，如果是多版本 examination （察看）父游标会发生父游标的 cursor pin S 。

这里需要强调一下，它们只是替换了访问 cursor 的 library cache pin ，而对于访问 procedure 这种实体对象，依然是传统的 library cache pin ，所以可以利用这一特性，模拟 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原因

通常造成 Cursor: pin S wait on X的原因主要有以下几个方面：

ü   shared pool大小是否合适。

如果shared pool大小通常小于负载，则通常表现为Cursor: pin S wait on X.如果启用了AMM，这通常不是一个问题。

ü   频繁的硬解析

如果硬解析频率非常高，通常会发生这个等待事件以及伴随cursor mutex X/cursor mutex S/latch: shared pool

ü   High Version Counts

当由于某些原因（session参数，数据库参数，直方图等）导致sql版本数量过高，每次执行SQL时将要examined（查看）一个非常长的子游标链（handle list）将会导致硬解析成本很高以及软解析成本也很高，导致其他非解析会话产生这个等待事件。

ü   已知的bug导致。

ü   解析失败，AWR中解析失败统计会很高。

可以通过查询x$kglob或者，event 10035找到解析失败语句。

Document 1353015.1 How to Identify Hard Parse Failures

2.等待事件诊断方法

2.1 收集AWR/ADDM报告

SQL>@$oracle_HOME/rdbms/admin/awrrpt.sql
SQL>@$ORACLE_HOME/rdbms/admin/addmrpt.sql

2.2 system state dump

如果awr以及addm、ash，没有明显有问题sql，system state dump可以帮助捕获阻塞会话以及定位潜在问题。

(a) Non-Rac
sqlplus "/ as sysdba"
oradebug setmypid
oradebug unlimit
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
wait 90 seconds
oradebug dump systemstate 258
quit
(b) RAC
$ sqlplus '/ as sysdba'
oradebug setmypid
oradebug unlimit
oradebug setinst all
oradebug -g all hanganalyze 4
oradebug -g all dump systemstate 258
quit

2.3 errorstack

可以使用errorstack获得进程信息，对已经定位的阻塞者会话使用errorstack，帮助定位问题。

$ sqlplus
SQL> oradebug setospid <p.spid from above>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
<< wait 1min>>
oradebug dump errorstack 3
exit

2.4 如何判断哪个会话造成了cursor: pinS wait on X

v$session或v$session_wait的p2raw给出了造成cursor: pin S wait on X的会话，不同平台不同bytes代表了sid，需要转换成10进制：

SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'XXXXXXXX') sid
from v$session
where event = 'cursor: pin S wait on X';
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).

2.5 可以使用如下sql找到阻塞会话以及执行sql

SELECT s.sid, t.sql_text
FROM v$session s, v$sql t
WHERE s.event LIKE '%cursor: pin S wait on X%'
AND t.sql_id = s.sql_id

3.模拟测试

创建表：
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 a30
col p1 for 999999999999999999999
col p2 for 999999999999999999999
col p3 for 999999999999999999999
col sid for 999
col bs for 99999
select event,p1,p1raw,p2,p2raw,p3,sid,blocking_session bs from v$session where sid in (59,65);
EVENT                                              P1 P1RAW                                P2 P2RAW                                P3  SID     BS
------------------------------ ---------------------- ---------------- ---------------------- ---------------- ---------------------- ---- ------
cursor: mutex S                             978779761 000000003A56FE71           279172874241 0000004100000001           150323855360   59
library 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   59
library cache lock                         1969550192 000000007564F370             2096861920 000000007CFB92E0                5373954   65
 
20:09:34 SYS@honor1 > /
 
EVENT                                              P1 P1RAW                                P2 P2RAW                                P3  SID     BS
------------------------------ ---------------------- ---------------- ---------------------- ---------------- ---------------------- ---- ------
cursor: mutex S                             978779761 000000003A56FE71           279172874240 0000004100000000           150323855360   59
cursor: pin S wait on X                     978779761 000000003A56FE71           253403070464 0000003B00000000            21474836480   65

3.1 解析等待事件

cursor: mutex S:

查询造成cursor: mutex S的sql：
 
select sql_id,sql_text,version_count,executions from v$sqlarea where hash_value=&p1;
 
# 查看mutex类型。
select * from x$mutex_sleep_history where mutex_identifier=&p1 and blocking_session=(select to_number('&p2_topbytes','xxxxxxxxxxxxxxx') from dual);

library cache loclk

select kglnaobj,kglnaown,kglhdadr from x$kglob where kglhdadr=’&p1raw’;
KGLNAOBJ             KGLNAOWN                                                         KGLHDADR
-------------------- ---------------------------------------------------------------- ----------------
5cc6ce3e3a56fe71     $BUILD$                                                          000000007564F370
 
Oracle 在11.2 版本引入了Cursor Build Lock 机制，这一机制使得在某个父游标下创建子
游标的工作串行化。当获取Build Lock 时，需要持有Library Cache Lock，所以11.2版本更容易发生library cache lock。

3.2 各个等待事件发生原因

cursor: mutex S：当一个会话examination（查看）检索父游标时，需要持有父游标的library cache动态创建的mutex的S共享模式，此时其他会话也看查看，就会造成cursor: mutex S

library cache lock: 当硬解析时，需要获得build lock，build lock是排他性的，使在父游标下创建子游标串行化，此时如果其他会话也来创建子游标，则发生library cache lock等待build lock。

cursor pin S wait on X:当一个会话要共享一个子游标时，其他会话正在解析，则会话需要等待其他会话解析完成，然后共享cursor，此时就会发生cursor pin S wait on X。