Friday, December 20, 2013

Library Cache Pin and Library Cache Lock Wait Events

A developer was trying to compile a procedure without success recently so I decided to reproduce the problem for future reference. I did this in an Oracle 11.2 database. First I created a user then I used it to login, display my SID, create a long running procedure and kick it off. You can see this in the red session below:
 
SQL> conn / as sysdba
Connected.
SQL> create user andrew identified by reid
  2  /
 
User created.
 
SQL> grant create session, create procedure,
  2  select any dictionary to andrew
  3  /
 
Grant succeeded.
 
SQL> grant execute on dbms_lock to andrew
  2  /
 
Grant succeeded.
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       296
 
SQL> create procedure lcp
  2  is
  3  begin
  4  sys.dbms_lock.sleep(3600);
  5  end;
  6  /
 
Procedure created.
 
SQL> exec lcp;
 
While this was happening, I logged in with the same user and tried to compile the procedure in the blue session below. You cannot compile a procedure while another session is running it so the compilation had to wait:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       490
 
SQL> alter procedure lcp compile
  2  /
 
I connected to the database as SYS in the green session below and used the SID to determine that the blue session was waiting for a library cache pin:
 
SQL> conn / as sysdba
Connected.
SQL> undefine sid
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
Enter value for sid: 490
old   4: and sid = '&&sid'
new   4: and sid = '490'
 
TIME_WAITED/100
---------------
          30.07
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache pin'
  4  and sid = '&&sid'
  5  /
old   4: and sid = '&&sid'
new   4: and sid = '490'
 
TIME_WAITED/100
---------------
          39.08
 
SQL>
 
I logged in as ANDREW and tried to compile the procedure again in the purple session which follows:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       394
 
SQL> alter procedure lcp compile
  2  /
 
I connected as an administrator then used the SID to determine that the purple session was waiting for a library cache lock. This happens when more than one session tries to compile the same procedure simultaneously. You can see this in the green session which follows:
 
SQL> undefine sid
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache lock'
  4  and sid = '&&sid'
  5  /
Enter value for sid: 394
old   4: and sid = '&&sid'
new   4: and sid = '394'
 
TIME_WAITED/100
---------------
          24.05
 
SQL> exec sys.dbms_lock.sleep(10);
 
PL/SQL procedure successfully completed.
 
SQL> select time_waited/100
  2  from v$session_event
  3  where event = 'library cache lock'
  4  and sid = '&&sid'
  5  /
old   4: and sid = '&&sid'
new   4: and sid = '394'
 
TIME_WAITED/100
---------------
          33.07
 
SQL>
 
Finally, I used the script below to locate the session causing the problem. I gave it the SID of the session waiting for the library cache pin. It showed that SID 296 was holding a lock and SID 490 was waiting for one. Teminating SID 296 would allow both compilations to run:
 
SQL> conn / as sysdba
Connected.
SQL> select s.sid, kglpnmod "Mode", kglpnreq "Req"
  2  from x$kglpn p, v$session s, v$session_wait w
  3  where p.kglpnuse = s.saddr
  4  and p.kglpnhdl = w.p1raw
  5  and w.sid = &sid
  6  /
Enter value for sid: 490
old   5: and w.sid = &sid
new   5: and w.sid = 490
 
       SID       Mode        Req
---------- ---------- ----------
       490          0          3
       296          2          0
 
SQL>

No comments: