Re: Identify blocking session for cursor: pin S wait on X when database is hung
- From: Pavel <ocp.pauler@xxxxxxxxx>
- To: Mark.Bobak@xxxxxxxxxxxx, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Mar 2011 15:49:19 +0300
Hi
Didn't hear about _prelim before. Thanks Mark!
Best regards,
Pavel.
2011/2/28 Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>
> 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:
