Re: cursor: pin S wait on X
- From: fairlie rego <fairlie_r@xxxxxxxxx>
- To: kylelf@xxxxxxxxx, Joel.Patterson@xxxxxxxxxxx
- Date: Thu, 11 Nov 2010 14:23:56 -0800 (PST)
I am not sure what the exact symptoms are but In 11.2 I have had limited
success
by setting _kgl_mutex_wait=1 the default value for this parameter is 0 (means
yielding) and the unit of this parameter is in centi secs (10 msec).
And even in 11.2 with the latest bundle patch there are still some outstanding
issues which are currently being fixed
Ta
Fairlie Rego
Senior Oracle Consultant
http://el-caro.blogspot.com/
M: +61 402 792 405
________________________________
From: kyle Hailey <kylelf@xxxxxxxxx>
To: Joel.Patterson@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Sent: Fri, 12 November, 2010 8:22:51 AM
Subject: Re: cursor: pin S wait on X
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-
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/
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:
