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>
