【新炬網路名師大講堂】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  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