Library cache pins. Use the value of v$session_wait.p1raw in the queries below.
Identify which object is being waited for:
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 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).
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'