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/