11g library cache pin处理思路

<pre name="code" class="sql">create or replace procedure prc_test1 isbegin  loop  execute immediate 'select * from dual';end loop;end; SESSION 20 执行存储过程: SQL> select * from v$mystat where rownum<2;       SID STATISTIC#   VALUE---------- ---------- ----------20    0       0SQL> 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 P1RAWP2   P3 EVENT---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------20     537557208 00000000200A78D8        293    0 latch: shared poolSESSION 1137编译存储过程:  SQL>  select * from v$mystat where rownum<2;         SID STATISTIC#   VALUE---------- ---------- ----------      1137    0       0SQL> 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 P1RAWP2   P3 EVENT---------- ---------------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------20     537557208 00000000200A78D8        293    0 latch: shared pool      1137 20  832829180 0000000031A3F6FC  831962136 3.2728E+14 library cache pinSELECT 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 31A3F6FC20    2       0 31A3F6FC可以发现持有者为20