Inhaltsverzeichnis

Library cache Lock untersuchen

10/2010 zum erstenmal das Thema bearbeitet

Aus der Doku: 

wait: library cache lock
The library cache lock controls the concurrency between clients of the library cache by acquiring a lock on the object handle so that either:

One client can prevent other clients from accessing the same object
The client can maintain a dependency for a long time (no other client can change the object).
This lock is also obtained as part of the operation to locate an object in the library cache (a library cache child latch is obtained to scan a list of handles, then the lock is placed on the handle once the object has been found).

Demo Case

Code in Session 1 anlegen, in Session 2 aufrufen und in Session 1 erneut anlegen ⇒ Ein Library Cache Lock entsteht 1. Beispiel Code Endlos Schleife 

create or replace procedure endless
is
begin
 loop
  null;
 end loop;
end;
/

2. in zweiter Session aufrufen 

sqlplus>exec endless

3. Code erneut anlegen

4. Abfrage wer hat den Lock: 

/** Waiter ******************/
select ss.sid
     , ss.username
     , ss.terminal
     , ss.program
	 , sw.event
  from v$session ss , v$session_wait sw
 where ss.sid=sw.sid
   and sw.event like 'library cache %'

Session die die Procedure erneutern will ⇒ ABER Lock das zweite Session PL/SQL Objekt im Zugriff hat ⇒ Event 'library cache pin'

Session die blockiert: — in Arbeit —- über die x$kgllk

This table lists all held and requested library object locks for all sessions.
The column kglnaobj displays the first 80 characters of the name of the object.
kgllkreq = 0 means, the lock is held,
while kgllkreq > 0 means that the lock is requested. 

/**** Suchen über die v$lock und die X$KGLLK mit Username der blockierenden Session ***/
SELECT * FROM X$KGLLK where user_name = 'GPI' and KGLLKEXC > 0
/*** Beispiel ***/
select 
  kglnaobj, kgllkreq
from 
  x$kgllk x join v$session s on 
    s.saddr = x.kgllkses;

Quellen

Support Nodes:

Sehr gute Präsentation zu dem Thema: