Re: cursor: pin S wait on X
- From: kyle Hailey <kylelf@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx
- Date: Thu, 11 Nov 2010 13:22:51 -0800
There are a few bugs and if you hit one where the mutex isn't released then
it's nice to know how to kill the holder, if need be:
http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-cursor:-pin-S-wait-on-X-
<http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-library-cache#TOC-cursor:-pin-S-wait-on-X->I
think it was Connie Green that showed me this:
• If a session is waiting on the wait event ‘cursor: pin S wait on X’, the
session is most likely trying to execute a cursor (pin S), and must wait as
another session (who is most likely parsing the cursor) has it pinned X
(wait on X)
• v$session.p1 can be used to compare with
v$mutex_sleep_history.mutex_identifier
• Example v$session data (64 bit platform), from an instance where the mutex
holder was hung due to a bug not related to mutexes, causing requestors to
back up behind the holder:
select p1, p2raw, count(*)
from v$session
where event = ‘cursor: pin S wait on X’
and wait_time = 0
group by p1, p2;
P1 P2RAW COUNT(*)
---------- ---------------- --------
2700259466 0000139700000000 9
<Mutex Id> < SId><RefCnt>
As you can see, 9 sessions were waiting for Session Id 0x1397, which was
holding exclusive the Mutex with the Id 2700259466. The Ref Count is zero,
as we would expect. The 9 sessions are waiting to execute the cursor
protected by mutex Id 2700259466.
To find the blocking session, use the top bytes of v$session.p2raw e.g. the
top bytes of p2raw is the blocker 0x00001397 which when converted to
decimal, is session Id 5015.
-Kyle
http://db-optimizer.blogspot.com/
<http://db-optimizer.blogspot.com/>
On Tue, Nov 9, 2010 at 11:26 AM, <Joel.Patterson@xxxxxxxxxxx> wrote:
> This question is somewhat associated with my lsuosstats global area latch
> question recently posted.
>
> We were running a load test and so many of the queries where probably doing
> the exact same thing. So the event “cursor: pin S wait on X” which bubbled
> to the top.
> From Tanel Poders web site: “A mutex differs from a latch in that there
> would be many more requestors backed up on a latch, whereas a mutex in this
> case protects just one cursor.”
> Since this was a test, I am assuming that this is nothing to be concerned
> about now, but if I were to become concerned about it, does anyone know how
> they could approach this event specifically? Is there anything we can do
> about it now – because we are testing?
>
>
> Joel Patterson
> Database Administrator
> 904 727-2546
>
>
>
Other related posts:
