Performance Tuning
Wait event : library cache pin and library cache locks
User Rating: 
/ 1
- Details
-
Parent Category: Articles
-
Created on Thursday, 18 March 2010 21:36
-
Last Updated on Monday, 12 March 2012 13:54
-
Published on Thursday, 18 March 2010 21:36
-
Written by Guy Lambregts
-
Hits: 11093
Wait event : library cache pin and library cache locks
ARTICLE YET TO BE COMPLETED
Library cache pin
A session tries to modify, (re)compile PL/SQL CODE while another session is executing that piece of code, read while another session requires that object to be pinned in the library cache in exclusive mode. When multiple session recompiles the same code library cache locks comes into play.
The library cache is also known as the kgl layer, in the below article I use the below fixed X$ tables
x$kglob : kgl objects
x$kglpn : kgl pins, for library cache pin diagnosis
x$kgllk : kgl locks, for library lock diagnosis
Note that from 10G R1 onwards the dynamic performance view v$session contains the fields event, p1,p1raw,p2,p2raw , p3 and p3raw. For older releases v$session_wait should be used. ( since this information wat not available yet at that time in v$session )
Note that the below fields will be of special interest for us
in x$kgllk we have the field : kgllkmod lock held (0: no lock; 1: null; 2: shared; 3: exclusive)
in x$kglpn we have the field : kglpnmod pin held (0: no lock; 1: null; 2: shared; 3: exclusive)
The below test is preferably done in a test database. ( never play nor test with production databases )
ADMIN SESSION (user SYS)
We grant execute privileges on dbms_lock to user PM
SQL> connect / as sysdba
Connected.
SQL> grant execute on dbms_lock to pm;
Grant succeeded.
SQL> exit
USER SESSION 1: (USER PM)
creates a procedure and executes it
SQL> create or replace procedure PRC_I_WILL_SLEEP(nr_sec number,el varchar2,rate number)
IS
BEGIN
insert into t_exchangerate_er values (seq_er.nextval,el,rate,systimestamp);
dbms_lock.sleep(nr_sec);
commit;
END;
/
Procedure created.
SQL> set timing on
SQL> begin
2 PRC_I_WILL_SLEEP(10,'Au',1091);
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:10.01
When session 1 executes the procedures we note that the object is pinned into the library cache but without pin requests, since at this time no other session tries to alter/compile.
SQL> select sid from v$mystat where rownum=1;
SID
143
SQL> begin
2 prc_i_will_sleep(1000,'Au',1993);
3 end;
4 /
ADMIN SESSION uses this select while USER SESSION 1 (sid 143) is executing, we notice kglpnmod pin held = 2 ( shared )
SQL> select p.KGLPNSID,p.KGLPNCNT,p.KGLPNMOD,p.KGLPNREQ,o.kglnaobj,s.event
from x$kglpn p ,x$kglob o,v$session s
where p.kglpnuse=s.saddr and o.kglhdadr=p.kglpnhdl;
KGLPNSID KGLPNCNT KGLPNMOD KGLPNREQ KGLNAOBJ EVENT
143 2 2 0 DBMS_LOCK PL/SQL lock timer
143 3 2 0 PRC_I_WILL_SLEEP PL/SQL lock timer
143 2 2 0 DBMS_LOCK PL/SQL lock timer
While USER SESSION 1 ( sid 143 ) is executing the procedure, USER SESSION 2 ( sid 125 ) tries to alter the procedure. We notice this session hangs ie a user cannot alter a procedure/function,view, ... while another session is accessing it
SQL> select sid from v$mystat where rownum=1;
SID
125
Elapsed: 00:00:00.01
SQL> alter procedure prc_i_will_sleep compile;
ADMIN SESSION uses this select while USER SESSION 1 (sid 143) is executing and USER SESSION 2 is waiting ( sid 125 ). We notice kglpnreq pin request = 3 for session 2. The exclusive pin request for session 2 is not compatible with the shared pin modus already hold by session 1, hence session 2 is waiting for the event library cache pin.
KGLPNSID KGLPNCNT KGLPNMOD KGLPNREQ KGLNAOBJ EVENT
125 0 0 3 PRC_I_WILL_SLEEP library cache pin
143 2 2 0 DBMS_LOCK PL/SQL lock timer
143 3 2 0 PRC_I_WILL_SLEEP PL/SQL lock timer
143 2 2 0 DBMS_LOCK PL/SQL lock timer
But at the moment USER SESSION 2 is waiting on library cache pin request, there is not only 1 additional entry in the x$kglpn ( pin ) table with kglpnreq = 3, there is also one in x$kgllk ( lock ) table with kgllkmod ( lock mode ) 3
SQL> column EVENT format a20
SQL> column KGLNAOBJ format a20
SQL> column KGLLKPNC format a8
SQL> column KGLLKMOD format 999
SQL> column KGLLKREQ format 999
SQL> select s.sid,s.event,KGLNAOBJ,KGLLKPNC,KGLLKMOD,KGLLKREQ from x$kgllk l,v$session s where KGLLKUSE=s.saddr and KGLLKMOD > 2;
SID EVENT KGLNAOBJ KGLLKPNC KGLLKMOD KGLLKREQ
125 library cache pin PRC_I_WILL_SLEEP 00 3 0
Library cache locks
What when there comes another session 3 ( sid = 27 ) into play. Session 3 also tries to recompile the procedure.
SQL> select sid from v$mystat where rownum=1;
SID
27
Elapsed: 00:00:00.01
SQL> alter procedure prc_i_will_sleep compile;
ADMIN SESSION uses the below select. Since USER SESSION 2 (sid 125) is holding x$kgllk.kgllkmod = 3 ( exclusive ) for a longer period since it is waiting for the library cache pin request ( x$kglpn.kglpnreq = 3 ), USER SESSION 3 is waiting ( sid 27 ) for a library cache lock. We notice kgllk.kgllkreq lock request = 3 for session 3. The exclusive lock request for session 3 is not compatible with the lock modus already hold by session 2, hence session 3 is waiting for the event library cache lock.
SQL> column EVENT format a20
SQL> column KGLNAOBJ format a20
SQL> column KGLLKPNC format a8
SQL> column KGLLKMOD format 999
SQL> column KGLLKREQ format 999
SQL> select s.sid,s.event,KGLNAOBJ,KGLLKPNC,KGLLKMOD,KGLLKREQ from x$kgllk l,v$session s where KGLLKUSE=s.saddr and (KGLLKMOD > 2 or KGLLKREQ > 2);
SID EVENT KGLNAOBJ KGLLKPNC KGLLKMOD KGLLKREQ
27 library cache lock PRC_I_WILL_SLEEP 00 0 3
125 library cache pin PRC_I_WILL_SLEEP 00 3 0
So
1. when a session cannot exclusively pin an object in the library cache it is waiting with the event library cache pin.
2. when a session cannot exclusively lock an object in the library cache it is waiting with the event library cache lock.
