cursor pin S wait on X

cursor pin S wait on X

1.等待事件說(shuō)明

1.1 等待事件說(shuō)明

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. 

# 通常，這個(gè)等待事件只是一個(gè)現(xiàn)象并不是原因，通常是需要更深層次的優(yōu)化或者已知的其他問(wèn)題導(dǎo)致。

cursor: pin S ， cursor: pin X ， cursor: pin S wait on X 這三個(gè)等待事件，實(shí)際上就是替代了 cursor 的 library cache pin ， pin S 代表執(zhí)行（ share pin ）， pin X 代表解析（ exclusive pin ）， pin S wait on X 代表執(zhí)行正在等待解析操作， pin S wait on X 一定是等待以修改為目的的 X 排他操作，如果是多版本 examination （察看）父游標(biāo)會(huì)發(fā)生父游標(biāo)的 cursor pin S 。

這里需要強(qiáng)調(diào)一下，它們只是替換了訪問(wèn) cursor 的 library cache pin ，而對(duì)于訪問(wèn) procedure 這種實(shí)體對(duì)象，依然是傳統(tǒng)的 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的原因主要有以下幾個(gè)方面：

ü   shared pool大小是否合適。

如果shared pool大小通常小于負(fù)載，則通常表現(xiàn)為Cursor: pin S wait on X.如果啟用了AMM，這通常不是一個(gè)問(wèn)題。

ü   頻繁的硬解析

如果硬解析頻率非常高，通常會(huì)發(fā)生這個(gè)等待事件以及伴隨cursor mutex X/cursor mutex S/latch: shared pool

ü   High Version Counts

當(dāng)由于某些原因（session參數(shù)，數(shù)據(jù)庫(kù)參數(shù)，直方圖等）導(dǎo)致SQL版本數(shù)量過(guò)高，每次執(zhí)行SQL時(shí)將要examined（查看）一個(gè)非常長(zhǎng)的子游標(biāo)鏈（handle list）將會(huì)導(dǎo)致硬解析成本很高以及軟解析成本也很高，導(dǎo)致其他非解析會(huì)話產(chǎn)生這個(gè)等待事件。

ü   已知的bug導(dǎo)致。

ü   解析失敗，AWR中解析失敗統(tǒng)計(jì)會(huì)很高。

可以通過(guò)查詢x$kglob或者，event 10035找到解析失敗語(yǔ)句。

Document 1353015.1 How to Identify Hard Parse Failures

2.等待事件診斷方法

2.1 收集AWR/ADDM報(bào)告

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

2.2 system state dump

如果awr以及addm、ash，沒(méi)有明顯有問(wèn)題sql，system state dump可以幫助捕獲阻塞會(huì)話以及定位潛在問(wèn)題。

(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獲得進(jìn)程信息，對(duì)已經(jīng)定位的阻塞者會(huì)話使用errorstack，幫助定位問(wèn)題。

$ 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 如何判斷哪個(gè)會(huì)話造成了cursor: pinS wait on X

v$session或v$session_wait的p2raw給出了造成cursor: pin S wait on X的會(huì)話，不同平臺(tái)不同bytes代表了sid，需要轉(zhuǎn)換成10進(jìn)制：

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找到阻塞會(huì)話以及執(zhí)行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.模擬測(cè)試

創(chuàng)建表：
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:

查詢?cè)斐蒫ursor: 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 機(jī)制，這一機(jī)制使得在某個(gè)父游標(biāo)下創(chuàng)建子
游標(biāo)的工作串行化。當(dāng)獲取Build Lock 時(shí)，需要持有Library Cache Lock，所以11.2版本更容易發(fā)生library cache lock。

3.2 各個(gè)等待事件發(fā)生原因

cursor: mutex S：當(dāng)一個(gè)會(huì)話examination（查看）檢索父游標(biāo)時(shí)，需要持有父游標(biāo)的library cache動(dòng)態(tài)創(chuàng)建的mutex的S共享模式，此時(shí)其他會(huì)話也看查看，就會(huì)造成cursor: mutex S

library cache lock: 當(dāng)硬解析時(shí)，需要獲得build lock，build lock是排他性的，使在父游標(biāo)下創(chuàng)建子游標(biāo)串行化，此時(shí)如果其他會(huì)話也來(lái)創(chuàng)建子游標(biāo)，則發(fā)生library cache lock等待build lock。

cursor pin S wait on X:當(dāng)一個(gè)會(huì)話要共享一個(gè)子游標(biāo)時(shí)，其他會(huì)話正在解析，則會(huì)話需要等待其他會(huì)話解析完成，然后共享cursor，此時(shí)就會(huì)發(fā)生cursor pin S wait on X。