Troubleshooting :Cursor :Pin S Wait Event

Recently we had performance issue with a batch job on a 3 Node RAC running on Linux.The job got hung from the database side, all we could see is session waiting on the Cursor pin S wait event. is not moving anywhere

When I tried to find out the BLOCKING_SESSION & BLOCKING_INSTANCE in the v$session. The column was blank.!!

Here is how the issue got fixed.

select sid,serial#,p1,p2,p3 from v$session where sid=178;
SID SERIAL# P1 P2 P3
---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- ----------
178 12856 3552538230 6.4425E+11 2.5761E+10

Get the P3 Value from the above query and then substitute in the below query.You will the get Location

select decode(trunc(2.5376E+10/4294967296),0,trunc(2.5376E+10/65536),trunc(2.5376E+10/4294967296)) LOCATION_ID from dual;

LOCATION_ID
----------------------
5

The below query may not be useful in debugging. But could help to find more about the wait event.

SELECT MUTEX_TYPE, LOCATION FROM x$mutex_sleep WHERE mutex_type like 'Cursor Pin%'
and location_id=&&LOCATION_ID;
Enter value for location_id: 5
old 4: and location_id=&&LOCATION_ID
new 4: and location_id=5
MUTEX_TYPE LOCATION
--------------------------------------------
Cursor Pin kkslce [KKSCHLPIN2]

select MUTEX_TYPE,LOCATION,REQUESTING_SESSION,BLOCKING_SESSION from V$MUTEX_SLEEP_HISTORY where MUTEX_IDENTIFIER=3552538230;

MUTEX_TYPE LOCATION REQUESTING_SESSION BLOCKING_SESSION
------------------------------------------------------------------------------------------------ ------------------ ----------------
Cursor Pin kkslce [KKSCHLPIN2] 178 150

Here P3 from the v$session is value for MUTEX_IDENTIFIER.

Lets see what session 150 is doing.

select SID,SERIAL#,STATUS,ACTION,STATE,EVENT from v$session where sid=150;

SID SERIAL# STATUS ACTION STATE EVENT
---------------- ----------------------------------------------------------------------------------------------------------
150 2905 KILLED JDBC Thin Client WAITED SHORT TIME library cache lock

Here we see that session is actually killed but still holding the lock.

select p.pid,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.SID = 150

SPID is the Operating system identifier (OS-PID)
SID is the Oracle session identifier
PID is the Oracle process identifier

Find the Oracle process and kill it at OS level.Once done.the Batch resumed smoothly.