Library Cache Locks

Few dayes back my raguler jobs are hanges. After analyzing I find the hange query is wating for Library cache lock .Then i found the sesstion which is causing issue using below quries and kill the causing session and my hanges quiers are run . Library Cache contention is a serious issue In most cases it would be good to analyze what is holding the library cache lock and killing it will resolve the issue.

Detect sessions waiting for a Library Cache Locks

SELECT SID AS "Waiter session", p1raw P1, p2raw P2 , p3raw P3,

SUBSTR(RAWTOHEX(p1),1,50) Handle,

SUBSTR(RAWTOHEX(p2),1,50) Pin_addr

FROM v$session_wait WHERE wait_time=0 AND event LIKE '%library cache%';

Detect Library Cache holders that sessions are waiting for

SELECT SESSION_ID SID,

LOCK_TYPE,

SUBSTR(lock_id1,1,50) 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 mode_requested<>mode_held

AND session_id IN ( SELECT SID

FROM v$session_wait WHERE wait_time=0

AND event LIKE '%library cache%') ;

Objects locked by Library Cache based on sessions detected above

SELECT SID Holder ,KGLPNUSE Sesion , KGLPNMOD Held, KGLPNREQ Req

FROM x$kglpn , v$session

WHERE KGLPNHDL IN (SELECT p1raw FROM v$session_wait

WHERE wait_time=0 AND event LIKE '%library cache%')

AND KGLPNMOD <> 0

AND v$session.saddr=x$kglpn.kglpnuse ;

What are THE holders waiting FOR?

SELECT SID,SUBSTR(event,1,30),wait_time

FROM v$session_wait

WHERE SID IN (SELECT SID FROM x$kglpn , v$session

WHERE KGLPNHDL IN (SELECT p1raw FROM v$session_wait

WHERE wait_time=0 AND event LIKE 'library cache%')

AND KGLPNMOD <> 0

AND v$session.saddr=x$kglpn.kglpnuse );