CNDBA社区
1.创建测试环境
session A:
SQL> select sid from v$mystat where rownum<2;
SID
---------
159
SQL> select spid from v$process where addr in(select paddr from v$session where sid in (select sid from v$mystat where rownum<2));
SPID
------------
5314
创建表
create table mac_kks tablespace users nologging as select * from dba_objects;
insert /*+ append */ into mac_kks select * from mac_kks;
commit;
insert /*+ append */ into mac_kks select * from mac_kks;
commit;
insert /*+ append */ into mac_kks select * from mac_kks;
commit;
insert /*+ append */ into mac_kks select * from mac_kks;
commit;
insert /*+ append */ into mac_kks select * from mac_kks;
commit;
修改表定义
SQL> alter table mac_kks add t2 char(2000) default 'MACLEAN';
session B:
HANG住session A
SQL> oradebug setospid 5314
Oracle pid: 13, Unix process pid: 5314, image: oracle@single (TNS V1-V3)
SQL> oradebug suspend;
Statement processed.
session C:
被HANG住
SQL> select sid from v$mystat where rownum<2;
SID
--------
145
select * from mac_kks where rownum=1;
session D:
被HANG住
SQL> select sid from v$mystat where rownum<2;
SID
----------
141
select * from mac_kks where rownum=1;
session E:
查看等待事件
SQL> select sid,event from v$session where wait_class!='Idle';
SID EVENT
---------- ----------------------------------------------------------------
141 cursor: pin S wait on X
145 library cache lock
159 db file sequential read
生成dump文件
SQL> oradebug setmypid
Statement processed.
SQL> oradebug dump systemstate 266;
Statement processed.
SQL> oradebug tracefile_name
/oracle/app/product/admin/single5/udump/single5_ora_5664.trc
set lines 180 pages 1000
col event for a30
col p3 for 999999999999999999
select SID,ROW_WAIT_OBJ#,EVENT,P1RAW,P2,P2RAW,P3RAW,BLOCKING_SESSION from v$session where wait_class!='Idle';
SID ROW_WAIT_OBJ# EVENT P1RAW P2 P2RAW P3RAW BLOCKING_SESSION
---------- ------------- ------------------------------ ---------------- ------------------- ---------------- ---------------- ----------------
141 -1 cursor: pin S wait on X 000000007ABADECF 622770257920 0000009100000000 0000000500031F6A
145 -1 library cache lock 000000007E77E5E0 2143171576 000000007FBE33F8 00000000000000C9 159
159 58623 db file sequential read 0000000000000002 3 0000000000000003 0000000000000001
col name for a30
col PARAMETER1 for a15
col PARAMETER2 for a15
col PARAMETER3 for a20
select NAME,PARAMETER1,PARAMETER2,PARAMETER3 from v$event_name where name in ('cursor: pin S wait on X','library cache lock');
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ --------------- --------------- --------------------
cursor: pin S wait on X idn value where|sleeps
library cache lock handle address lock address 100*mode+namespace
2.分析原理
方法一：
从上面的查询语句很直观的可以看出
SID为145的library cache lock等待事件到等待SID为159的SESSION
怎么查看SID为141的cursor: pin S wait on X在等待谁
如果我们只能查到P2字段，历史视图v$active_session_history没有P2RAW字段
p2为622770257920，转化为P2RAW
select to_char(622770257920,'xxxxxxxxxxxxxxxx') from dual;
TO_CHAR(622770257
-----------------
9100000000
P2RAW找到阻塞源
select to_number(substr('0000009100000000',1,8),'xxxxxxxx') from dual;
TO_NUMBER(SUBSTR('0000009100000000',1,8),'XXXXXXXX')
----------------------------------------------------
145
阻塞源是SID为145的SESSION
方法二：
more /oracle/app/product/admin/single5/udump/single5_ora_5664.trc
搜索 waiting for 'cursor: pin S wait on X'
结果为
waiting for 'cursor: pin S wait on X' wait_time=0, seconds since wait started=0
idn=7abadecf, value=9100000000, where|sleeps=5000066c2
blocking sess=0x(nil) seq=26320
得到value=9100000000
转化得阻塞源是SID为145的SESSION
BUG：8666117，也会引起cursor: pin S wait on X大量出现
版权声明：本文为博主原创文章，未经博主允许不得转载。
cursor: pin S wait on X 等待事件分析
- 上一篇：pg用户权限查询
