How to Find Blocking Session for Mutex Wait Event cursor: pin S wait on X

技术2022-05-19 24

SQL> select p2raw,to_number(substr(to_char(rawtohex(p2raw)),1,8),'xxxxxxxx') sid from v$session where event='cursor: pin S wait on X';

P2RAW SID

---------------- ----------

000000E800000000 232

P2RAW SID

---------------- ----------

000000E800000000 232

16 rows selected.

SQL> select sid,serial#,sql_id,action,blocking_session,blocking_session_status,event from v$session where sid=232;

SID SERIAL# SQL_ID ACTION

---------- ---------- ------------- --------------------------------

BLOCKING_SESSION BLOCKING_SE

---------------- -----------

EVENT

----------------------------------------------------------------

232 33179 cgn2901831cac

UNKNOWN

SQL*Net message from dblink

SQL> alter system kill session '232,33179';

alter system kill session '232,33179'

ERROR at line 1:

ORA-00031: session marked for kill

SQL> select p1,p2raw,count(*) from v$session where event='cursor: pin S wait on X' and wait_time=0 group by p1,p2raw;

P1 P2RAW COUNT(*)

---------- ---------------- ----------

1345368396 000000E800000000 16

SQL> select sid,serial#,sql_id,action,blocking_session,blocking_session_status,event from v$session where sid=232;

SID SERIAL# SQL_ID ACTION

---------- ---------- ------------- --------------------------------

BLOCKING_SESSION BLOCKING_SE

---------------- -----------

EVENT

----------------------------------------------------------------

232 33179 cgn2901831cac

UNKNOWN

SQL*Net message from dblink

SQL> select a.username,c.spid as ospid,c.pid as opid from v$session a,v$process c where c.addr=a.paddr and a.sid=232 and a.serial#=33179;

USERNAME OSPID OPID

------------------------------ ------------ ----------

DPSIID 18233 278

sczh-1#[/]kill -9 18233

专利

最新回复(0)