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

讓客戶滿意是我們工作的目標(biāo)，不斷超越客戶的期望值來自于我們對這個行業(yè)的熱愛。我們立志把好的技術(shù)通過有效、簡單的方式提供給客戶，將通過不懈努力成為客戶在信息化領(lǐng)域值得信任、有價值的長期合作伙伴，公司提供的服務(wù)項(xiàng)目有：空間域名、雅安服務(wù)器托管、營銷軟件、網(wǎng)站建設(shè)、雙牌網(wǎng)站維護(hù)、網(wǎng)站推廣。

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. 

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

cursor: pin S ， cursor: pin X ， cursor: pin S wait on X 這三個等待事件，實(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)會發(fā)生父游標(biāo)的 cursor pin S 。

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

ü  shared pool大小是否合適。

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

ü  頻繁的硬解析

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

ü  High Version Counts

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

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

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

可以通過查詢x$kglob或者，event 10035找到解析失敗語句。

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

$ sqlplus
SQL> oradebug setospid 
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，需要轉(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找到阻塞會話以及執(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.模擬測試

創(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:

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

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

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

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

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