Diagnosing Library Cache Pins waits in v$session_wait

Home

Toolkit:

My OraFAQ Blog

Contact me

Library cache pins. Use the value of v$session_wait.p1raw in the queries below.

Identify which object is being waited for: 


 SELECT kglnaown "Owner", kglnaobj "Object" 
     FROM x$kglob WHERE kglhdadr='value of p1raw'
Who is pinning the object? 
  SELECT s.sid, s.serial#, s.username, s.osuser, s.machine, s.status, 
kglpnmod "Mode", kglpnreq "Req"
    FROM x$kglpn p, v$session s WHERE p.kglpnuse=s.saddr AND 
kglpnhdl='value of p1raw'
"Mode"=mode pin is held in, "Request"=mode of request.
The "Mode" and "Request" can be either exclusive (3) or shared (2).

On systems where a webserver repeatedly reissues SQL after a timeout, you can see a large buildup of webserver sessions all trying to pin the same blocked library cache pin. Use the following script to generate SQL to kill off multiple sessions all waiting for the same library cache pin. 

  SELECT 'alter system kill session ''' || s.sid || ','  || s.serial# || ''';'
  FROM x$kglpn p, v$session s
  WHERE p.kglpnuse=s.saddr
  AND kglpnhdl='value of p1raw'

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...