因为系统出现事件cursor: pin S wait on X过去几天，在v$session_wait找不到对应的记录，现需要找到引起问题的sid，从其他途径获得了对应的p2，故验证p2与p2raw的转换

现模拟出事件cursor: pin S wait on X，然后通过p2转换出p2raw是否正确。

–==============

session 1:

–============================

SQL> select sid from v$mystat where rownum=1;

SID

———-

1147

–创建测试表 SQL> create table t tablespace users as select * from dba_objects;

表已创建。

SQL> declare

v_string varchar2(100) := ‘alter system flush shared_pool’;

msql varchar2(200);

begin

loop

execute immediate v_string;

for i in 1 .. 100 loop

msql:=’select object_id from t where object_id=’||i;

execute immediate msql;

end loop;

end loop;

end;

/

–==============================

session 2:

–==============================

SQL>  select sid from v$mystat where rownum=1;

SID

———-

10

SQL> declare

v_string varchar2(100) := ‘alter system flush shared_pool’;

msql varchar2(200);

begin

loop

execute immediate v_string;

for i in 1 .. 100 loop

msql:=’select object_id from t where object_id=’||i;

execute immediate msql;

end loop;

end loop;

end;

/

–================================

session 3:(监控)

–==============================

SQL> select b.*, sq.sql_text  from v$session se , v$sql sq ,  (select a.*,s.sql_text from v$sql s ,  (select sid,event,wait_class,p1,p2,p2raw from v$session_wait where event like ‘cursor%’) a  where s.HASH_VALUE=a.p1) b  where se.sid=b.sid and se.sql_hash_value=sq.hash_value;

SID EVENT                     WAIT_CLASS              P1         P2 P2RAW ———- ————————- ————— ———- ———- —————- SQL_TEXT ——————————————————————————————- SQL_TEXT ——————————————————————————————-

1147 cursor: pin S wait on X   Concurrency     3363839347     655360 00000000000A0000 select object_id from t where object_id=72 select object_id from t where object_id=72

p2:655360

p2raw:A0000

SQL> select to_char(655360,’xxxxxxxxxxxxxxx’) from dual;

TO_CHAR(655360,’

—————-

a0000

可见转换正确。

查找对应的sid：

SQL> select to_number(substr(’00000000000A0000′,1,12),’xxxxxxxxxxxxxxxxxx’) sid from dual;

SID

———-        

10

正好对应session2