Library cache lock and library cache pin waits

posted Sep 10, 2010, 5:23 AM by Sachchida Ojha
Library cache lock and pin waits can hang instance, and in some cases, whole clusters of RAC instances can be hung due to library cache lock and pin waits. Library cache locks and pins are externalized in three x$ tables. x$kgllk is externalizing all locking structures on an object. Entries in x$kglob acts as a resource structure. x$kglpn is externalizing all library cache pins.

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';

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';
Comments