Simple explanation of “cursor: pin S wait on X”

I think it is very  difficult if not impossible to  understand “cursor: pin S wait on X” without the knowledge of “cursor: pin S“.  So this blog will start with “cursor: pin S” along with basics of mutex.

Also see my blog about “cursor:pin s”  with more detailed info https://swervedba.wordpress.com/2011/03/30/cursor-pin-s/

Oracle introduced Mutexes in Oracle Version 10.2 and they stand for mutual exclusion (functionality is similar to mutexes in c).  Mutexes are used to protect data or other resources from concurrent access.

Working of Oracle Mutexes

Oracle uses counters  in implementing mutexes.  The counters are called as reference counters

1. Every time a particular mutex is obtained, Oracle will increment its value.

2. Every time a particular mutex is released, Oracle will decrement its value.

Cursor Pin S : A wait even that occurs when a session wants a specific mutex in shared mode on a specific cursor. As mentioned in the previous section,  Oracle has to update the ref counters  to get the mutex

However it is very important to understand that access to these counters are not concurrent. If there are concurrent sessions trying to obtain a mutex, then only one session can actually increment or decrement the reference counter at a time. Therefore concurrent sessions requesting the mutex must wait. This wait is called as “cursor: pin s” wait.

In short

“cursor: pin s” wait event occurs when Oracle wants a specific mutex in shared  mode on a specific cursor but could not obtain it as it was not able to update the reference counters (Not because another session was holding  the mutex in exclusive mode) .

“cursor: pin S wait on X” wait event occurs when Oracle is not able to get the mutex  in share(S)  mode  because some other session is  holding the  same mutex in  exclusive(X) mode.

Advertisements

Tagged: , , , , ,

7 thoughts on “Simple explanation of “cursor: pin S wait on X”

  1. Greg Cereghino November 20, 2011 at 8:59 am Reply

    Very interesting subject , regards for posting .

  2. Rachell Inzer November 30, 2011 at 9:48 pm Reply

    I was reading some of your articles on this site and I conceive this internet site is rattling instructive! Keep putting up.

  3. Onita December 12, 2011 at 4:16 pm Reply

    I am impressed with this website, rattling I am a big fan.

  4. Christopher Kossow December 14, 2011 at 6:54 am Reply

    Keep working ,impressive job!

  5. Debbra Manzur December 17, 2011 at 12:33 pm Reply

    Thankyou for this post, I am a big fan of this site would like to go on updated.

  6. Kenna Jergen December 17, 2011 at 5:24 pm Reply

    Hey there. Basically wanted to comment and mention that I admired this post. I’ll be bookmarking your blogging site and looking to find out if you post any additional ones. Thanks so much!

  7. Homepage August 8, 2012 at 4:29 am Reply

    … [Trackback]…

    […] Find More Informations here: swervedba.wordpress.com/2011/05/21/simple-explanation-of-cursor-pin-s-wait-on-x-2/ […]…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: