解析cursor pin S等待事件中的p1、p2、p3值
造成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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/53956/viewspace-1985262/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 等待事件P1 P2 P3含義事件
- cursor: pin S 等待事件事件
- cursor: pin S wait on X等待事件。AI事件
- gc current request等待事件,介紹p1,p2,p3轉換方法GC事件
- oracle等待事件之cursor:pin S wait on XOracle事件AI
- cursor: pin S wait on X等待事件模擬AI事件
- About Enqueue:P1/P2/P3ENQ
- cursor: pin S wait on X等待事件模擬(轉)AI事件
- [20201117]解析cursor pin S等待事件.txt事件
- cursor: pin S wait on X等待事件的處理過程AI事件
- 【故障】cursor: pin S wait on X等待事件大量出現AI事件
- 【徵文】cursor: pin S wait on X等待事件的處理過程AI事件
- cursor: mutex S等待事件Mutex事件
- Cursor pin S wait on X 事件AI事件
- 'cursor:mutex ..'/ 'cursor:pin ..'/ 'library cache:mutex ..'型別的等待事件Mutex型別事件
- cursor: pin S wait on X等待實驗二AI
- v$session_wait中p1 p2 p3應用總結分析SessionAI
- SAP中MRP型別 P1,P2,P3,P4,PD的區別型別
- library cache lock和cursor: pin S wait on X等待AI
- 一次cursor: pin S wait on X事件的跟蹤AI事件
- Cursor Mutex S Waits等待事件引發hangMutexAI事件
- cursor pin S wait on XAI
- cursor: pin S wait on XAI
- cursor:pin S wait on XAI
- 遭遇cursor:pin x等待事件定位阻塞會話診斷過程事件會話
- 學習Oracle核心(cursor: pin S)Oracle
- 【等待事件】library cache pin事件
- 等待事件--library cache pin事件
- library cache pin 等待事件事件
- cursor: pin S wait on X模擬AI
- cursor: pin S模擬與處理
- library cache pin等待事件的模擬事件
- cursor:pin S wait on X故障診分析AI
- 等待事件Cursor: Pin S Wait On X和Library Cache Load Locks可能意味著過度的記憶體調整事件AI記憶體
- 分散式引起的cursor: pin S wait on X 事件一次問題處理分散式AI事件
- 解決library cache pin等待事件事件
- enq:Library cache lock/pin等待事件ENQ事件
- cursor: pin S產生原理及解決方法