Sanjeev,
Query DBA_HIST_ACTIVE_SESS_HISTORY where EVENT = 'cursor: pin S wait
on X' and use the information provided there. What might be most
useful will be the relationship between SESSION_ID/SESSION_SERIAL#
and BLOCK_SESSION_ID/BLOCK_SESSION_SERIAL#, as well as the P1, P2,
and P3 value associated with the wait event, as well as the SQL_ID.
Have fun!
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal => 13087 West Cedar Drive #225, Lakewood CO 80228
website => http://www.EvDBT.com/
email => Tim@xxxxxxxxx
mobile => +1-303-885-4526
fax => +1-303-484-3608
Lost Data? => http://www.ora600.be/ for info about DUDE...
On 2/28/2011 11:34 AM, Sanjeev M wrote:
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.
--
//www.freelists.org/webpage/oracle-l
|
