X$系列之三：x$kglob、x$kgllk、x$kglpn关于库缓存

1：我常用快速解决问题的语句：
select 'kill -9 '||spid from v$process where addr in (select /*+ ordered */ h1.paddr 
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 where
  (((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
   and
     (((w.kgllkmod = 0) or (w.kgllkmod= 1))
     and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
  and  w.kgllktype       =  h.kgllktype
  and  w.kgllkhdl        =  h.kgllkhdl
  and  w.kgllkuse     =   w1.saddr
  and  h.kgllkuse     =   h1.saddr
  and  h1.type='USER')
/

select /*+ ordered */ w1.sid  waiting_session,
        h1.sid  holding_session,
        w.kgllktype lock_or_pin,
        w.kgllkhdl address,
        decode(h.kgllkmod,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
           'Unknown') mode_held,
        decode(w.kgllkreq,  0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
           'Unknown') mode_requested
  from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
 where
  (((h.kgllkmod != 0) and (h.kgllkmod != 1)
     and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
   and
     (((w.kgllkmod = 0) or (w.kgllkmod= 1))
     and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
  and  w.kgllktype       =  h.kgllktype
  and  w.kgllkhdl        =  h.kgllkhdl
  and  w.kgllkuse     =   w1.saddr
  and  h.kgllkuse     =   h1.saddr
/

2：分析语句的x$表
select view_definition from v$fixed_view_definition where view_name='DBA_KGLLOCK';

CREATE OR REPLACE FORCE VIEW SYS.dba_kgllock (kgllkuse,
                                              kgllkhdl,
                                              kgllkmod,
                                              kgllkreq,
                                              kgllktype
                                             )
AS
   SELECT kgllkuse, kgllkhdl, kgllkmod, kgllkreq, 'Lock' kgllktype
     FROM x$kgllk
   UNION ALL
   SELECT kglpnuse, kglpnhdl, kglpnmod, kglpnreq, 'Pin' kgllktype
     FROM x$kglpn;
    
x$kgllk;
ADDR RAW(4)
INDX NUMBER
INST_ID NUMBER
KGLLKADR RAW(4)
KGLLKUSE RAW(4) ---(v$session.saddr)
KGLLKSES RAW(4) ---owner地址
KGLLKSNM NUMBER ---SID
KGLLKHDL RAW(4) ---handle address
KGLLKPNC RAW(4) ---the address of the call pin
KGLLKPNS RAW(4) ---trace中的session pin值
KGLLKCNT NUMBER
KGLLKMOD NUMBER ---持有lock的模式(0为nolock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKREQ NUMBER ---请求lock的模式(0为nolock/pin held﹐1为null,2为share﹐3为exclusive)
KGLLKFLG NUMBER ---cursor的状态
KGLLKSPN NUMBER ---trace中的savepoint的值
KGLLKHTB RAW(4)
KGLNAHSH NUMBER ---(v$session.sql_hash_value)
KGLLKSQLID VARCHAR2(13) ---(v$session.sql_id)
KGLHDPAR RAW(4) ---(v$session.sql_address)
KGLHDNSP NUMBER
USER_NAME VARCHAR2(30) ---(v$session.username)
KGLNAOBJ VARCHAR2(60)

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) ---handle address (v$session.p1)
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 ---trace中的savepoint的值

rac实例有自己的shared pool，handle地址不同，用handle地址去查找要注意。
但是x$kglob中被pin住的对象是不变的，所以handle地址可以从x$kglob中去查找。
x$kglob:
addr:
kglnaown:对象属主
kglnaobj:对象名
kglhdpar:父游标地址
kglhdadr:游标地址，kglhdpar=kglhdadr就是父游标（v$session.p1 lc pin）
kglnahsh:(v$sql.hash_value lc pin)(v$session.p1 lc mutex x)
kglhdobj:
kglobtyd:
kglobt23:
kglobt24:
kglhdexc:
kglhdnsp:

3：库缓存常见等待事件
3.1：library cache pin:
v$session.event--v$session.p1--x$kglpn.kglpnhdl--x$kglob.kglhdadr--kglnaown, kglnaobj
v$session.event--v$session.saddr--x$kglpn.kglpnuse--x$kglpn.kglpnhdl--x$kglob.kglhdadr--kglnaown, kglnaobj
v$session.event--v$session.p1--x$kglob.kglhdadr--kglnaown, kglnaobj

select addr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj from x$kglob
where kglhdadr=v$session.p1(lc pin kglnahsh=v$sql.hash_value)

select a.sid,a.username,a.program,b.addr,b.kglpnadr,b.kglpnuse,b.kglpnses,b.kglpnhdl,b.kglpnlck,b.kglpnmod,kglpnreq
from v$session a,x$kglpn b
where a.saddr=b.kglpnuse and b.kglpnhdl=v$session.p1(lc pin) and b.kglpnmod<>0;

select namespace,reloads,invalidations from v$librarycache;
reloads means cursor heaps were kicked out implying shared pool too small

3.2：library cache lock:
同上x$kglpn换为x$kgllk

3.3：library cache: mutex X：
select p1,count(1) from v$session where event='library cache: mutex X' group by p1;
select kglhdadr address,kglnaown,substr(kglnaobj,1,20) name,kglnahsh hash_value,kglobtyd type,
kglobt23 locked_total,kglobt24 pinned_total,kglhdexc executions,kglhdnsp namespace from x$kglob where kglnahsh=v$session.p1(lc mutex x )

3.4：cursor: pin S wait on X 查找holder
SELECT decode(trunc(&P2/4294967296),0,trunc(&P2/65536),trunc(&P2/4294967296)) SID_HOLDING_MUTEX FROM dual;