RE: Massive Wait Events "Cursor: pin S wait on X" with PL/SQL in 11.1.0.7
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>, "usn@xxxxxxxxx" <usn@xxxxxxxxx>, "ukja.dion@xxxxxxxxx" <ukja.dion@xxxxxxxxx>
- Date: Fri, 7 May 2010 05:07:43 -0400
Forgot to mention, see here for more insight:
http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-cursor:-pin-S-wait-on-X-
Thanks to Connie Green and Kyle Hailey for that info.
-Mark
________________________________________
From: Bobak, Mark
Sent: Friday, May 07, 2010 5:05
To: usn@xxxxxxxxx; ukja.dion@xxxxxxxxx
Cc: Martin.Klier@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Massive Wait Events "Cursor: pin S wait on X" with PL/SQL in
11.1.0.7
Hi Martin,
Try this to see if you can track down the blocking session:
select p2/power(2,32) blocking_sid, p1 mutex_id, sid blocked_sid
from v$session
where event like 'cursor:%'
and wait_time=0
/
Hope that helps,
-Mark
PS The above works for me on a 64-bit Linux system. If you're running a
32-bit database, I *believe* the P1, P2, P3 columns would be RAW(4) rather than
RAW(8), and you'd have to divide by power(2,16) instead of power(2,32). I
don't have a 32-bit database to test/prove that, though.
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] On Behalf
Of Martin Klier [usn@xxxxxxxxx]
Sent: Friday, May 07, 2010 4:04
To: ukja.dion@xxxxxxxxx
Cc: Martin.Klier@xxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Massive Wait Events "Cursor: pin S wait on X" with PL/SQL in
11.1.0.7
Hi,
Dion Cho schrieb:
> Querying V$MUTEX_SLEEP_HISTORY view would be another starting point -
> who is blocking mutex to execute which codes?
thanks for the reply. But to be honest, the LOCATION field does tell me
nothing - looks like internal function names. How does the information
help me to find out the culprit?
Thanks in advance
Martin
--
Usn's IT Blog for Linux, Oracle, Asterisk
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
