Friday, December 20, 2013

Recursive Procedure Compilation

This was tested on Oracle 11.2. You cannot compile a procedure if anybody is running it so, if you write a procedure which compiles itself, it hangs:
 
SQL> conn andrew/reid
Connected.
SQL> select distinct sid from v$mystat
  2  /
 
       SID
----------
       296
 
SQL> create procedure blah
  2  is
  3  begin
  4  execute immediate 'alter procedure blah compile';
  5  end;
  6  /
 
Procedure created.
 
SQL> exec blah;
 
This appears in V$SESSION_EVENT as 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: 296
old   4: and sid = '&&sid'
new   4: and sid = '296'
 
TIME_WAITED/100
---------------
         459.91
 
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 = '296'
 
TIME_WAITED/100
---------------
         468.94
 
SQL>
 
… and, if you look in x$kglpn, you see that SID 296 is blocking and waiting simultaneously:
 
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: 296
old   5: and w.sid = &sid
new   5: and w.sid = 296
 
       SID       Mode        Req
---------- ---------- ----------
       296          0          3
       296          2          0
 
SQL>

No comments: