Thursday, April 23, 2009

Mutex: Reloaded

Different Database but again mutex issue:

And again I must say I hate mutex! Need to find a blocking session...

SQL> select p2raw, count(*) from (select * from v$session where event = 'cursor: pin S wait on X') group by p2raw order by 2;
P2RAW COUNT(*)
---------------- ----------
000005D500000000 239

... 000005D5 in HEX -> 1493 in DEC ...
SQL> select sid,serial# from v$session where SID=1493;
SID SERIAL#
---------- ----------
1493 78
Now what? KILL IT!

SQL> alter system kill session '1493, 78'
alter system kill session '1493, 78'
*
ERROR at line 1:
ORA-00031: session marked for kill

Some time has passed and session still exists... Mutex problem persists of course too. So... kill -9 from OS layer...

Bingo!

We have hardcore workaround! Kill -9 ;-)

1 comments:

Kyle Hailey said...

cool - thanks for the EM graphic and kill -9 steps.
I linked it in
http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-cursor:-pin-S-wait-on-X-
I'll try to fill this page out more in the coming week.