RE: Identify blocking session for cursor: pin S wait on X when database is hung

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 28 2011 - 12:51:47 CST