Library Cache load lock in Oracle

Session holding the Library Cache load lock in Oracle

Library Cache is the part of Memory component in SGA which used for storing SQL Statements, PLSQL and objects.
Library Cache lock occurred when one process is waiting for load an object/SQL , that object/SQL is loaded by other process at a time in exclusive mode. Cause of lock due to less shared pool size or having hard parsing in SQL queries not using bind variables.

Find the Session holding library cache lock

select sid Waiter, p1raw,
substr(rawtohex(p1),1,30) Handle,
substr(rawtohex(p2),1,30) Pin_addr
from v$session_wait where wait_time=0 and event like '%library cache%';

Find Session holding library cache at RAC Environment

select a.sid Waiter,b.SERIAL#,a.event,a.p1raw,
substr(rawtohex(a.p1),1,30) Handle,
substr(rawtohex(a.p2),1,30) Pin_addr
from v$session_wait a,v$session b where a.sid=b.sid
and a.wait_time=0 and a.event like 'library cache%';
or
set lines 152
col sid for a9999999999999
col name for a40
select a.sid,b.name,a.value,b.class
from gv$sesstat a , gv$statname b
where a.statistic#=b.statistic#
and name like '%library cache%';

Find the blocker session holding the library Cache
–Find the blocking session holding library cache lock

col sid format 9999
col username format a15
col terminal format a10
col program format a15
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 in (select saddr from v$session where sid in
(select sid from v$session_wait where event like 'library cache lock'))
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ > 0)
);

Find the blocked session waiting for library Cache lock

col sid format 9999
col username format a15
col terminal format a10
col program format a15
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 in (select saddr from v$session where sid in
(select sid from v$session_wait where event like 'library cache lock'))
AND LOCK_A.KGLLKHDL = LOCK_B.KGLLKHDL
AND KGLLKREQ = 0)
);

Check object lock by library cache

select to_char(SESSION_ID,'999') sid ,
substr(LOCK_TYPE,1,30) Type,
substr(lock_id1,1,23) Object_Name,
substr(mode_held,1,4) HELD, substr(mode_requested,1,4) REQ,
lock_id2 Lock_addr
from dba_lock_internal
where
mode_requested = 'None'
and session_id in
(select sid from v$session_wait where wait_time=0 and event like '%library cache%') ;