How to resolve library cache lock in oracle

This post is about library cache lock and How to resolve library cache lock in oracle

What is library cache lock

This is used to control concurrency between clients of the library cache by acquiring a lock on the object handle. Uses are
(a)One client can prevent other clients from accessing the same object
(b)The client can maintain a dependency for a long time (no other client can change the object).

Reasons for the Library lock contention

(a) Unshared SQL Due to Literals
(b) Improper shared pool size
(c) Improper Cursor_sharing parameter
(d) Library cache object Invalidations due to DDL on the objects
(e) Gathering optimizer statistics during busy periods
(f) Compilation of objects during a busy period
(g) Extensive use of row-level trigger

How to reduce the library cache lock

(a) Rewrite SQL to use Bind Variables
(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
(e) Increase the size of the shared pool
(f) Appropriate use of parameter CURSOR_SHARING

How to resolve library cache lock in oracle

First, we can identify all the sessions which are waiting on the Library cache lock using the below sql

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 lock%'
order by  3
;

Now you can use the below query to identify the blocking session

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 you can below also to find the blocking session

select sid,saddr from v$session where event= ‘library cache lock’;

The saddr obtained from above SQL needs to be used below to find out the blocking session

SELECT SID,
USERNAME,
TERMINAL,
PROGRAM
FROM V$SESSION
WHERE SADDR IN
(SELECT KGLLKSES
FROM X$KGLLK LOCK_A
WHERE KGLLKREQ = 0
AND EXISTS
(SELECT LOCK_B.KGLLKHDL
FROM X$KGLLK LOCK_B
WHERE KGLLKSES = ‘&saddr’
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0));

Once you find the culprit session, we can kill the session using the below kill command

select 'alter system kill session ' ||''''|| s.sid||','|| s.serial#||''''||';'
from v$session s where sid='&1';

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
how to resolve library cache pin in oracle :check out how to resolve library cache pin in oracle, what are the reason and how we can avoid in Oracle database
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9015.htm