11g library cache pin处理思路

<pre name="code" class="sql">create or replace procedure prc_test1 
is
begin
  loop
  execute immediate 'select * from dual';
end loop;
end; 

SESSION 20 执行存储过程: 

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

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	20	    0	       0

SQL> exec prc_test1;


此时的等待事件:
SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid=20;

       SID BLOCKING_SESSION	    P1 P1RAW			P2	   P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	20		     537557208 00000000200A78D8        293	    0 latch: shared pool


SESSION 1137编译存储过程:  

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

       SID STATISTIC#	   VALUE
---------- ---------- ----------
      1137	    0	       0

SQL> alter procedure prc_test1 compile;

SQL> select sid,blocking_session,p1,p1raw,p2,p3,event from v$session where event not like '%message%' and sid in (20,1137);

       SID BLOCKING_SESSION	    P1 P1RAW			P2	   P3 EVENT
---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
	20		     537557208 00000000200A78D8        293	    0 latch: shared pool
      1137		 20  832829180 0000000031A3F6FC  831962136 3.2728E+14 library cache pin


SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
    FROM x$kglpn p, v$session s 
    WHERE p.kglpnuse=s.saddr
    AND kglpnhdl like '%&P1RAW%'

SQL> SELECT s.sid, kglpnmod "Mode", kglpnreq "Req",p.kglpnhdl
    FROM x$kglpn p, v$session s 
    WHERE p.kglpnuse=s.saddr  2    3  ;

       SID	 Mode	     Req KGLPNHDL
---------- ---------- ---------- --------
      1137	    0	       3 31A3F6FC
	20	    2	       0 31A3F6FC

可以发现持有者为20