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"
Who is pinning the object?
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
AND kglpnhdl='value of p1raw';