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。