cursor: pin S wait on X

Few weeks back thread came up on oracle-l freelist regarding “cursor: pin S wait on X” event . Tanel Poder replied to this thread with excellent explanation. You can read full thread here

 

This wait happens when your session wants to examine / execute an existing
child cursor – but it’s being pinned in X mode by someone else. The usual
reasons for this are:
1) The cursor got flushed out for some reason (or it’s just the first time
anyone is running it) and the other session is currently hard
parsing/compiling/loading the contents of this cursor back into library
cache. The longer the parsing/loading takes, the longer you wait. If the
parsing session hits some (performance) issues, this pin may be held for a
long time.

or

2) The child cursor is already loaded into cache, but someone else is
currently flushing it out (shared pool shortage, fragmentation & loading of
a big object, SGA target / ASMM reducing the SGA size).

So as explained above, this could be seen as side affect of setting SGA_TARGET in environment. I have also observed this behavior in RAC system where shared pool (>10G) is under pressure and LCK0 process is trying to release memory which causes lot of processes to wait for this event. At this time database is completely hung. “cursor:pin S wait on X” might be reported for lot of sessions but we should see if shared pool free memory is less and try to fix it.

Amit Bansal

Experienced professional with 16 years of expertise in database technologies. In-depth knowledge of designing and implementation of Disaster Recovery / HA solutions, Database Migrations , performance tuning and creating technical solutions. Skills: Oracle,MySQL, PostgreSQL, Aurora, AWS, Redshift, Hadoop (Cloudera) , Elasticsearch, Python

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.