library cache pin 诊断思路

<pre name="code" class="sql"><pre name="code" class="sql">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
---------- ---------- ---------------- ---------- ---------- ----------------------------------------------------------------
      1628 2172090560 00000000817778C0 2196671232	 301 library cache pin

此时1628 在等待library cache pin的excusive

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

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

       SID	 Mode	     Req KGLPNHDL
---------- ---------- ---------- ----------------
      1628	    0	       3 00000000817778C0
      1644	    2	       0 00000000817778C0
  
SQL> desc x$kglpn;
名称 类型
------------ ----------------------------
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLPNADR RAW(4)
KGLPNUSE RAW(4) ---会话地址(对应v$session的saddr)
KGLPNSES RAW(4) ---owner地址
KGLPNHDL RAW(4) ---句柄
KGLPNLCK RAW(4)
KGLPNCNT NUMBER
KGLPNMOD NUMBER ---持有pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive) 
KGLPNREQ NUMBER ---请求pin的模式(0为no lock/pin held﹐1为null,2为share﹐3为exclusive)
KGLPNDMK NUMBER
KGLPNSPN NUMBER ---对应跟踪

此时1628在等待excusive模式,而1644是持有share 模式

11g可以直接查询ash: