Thursday Mar 01, 2012

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.

How to find PSU Version ?

Patch Set Updates are referenced by their 5-place version number. You may use the below commands depending on hte ORACLE_HOME.

Database PSU:

$opatch lsinventory -bugs_fixed | grep -i 'DATABASE PSU' 

CRS (Cluster Ready Services) PSU:

$opatch lsinventory -bugs_fixed | grep -i 'TRACKING BUG' | grep -i 'PSU'

GI (Grid Infrastructure) PSU:

$opatch lsinventory -bugs_fixed | grep -i 'GI PSU' 

Enterprise Manager Agent PSU:

$ opatch lsinventory -bugs_fixed | grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

Enterprise Manager OMS PSU:

$opatch lsinventory -bugs_fixed | grep -i 'ENTERPRISE MANAGER OMS' | grep -i 'PSU'

WebLogic Server PSU:

Enter the following commands, where WL_HOME is the path of the WebLogic home:

$ . $WL_HOME/server/bin/SetWLSEnv.sh  

$ java weblogic.version 

 

About

I am Jagatheesh Ramakrishnan - Apps DBA currently part of Advanced Customer Services for Oracle Corp.Am based out in London and working for a major bank customer.

Search

Archives
« March 2012 »
SunMonTueWedThuFriSat
    
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
       
Today