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/，如需轉載，請註明出處，否則將追究法律責任。