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.
Recent Comments