解析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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1985262/