oracle x kglob,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;