how to resolve library cache pin in oracle

In this post, we will see what is library cache pin and how to resolve library cache pin in oracle

What is library cache Pin

• In the library cache, a database object is cached in 2 parts: “handle” and “object”. Library cache lock works on Handle and the Library cache pin is only held when the “object” part is cached.
• We have already discussed the Library cache lock. Now let’s check the Library cache pin
• This manages library cache concurrency. Pinning an object causes the heaps to be loaded into memory. If a client wants to modify or examine the object, the client must acquire a pin after the lock. The pin can be acquired in NULL, SHARE, or EXCLUSIVE modes
• A wait for a “library cache pin” implies some other session holds that PIN in an incompatible mode.
• A library cache pin will be obtained on a database object that is currently cached in the library cache (table, view, procedure, function, package, package body, trigger, index, cluster, synonym).

How Library cache pin and lock are related

• Library cache lock and Pin work together and work in NOWAIT mode
• By acquiring a lock on the object handle, a process can prevent other processes from accessing the object, or even finding out what type it is. It can maintain a dependency on an object without preventing other processes from accessing the object. Acquiring a lock is also the only way to locate an object in the cache. A process locates and locks an object in a single operation.
• If the process wants to actually examine or modify the object, then it must acquire a pin on the object itself (after acquiring a lock on the handle). Pinning the object causes information about the object and its heaps to be loaded into memory if they were not already there. This information is guaranteed to remain in memory at least until the pin is released.
• Oracle needs Library Cache Lock and Library Cache Pin for the compilation/parsing of Package/Procedure/Function/View. This is to ensure that no one is using the object during compilation/parsing while changes are being made to the definition of the object and the object is dropped and recreated the object with the new definition.

How to reduce the library cache pin

(b) Avoid compiling objects in different sessions at the same time or during busy times
(c)Do not collect optimizer statistics during busy periods
(d) Do not perform DDL operations during busy periods

how to resolve library cache pin in oracle

First, we can find the session waiting on the library cache pin

set linesize 132

column "USER" format a20
column "SID_SERIAL" format a15
column "EVENT" format a60 wrap

select
     b.username || '('|| b.osuser || ')'    "USER",
      a.sid || ','||  b.serial#             "SID_SERIAL",
      a.event || '=>'|| a.p1text || '=' || a.p1raw || ' ' ||
         a.p2text || '=' || a.p2 ||  ' ' ||
         a.p3text || '=' || a.p3 "EVENT"
      from v$session_wait a, v$session b
where a.sid = b.sid
and a.event like 'library cache pin%'
order by  3
;

Now we can find database-wide library cache pin blockers using the below query

select /*+ all_rows */ w1.sid waiting_session, 
h1.sid holding_session, 
w.kgllktype lock_or_pin, 
w.kgllkhdl address, 
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_held, 
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive', 
'Unknown') mode_requested 
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1 
where 
(((h.kgllkmod != 0) and (h.kgllkmod != 1) 
and ((h.kgllkreq = 0) or (h.kgllkreq = 1))) 
and 
(((w.kgllkmod = 0) or (w.kgllkmod= 1)) 
and ((w.kgllkreq != 0) and (w.kgllkreq != 1)))) 
and w.kgllktype = h.kgllktype 
and w.kgllkhdl = h.kgllkhdl 
and w.kgllkuse = w1.saddr 
and h.kgllkuse = h1.saddr 
/

or

select decode(lob.kglobtyp, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 
11, 'PACKAGE BODY', 12, 'TRIGGER', 
13, 'TYPE', 14, 'TYPE BODY', 
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 
32, 'INDEXTYPE', 33, 'OPERATOR', 
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 
42, 'MATERIALIZED VIEW', 
43, 'DIMENSION', 
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN', 
48, 'CONSUMER GROUP', 
51, 'SUBSCRIPTION', 52, 'LOCATION', 
55, 'XML SCHEMA', 56, 'JAVA DATA', 
57, 'SECURITY PROFILE', 59, 'RULE', 
62, 'EVALUATION CONTEXT', 
'UNDEFINED') object_type, 
lob.KGLNAOBJ object_name, 
pn.KGLPNMOD lock_mode_held, 
pn.KGLPNREQ lock_mode_requested, 
ses.sid, 
ses.serial#, 
ses.username 
FROM 
x$kglpn pn, 
v$session ses, 
x$kglob lob, 
v$session_wait vsw 
WHERE 
pn.KGLPNUSE = ses.saddr and 
pn.KGLPNHDL = lob.KGLHDADR 
and lob.kglhdadr = vsw.p1raw 
and vsw.event like 'library cache%' 
order by lock_mode_held desc 
/ 

Or you can get the blocking using the below method also

select a.p1text  "handle address"
      from v$session_wait a, v$session b
where a.sid = b.sid
and a.event like 'library cache pin%'
order by  3
;

Use the above handle address and query the below sql
col "User" format a8
col "Object" format a40
select b.sid "SID", b.username "User", c.kglnaobj "Object", a.KGLPNMOD "Mode"
from x$kglpn a, v$session b, x$kglob c
where a.KGLPNUSE = b.saddr
and a.kglpnhdl = c.kglhdadr
and a.kglpnhdl = '&handladdr';

An X request (3) will be blocked by any pins held S mode (2) on the object.
An S request (2) will be blocked by any X mode (3) pin held, or may queue behind some other X request.

The SQL given above is instance-specific, so please use this in all the instances in case of Oracle RAC

Related Articles
Oracle table locks : Oracle Enqueue,Row-level & DDL, table locks, how oracle locks work, Useful queries to find out the waiters and blockers in oracle
How to find table where statistics are locked: Check this post on How to find table where statistics are locked, how to lock oracle statistics, unlock oracle statistics,ORA-20005,ORA-38029
How to find the waitevent History of the Oracle session
oracle sql trace
Script to check locks in oracle database : Script to check locks in oracle database, unlock oracle table, find session holding oracle table locks, How to check lock on table in oracle
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm