造成cursor: pin S等待事件最常見的觸發原因是兩個Session執行同一段程式碼

---製造資料：
create table ad.t1 tablespace ts_pub as select * from all_users;

---session 1: sid=4727，執行如下procedure
select * from v$mystat where rownum=1;
declare
v_uid number;
v_sql varchar2(1000);
v_cur number;
ret_exec number;
ret_rows number;
begin
v_cur:=dbms_sql.open_cursor;
v_sql:='select user_id from ad.t1 where username=''SYS''';
while ( true ) loop
dbms_sql.parse(v_cur,v_sql,dbms_sql.native);
dbms_sql.define_column(v_cur,1,v_uid);
ret_exec:=dbms_sql.execute(v_cur);
end loop;
dbms_sql.close_cursor(v_cur);
end;
/

---session 2: sid=2843，執行session 1相同的procedure
。。。見session 1

---session 3: 觀察session 1、session 2的爭用情況，
col event format a20
col p1text format a10
col p2text format a10
col p3text format a10
set linesize 180 numwidth 16

***下列語句執行多次，以觀察不同的結果集
select event,sid,status,p1,p2,p2raw,decode(trunc(p2/4294967296),0,trunc(p2/65536),trunc(p2/4294967296)) blocking_sid,p3,p3raw,decode(trunc(p3/4294967296),0,trunc(p3/65536),trunc(p3/4294967296)) loc_id from v$session where sid in (2843,4727);

--->第一組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 1 0000000000000001 0 38654705664 0000000900000000 9
cursor: pin S 4727 ACTIVE 3846933954 12210592022530 00000B1B00000002 2843 38654705664 0000000900000000 9

--->第二組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 1 0000000000000001 0 17179869184 0000000400000000 4
cursor: pin S 4727 ACTIVE 3846933954 12210592022529 00000B1B00000001 2843 17179869184 0000000400000000 4

--->第三組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 20302310408193 0000127700000001 4727 38654705664 0000000900000000 9
cursor: pin S 4727 ACTIVE 3846933954 12210592022529 00000B1B00000001 2843 38654705664 0000000900000000 9

--->第四組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 2 0000000000000002 0 38654705664 0000000900000000 9
cursor: pin S 4727 ACTIVE 3846933954 1 0000000000000001 0 17179869184 0000000400000000 4

--->第五組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 0 00 0 17179869184 0000000400000000 4
cursor: pin S 4727 ACTIVE 3846933954 1 0000000000000001 0 38654705664 0000000900000000 9

--->第六組輸出
EVENT SID STATUS P1 P2 P2RAW BLOCKING_SID P3 P3RAW LOC_ID
-------------------- ---------------- -------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ----------------
cursor: pin S 2843 ACTIVE 3846933954 20302310408193 0000127700000001 4727 38654705664 0000000900000000 9
cursor: pin S 4727 ACTIVE 3846933954 0 00 0 17179869184 0000000400000000 4

P1、P2、P3值含義：
>>P1：代表sql_hash_value，可以看出在哪條SQL語句上出現了爭用
col sql_text format a60
set linesize 180
select sql_text,hash_value from v$sql where hash_value='3846933954';
SQL_TEXT HASH_VALUE
------------------------------------------------------------ ----------------
select user_id from ad.t1 where username='SYS' 3846933954

>>P2：透過P2RAW比較容易區分出來：高4位元組為mutex當前持有者的sid；低4位元組為當前申請該mutex的和當前持有該mutex的session數量；如果要透過P2值來區分，方法如下：
select decode(trunc(p2/4294967296),0,trunc(p2/65536),trunc(p2/4294967296)) blocking_sid from v$session where sid in (2843,4727); <---mutex持有者的sid，如果系統是32bit的那麼p2/65536
BLOCKING_SID
------------
4727
0

select decode(bitand(p2,4294967295),p2,bitand(p2,65535),bitand(p2,4294967295)) mutex_refer_cnt from v$session where sid in (2843,4727); <---mutex持有+申請的session總數，如果系統是32bit的，那麼bitand(p2,65535)
MUTEX_REFER_CNT
---------------
2
1

>>P3：透過P3RAW比較容易區分出來：高4位元組為mutex的location_id，低4位元組在11g裡總是為0，如果要透過P3值來區分，方法如下：
select decode(trunc(p3/4294967296),0,trunc(p3/65536),trunc(p3/4294967296)) loc_id from v$session where sid in (2843,4727);
LOC_ID
----------------
9
4

col mutex_type format a15
col location format a35
set linesize 180 numwidth 5
select * from x$mutex_sleep where location_id in (4,9) and MUTEX_TYPE like 'Cursor Pin'; <---mutex_type一定要加在條件裡，因為不通的mutex_type會具有相同的location_id

ADDR INDX INST_ID MUTEX_TYPE MUTEX_TYPE_ID LOCATION_ID LOCATION SLEEPS WAIT_TIME
---------------- ----- ------- --------------- ------------- ----------- ----------------------------------- ------ ---------
0000000110AB3338 24 1 Cursor Pin 7 9 kksLockDelete [KKSCHLPIN6] 4866 0
0000000110AB3438 26 1 Cursor Pin 7 4 kksfbc [KKSCHLPIN1] 8072 0

透過以上六組輸出可以看出，P2、P3兩個列值經常會變化。
P2是記錄mutex持有者sid和mutex reference count的，P2的變化表明兩個Session執行同一段程式碼的時候會隨機的相互阻塞，並不一定是先發起的session阻塞後發起的，且它們各自記錄的mutex reference count也是各不相同的；
P3記錄的則是mutex申請者當前阻塞在哪個環節，類似"kksLockDelete [KKSCHLPIN6]"、"kksfbc [KKSCHLPIN1]"可以用來提交至oracle support定位到kernel層的程式碼，確定爭用出現在parent cursor、child cursor、hash bucket等到底哪個記憶體結構上。由於mutex的申請和釋放是在後臺高速執行的，所以查詢結果也會實時變化

以上測試雖然模擬的是cursor: pin S等待事件，但其中提及的分析方法與結論同樣適用於下面四種Mutex等待：
cursor: mutex X
cursor: mutex S
cursor: pin X
cursor: pin S wait on X

解析cursor pin S等待事件中的p1、p2、p3值

相關文章