• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Techgoeasy

Techgoeasy

Learn Oracle, PHP, HTML,CSS,Perl,UNIX shell scripts

  • Home
  • Oracle
    • Oracle database
    • Oracle Ebusiness Suite
    • Oracle weblogic
    • Oracle Performance Tuning
    • Oracle Hyperion
    • Oracle Cloud Tutorials
  • SQL
  • interview questions
  • Linux
  • PHP and HTML
  • Downloads
  • General
Home » Oracle » Oracle Database » How to resolve library cache lock in oracle

How to resolve library cache lock in oracle

May 3, 2022 by techgoeasy Leave a Comment

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

Table of Contents

  • What is library cache lock
  • Reasons for the Library lock contention
  • How to reduce the library cache lock
  • 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

Filed Under: Oracle, Oracle Database

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar



Subscribe to our mailing list

Enter your email address to subscribe to this blog and receive notifications of new posts by email

Recent Posts

  • How to generate sql monitor report in Oracle
  • How to find the long running (longops session) in Oracle
  • How to find concurrent request id from Sid or spid
  • how to check recycle bin in oracle: Purge, enable/disable
  • how to check fra size in oracle: Location, usage, change size

Copyright © 2023 : TechGoEasy

  • Hire me
  • Privacy Policy
  • Contact Us
  • New? Start Here
  • About Us