Find Blocking Session Causing 'library cache pin' and 'library cache lock' Wait Events

Problem:

A session, usually but not always trying to compile a PL/SQL package, waits for 'library cache pin' for a long time. This may be caused by another session holding a library cache pin. Since this is not an enqueue lock, views such as v$lock or v$locked_object won't have a record to help find the blocking session. Sometimes the wait is 'library cache lock' if the waiting session can't even locate some library cache metadata.

Solution:

Pass p1raw of v$session to dba_kgllock.

Given the session waiting for 'library cache pin', find the value of p1raw in v$session for this session. This is the library cache object handle address. Suppose it's 000000009E0FA598. Use that value to find the sessions involved in this 'library cache pin' holding and waiting situation.

SQL> select * from dba_kgllock where kgllkhdl='000000009E0FA598';

KGLLKUSE         KGLLKHDL           KGLLKMOD   KGLLKREQ KGLL
---------------- ---------------- ---------- ---------- ----
00000001014C93B8 000000009E0FA598          1          0 Lock
0000000101947CF0 000000009E0FA598          3          0 Lock
0000000101947CF0 000000009E0FA598          0          3 Pin  <-- pin waiter
00000001014C93B8 000000009E0FA598          2          0 Pin  <-- pin holder

The above shows that the session with address (kgllkuse) 00000001014C93B8 holds this pin in mode (kgllkmod) 2 and is not requesting for it (request mode, kgllkreq, being 0). Session 0000000101947CF0 is not holding this pin (mode 0) and is requesting for a pin in mode 3. You can find the session IDs in v$session:

SQL> select sid, saddr from v$session where saddr in ('0000000101947CF0','00000001014C93B8');

       SID SADDR
---------- ----------------
       222 00000001014C93B8
      1178 0000000101947CF0

Whether to kill the blocking session, SID 222 here, is a business decision that should be made by checking other columns of v$session and maybe notifying the app team.

Sometimes both pins and locks are involved. Use the following queries for your convenience (the example below assumes p1raw of the session waiting on the event to be 000000008FFCDDD0):

SQL> select a.sid, a.saddr, a.event, a.p1raw, b.kgllkmod, b.kgllkreq from v$session a, dba_kgllock b where kgllkhdl='000000008FFCDDD0' and a.saddr=b.kgllkuse and b.kgllktype='Pin' order by 1,2,3;

       SID SADDR            EVENT                               P1RAW              KGLLKMOD   KGLLKREQ
---------- ---------------- ----------------------------------- ---------------- ---------- ----------
       413 000000010571A050 PL/SQL lock timer                   00                        2          0
      1179 0000000105AB1088 library cache pin                   000000008FFCDDD0          0          3

The above shows session 413 blocks session 1179 causing the latter to wait on library cache pin.

SQL> select a.sid, a.saddr, a.event, a.p1raw, b.kgllkmod, b.kgllkreq from v$session a, dba_kgllock b where kgllkhdl='000000008FFCDDD0' and a.saddr=b.kgllkuse and b.kgllktype='Lock' order by 1,2,3;

       SID SADDR            EVENT                               P1RAW              KGLLKMOD   KGLLKREQ
---------- ---------------- ----------------------------------- ---------------- ---------- ----------
        18 00000001013F28E0 library cache lock                  000000008FFCDDD0          0          3
       413 000000010571A050 PL/SQL lock timer                   00                        1          0
      1179 0000000105AB1088 library cache pin                   000000008FFCDDD0          3          0

The above shows sessions 413 and 1179 block session 18 causing the latter to wait on library cache lock.


RAC database:

View dba_kgllock is local to the instance you're in in spite of the name dba_* (I wish it was called v$kgllock, and a gv$ view was built on it). In a RAC database, make sure to run the above queries in the instance where the blocked session is. If both the blocking and blocked sessions are in the same instance, the above queries still work. If they're in different instances, the queries won't find the blocker. In that case, find the blocker by way of the object being contended for. The upper 8 bytes of p3raw of the blocked session is the object_id in hex. Suppose p3raw is 000449A100010003, the object_id is 000449A1 or 280993 in decimal. Find the object name in dba_objects. Go to the other instance (or each of the other instances) and run this query as sys:

select * from x$kgllk where kglnaobj='<the object name>';

which shows the blocking session ID under kgllkses or kgllkuse. To find the entry in x$kglpn, go by handle, whose address is kglpnhdl in x$kglpn, kgllkhdl in x$kgllk, kglhdadr in x$kglob.

2020-03