library cache lock诊断思路

create or replace procedure prc_test1 
is
begin
  loop
  execute immediate 'select * from dual';
end loop;
end; 
SESSION 1644 执行存储过程:  
SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
      1644	    0	       1

SQL> exec prc_test1;

  
SESSION 1628 编译存储过程:

SQL> select * from v$mystat where rownum<2;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
      1628	    0	       1

SQL> alter procedure prc_test1 compile;


此时出现如下等待事件；
SQL> select sid,p1, P1RAW,p2,p3,event from v$session_wait where event like '%library cache%';

       SID	   P1 P1RAW		       P2	        P3 EVENT
---------- ---------- ---------------- ---------- ---------- --------