12 | 12 | 2017
Latest Articles
Popular Articles

Performance Tuning

Wait event : library cache pin and library cache locks

User Rating:  / 1
PoorBest 

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.