Oracle "cursor: pin S wait on X" Wait Event

This post is a like a filler in between the Oracle Backup & Recovery Series

Recently while looking at a AWR report, Noticed the top wait event "cursor: pin S wait on X" which prompted me to write up on this wait event. As we already know there are mainly 3 buckets of wait event categories I/O Write, I/O Read & Others which comes under non-idle wait events.

This wait event "cursor: pin S wait on X" falls under others category, But luckily all the wait events under this others category normally compliments each other. So we might fall into a pitfall finding details of the session that is recording "cursor: pin S wait on X" waits. We should track down the actual session that is making this session wait for the cursor pinning.

From Oracle Documentation:

cursor:pin S
A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast.

Now, which is this another session? How to find that session? Follow me...I'll explain how to find.

1) Find the top blocking session from the ASH report (Note down the event)

2) Find the Event P1/P2/P3 values from the ASH report.

3) Find the mutex location using the P3 value "21474836480" as in below query.

SQL> select decode(trunc(21474836480/4294967296),0,trunc(21474836480/65536),

trunc(21474836480/4294967296)) LOCATION_ID from dual;

 
or

Get the details from the mutex sleeps  summary section in the AWR report.

 

4) Now finally catch the culprit session which was holding the mutex in exclusive mode and making the "cursor: pin S wait on X" the top wait event using the below query against the GV$MUTEX_SLEEP_HISTORY view.

SQL> select MUTEX_TYPE,LOCATION,REQUESTING_SESSION,BLOCKING_SESSION,SLEEP_TIMESTAMP from GV$MUTEX_SLEEP_HISTORY where location=’kksfbc [KKSCHLFSP2]’;

SQL> select MUTEX_TYPE,LOCATION,REQUESTING_SESSION,BLOCKING_SESSION,SLEEP_TIMESTAMP from GV$MUTEX_SLEEP_HISTORY where location=’kkslce [KKSCHLPIN2]’;

Hope this helps!

HAPPY LEARNING!