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>