cursor: pin S wait on X模拟

cursor: pin S wait on X模拟

 

因为系统出现事件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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24996904/viewspace-772651/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24996904/viewspace-772651/