RE: Identify blocking session for cursor: pin S wait on X when database is hung
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "sanjeevorcle@xxxxxxxxx" <sanjeevorcle@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 28 Feb 2011 13:51:47 -0500
Hopefully, the MOS document adequately covers how to track down blocking
session.
However, what is one to do when you cannot connect to the database?
Well, you might try the '-prelim' option to SQL*Plus.
You can read about how it works, here:
http://oradeblog.blogspot.com/2007/10/sqlplus-prelim-connection.html
-Mark
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Sanjeev M
Sent: Monday, February 28, 2011 1:34 PM
To: ORACLE-L
Subject: Identify blocking session for cursor: pin S wait on X when database is
hung
All,
Platform : Solaris(64-bit)
RDBMS Version: 11.1.0.7.2
Our production database experienced hang and we could not connect to sqlplus
and hang cleared on its own after few minutes. From AWR report for duration of
the hang Top 5 Timed Foreground Events were:
Event
Waits
Time(s)
Avg wait (ms)
% DB time
Wait Class
cursor: pin S wait on X
34,422,745
688,152
20
49.78
Concurrency
row cache lock
205,545
518,821
2524
37.53
Concurrency
library cache load lock
778
110,728
142324
8.01
Concurrency
DB CPU
27,809
2.01
rdbms ipc reply
16,003
22,098
1381
1.60
Other
Found this MOS document : HOW TO FIND BLOCKING SESSION FOR MUTEX WAIT EVENT
cursor: pin S wait on X [ID 786507.1] however when the issue occured could not
get connection to sqlplus
QUESTION: Is there a way to find out blocker session for this wait event after
the fact the issue got cleared and the sessions got completed?
Other research done and metalink notes found were:
Found this note: "Cursor: Pin S Wait On X" Contention Mutex Sleep Reason
Primarily ' kkslce [KKSCHLPIN2]' [ID 1268724.1]
Mutex Type
Location
Sleeps
Wait Time (ms)
Cursor Pin
kkslce [KKSCHLPIN2]
31,465,924
-3,361,856
Cursor Pin
kksfbc [KKSCHLFSP2]
3,607,296
2,750,834
Cursor Parent
kksfbc [KKSPRTLOC1]
406,049
1,120
Although we have above event the solution mentioned in this note is not
relevant in our environment.
I have found this bug: Bug 7234778 Unnecessary "cursor: pin S wait on X" wait
and I am going to follow up with oracle support on this with service request.
Regards,
Sanjeev.
Other related posts:
