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 
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。