x$kglob/x$kgltr 中library cache信息查看（结果同dump内容）

最新推荐文章于 2021-04-06 16:40:04 发布

cishu8308

最新推荐文章于 2021-04-06 16:40:04 发布

阅读量437

点赞数

Kgl 解释 kernel layer generic layer library cache manager

以下两个表的说明可以先简单看下，然后看完这节内容之后再回过头来看下。

X$kglob 表说明如下：

Kglhdadr (children address) LIBRARY OBJECT HANDLE: handle

Kglhdpar (parent address) LIBRARY OBJECT HANDLE:handle

Kglnaown 依赖object的owner name

Kglnahsh sql的hash value （v$sql 中的hash_value）

Kglnahsv sql 的hash value （长串格式：9a5fb5d584eb42ce5f55b9c92930cf55）

Kglhdobj LIBRARY OBJECT: object

Kglobhd0 data#=0 的 heap 地址

Kglobhd1

Kglobhd2

Kglobhd3..7

Kglobts4 执行sql的schema name

X$kgltr 表说明如下：

Kglhdadr 同上

Kgltrorg 在dump内容中即为original

Kgltrfnl 在dump内容中即为final

测试用户 scott

测试表 t5

测试sql select count(*) from t5;

测试后使用 alter system set events ‘immediate trace name level_cache level 16’;

BUCKET 53077:

LIBRARY OBJECT HANDLE: handle=2a5790c4 mutex=2A579178(1)

name=select count(*) from t5

hash=9a5fb5d584eb42ce5f55b9c92930cf55 timestamp=01-06-2011 14:19:16

namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/SML/KST/DBN/MTX/[120100d4]

kkkk-dddd-llll=0001-0001-0001 lock=N pin=0 latch#=2 hpc=0002 hlc=0002

lwt=2A579120[2A579120,2A579120] ltm=2A579128[2A579128,2A579128]

pwt=2A579104[2A579104,2A579104] ptm=2A57910C[2A57910C,2A57910C]

ref=2A579140[2A579140,2A579140] lnd=2A57914C[2FD3FEE8,2A579964]

LIBRARY OBJECT: object=2eabd654

type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

CHILDREN: size=16

child# table reference handle

------ -------- --------- --------

0 2eb2feb8 2eb2fb6c 2a578fe0

DATA BLOCKS:

data# heap pointer status pins change whr alloc(K) size(K)

----- -------- -------- --------- ---- ------ --- -------- --------

0 3170cfcc 2eabd6ec I/P/A/-/- 0 NONE 00 0.67 1.05

BUCKET 53077 total object count=1

Sys登录，执行此sql：

select * from X$KGLOB where kglhdadr='2A5790C4';

执行结果如下：

ADDR 08FDF118

INDX 1093

INST_ID 1

KGLHDADR 2A5790C4

KGLHDPAR 2A5790C4

KGLHDCLT 2

KGLNAOWN

KGLNAOBJ select count(*) from t5

KGLFNOBJ

KGLNADLK

KGLNAHSH 691064661

KGLNAHSV 9a5fb5d584eb42ce5f55b9c92930cf55

KGLNATIM 2011-01-06 14:19:16

KGLNAPTM

KGLHDNSP 0

KGLHDLMD 1

KGLHDPMD 0

KGLHDFLG 302055636

KGLHDOBJ 2EABD654

KGLHDLDC 1

KGLHDIVC 0

KGLHDEXC 0

KGLHDLKC 1

KGLHDKMK 1

KGLHDDMK 1

KGLHDAMK 0

KGLOBFLG 1

KGLOBSTA 1

KGLOBTYP 0

KGLOBHMK 1

KGLOBHS0 1433

KGLOBHS1 0

KGLOBHS2 0

KGLOBHS3 0

KGLOBHS4 0

KGLOBHS5 0

KGLOBHS6 0

KGLOBHS7 0

KGLOBHD0 3170CFCC

KGLOBHD1 00

KGLOBHD2 00

KGLOBHD3 00

KGLOBHD4 00

KGLOBHD5 00

KGLOBHD6 00

KGLOBHD7 00

KGLOBPC0 0

KGLOBPC6 0

KGLOBTP0 00

KGLOBT00 0

KGLOBT01 0

KGLOBT02 3

KGLOBT03 5ypdtt4nm1mup

KGLOBT04 0

KGLOBT05 0

KGLOBT35 0

KGLOBT06 0

KGLOBT07 0

KGLOBT08 0

KGLOBT09 65535

KGLOBT10 0

KGLOBT11 0

KGLOBT12 0

KGLOBT13 0

KGLOBT14 0

KGLOBT15 0

KGLOBT16 0

KGLOBT17 2147483644

KGLOBT18 2147483644

KGLOBT19 -625018272

KGLOBT20 0

KGLOBT21 0

KGLOBT22 0

KGLOBT23 0

KGLOBT24 0

KGLOBT25 0

KGLOBT26 0

KGLOBT28 0

KGLOBT29 0

KGLOBT30 0

KGLOBT31 0

KGLOBT27 0

KGLOBT32 0

KGLOBT33 0

KGLOBWAP 0

KGLOBWCC 0

KGLOBWCL 0

KGLOBWUI 0

KGLOBWDW 0

KGLOBT42 0

KGLOBT43 0

KGLOBT44 9581

KGLOBT45 49

KGLOBT46 3047282718

KGLOBT47 0

KGLOBT49 0

KGLOBT50 0

KGLOBTL0 8

KGLOBTL1 0

KGLOBTS0 SQL*Plus

KGLOBTS1

KGLOBTN0

KGLOBTN1

KGLOBTN2

KGLOBTN3

KGLOBTN4

KGLOBTN5

KGLOBTS2

KGLOBTS3

KGLOBTS5

KGLOBTT0

KGLOBCCE

KGLOBCCEH 0

KGLOBCLA

KGLOBCLC 0

KGLOBCCC 0

KGLOBTS4

KGLOBCBCA

KGLOBT48 0

KGLOBDSO 0

KGLOBDEX 0

KGLOBDPX 0

KGLOBDLD 0

KGLOBDIV 0

KGLOBDPS 0

KGLOBDDR 0

KGLOBDDW 0

KGLOBDBF 0

KGLOBDRO 0

KGLOBDCP 0

KGLOBDEL 0

KGLOBDFT 0

KGLOBDEF 0

KGLOBDUI 0

KGLOBDCL 0

KGLOBDAP 0

KGLOBDCC 0

KGLOBDPL 0

KGLOBDJV 0

LIBRARY OBJECT HANDLE: handle=2a578fe0 mutex=2A579094(0)

namespace=CRSR flags=RON/KGHP/PN0/[10010000]

kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch#=2 hpc=0002 hlc=0002

lwt=2A57903C[2A57903C,2A57903C] ltm=2A579044[2A579044,2A579044]

pwt=2A579020[2A579020,2A579020] ptm=2A579028[2A579028,2A579028]

ref=2A57905C[2EB2FB6C,2EB2FB6C] lnd=2A579068[2A579068,2A579068]

LIBRARY OBJECT: object=2e927b20

type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0

DEPENDENCIES: count=1 size=16

dependency# table reference handle position flags

----------- -------- --------- -------- -------- -------------------

0 2e8f2aa0 2e8f27a0 2a578e88 [c1] 21 DEP[01]

READ ONLY DEPENDENCIES: count=1 size=16

dependency# table reference handle flags

----------- -------- --------- -------- -------------------

0 2e927edc 2e927c5c 2a5790c4 [c2] /ROD/KPP[60]

AUTHORIZATIONS: count=1 size=16 minimum entrysize=16

00000000 36000000 00020000 00000000

ACCESSES: count=1 size=16

dependency# types

----------- -----

0 0009

TRANSLATIONS: count=1 size=16

original final

-------- --------

2a578e88 2a578e88[c3]

DATA BLOCKS:

data# heap pointer status pins change whr alloc(K) size(K)

----- -------- -------- --------- ---- ------ --- -------- --------

0 3165316c 2e927c70 I/P/A/-/- 0 NONE 00 2.33 3.14

6 2e8f26d4 2e66c254 I/-/A/-/- 0 NONE 00 2.38 4.00

Sys登录，执行sql：

select * from X$KGLtr where kglhdadr = '2A578FE0';

结果如下：

ADDR	INDX	INST_ID	KGLHDADR	KGLTRORG	KGLTRFNL
097F18D8	211	1	2A578FE0	2A578E88	2A578E88

select * from X$KGLOB where kglhdadr='2A578FE0';

ADDR 08FDE10C

INDX 1094

INST_ID 1

KGLHDADR 2A578FE0

KGLHDPAR 2A5790C4[c4]

KGLHDCLT 2

KGLNAOWN

KGLNAOBJ select count(*) from t5

KGLFNOBJ

KGLNADLK

KGLNAHSH 691064661

KGLNAHSV 9a5fb5d584eb42ce5f55b9c92930cf55

KGLNATIM 2011-01-06 14:19:16

KGLNAPTM

KGLHDNSP 0

KGLHDLMD 1

KGLHDPMD 0

KGLHDFLG 268500992

KGLHDOBJ 2E927B20

KGLHDLDC 1

KGLHDIVC 0

KGLHDEXC 3

KGLHDLKC 1

KGLHDKMK 0

KGLHDDMK 65

KGLHDAMK 0

KGLOBFLG 1

KGLOBSTA 1

KGLOBTYP 0

KGLOBHMK 1

KGLOBHS0 3496

KGLOBHS1 0

KGLOBHS2 0

KGLOBHS3 0

KGLOBHS4 0

KGLOBHS5 0

KGLOBHS6 4096

KGLOBHS7 0

KGLOBHD0 3165316C

KGLOBHD1 00

KGLOBHD2 00

KGLOBHD3 00

KGLOBHD4 00

KGLOBHD5 00

KGLOBHD6 2E8F26D4

KGLOBHD7 00

KGLOBPC0 0

KGLOBPC6 0

KGLOBTP0 00

KGLOBT00 -32496

KGLOBT01 0

KGLOBT02 3

KGLOBT03 5ypdtt4nm1mup

KGLOBT04 3

KGLOBT05 3

KGLOBT35 3

KGLOBT06 110705

KGLOBT07 389178

KGLOBT08 2028

KGLOBT09 0

KGLOBT10 2016

KGLOBT11 532

KGLOBT12 3

KGLOBT13 1556

KGLOBT14 4703

KGLOBT15 3

KGLOBT16 1044

KGLOBT17 54

KGLOBT18 54

KGLOBT19 -625018272

KGLOBT30 1231860717

KGLOBWAP 0

KGLOBWCC 0

KGLOBWCL 0

KGLOBWUI 270202

KGLOBWDW 0

KGLOBT42 0

KGLOBT43 0

KGLOBT44 9581

KGLOBT45 49

KGLOBT46 3047282718

KGLOBT47 1.78072365088998E19

KGLOBT49 1.78072365088998E19

KGLOBT50 3427055676

KGLOBTL0 8

KGLOBTL1 0

KGLOBTS0 SQL*Plus

KGLOBTS1

KGLOBTN0 331

KGLOBTN1

KGLOBTN2

KGLOBTN3

....

KGLOBTS5 SYS$USERS

KGLOBTT0 2011-01-06 14:19:16

KGLOBCCE E289FB89A1E49800BA001000AEF9C3E2CFEA331056415555519521105555551545545558591555449665851D551105855515551515255245A5154A8E09502020000020000000000100001000000002002000027D000000402D00000032001010000080839FCB0C090000A01600104A8E09504646262040262320030020003020A000

KGLOBCCEH 2659209391

KGLOBCLA 2011-01-06 14:19:23

KGLOBCLC 0

KGLOBCCC 1

KGLOBTS4 SCOTT

....

BUCKET 32439:

LIBRARY OBJECT HANDLE: handle=2a578e88 mutex=2A578F3C(0)

name=SCOTT.T5

hash=7ff3733e0b2451a8befa0c5632207eb7 timestamp=01-05-2011 10:08:17

namespace=TABL flags=KGHP/TIM/SML/[02000000]

kkkk-dddd-llll=0000-0701-0701 lock=N pin=0 latch#=3 hpc=0002 hlc=0002

lwt=2A578EE4[2A578EE4,2A578EE4] ltm=2A578EEC[2A578EEC,2A578EEC]

pwt=2A578EC8[2A578EC8,2A578EC8] ptm=2A578ED0[2A578ED0,2A578ED0]

ref=2A578F04[2A578F04,2A578F04] lnd=2A578F10[2A57888C,2A57B06C]

LIBRARY OBJECT: object=2e8f9788

type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0

DATA BLOCKS:

data# heap pointer status pins change whr alloc(K) size(K)

----- -------- -------- --------- ---- ------ --- -------- --------

0 31517050 2e8f9820 I/-/A/-/- 0 NONE 00 0.52 0.00

8 2e8f99b0 2e8ff48c I/-/A/-/- 0 NONE 00 0.39 1.05

9 2e8f9a48 2df220b4 I/-/A/-/- 0 NONE 00 0.17 1.05

10 2e8f9a98 2eadf4f8 I/-/A/-/- 0 NONE 00 0.11 1.05

BUCKET 32439 total object count=1

Sys查询sql：

此内容也可在v$kgltable中查询到：

select * from X$KGLtable where kglhdadr = '2A578E88';

Sys查询sql：

select * from X$KGLOB where kglhdadr='2A578E88';

ADDR 090C9FEC

INDX 686

INST_ID 1

KGLHDADR 2A578E88

KGLHDPAR 2A578E88

KGLHDCLT 3

KGLNAOWN SCOTT

KGLNAOBJ T5

KGLFNOBJ

KGLNADLK

KGLNAHSH 840990391

KGLNAHSV 7ff3733e0b2451a8befa0c5632207eb7

KGLNATIM 2011-01-05 10:08:17

KGLNAPTM

KGLHDNSP 1

KGLHDLMD 1

KGLHDPMD 0

KGLHDFLG 33554432

KGLHDOBJ 2E8F9788

KGLHDLDC 1

KGLHDIVC 0

KGLHDEXC 0

KGLHDLKC 1

KGLHDKMK 0

KGLHDDMK 1

KGLHDAMK 0

KGLOBFLG 5

KGLOBSTA 1

KGLOBTYP 2

KGLOBHMK 0

KGLOBHS0 344

KGLOBHS1 0

KGLOBHS2 0

KGLOBHS3 0

KGLOBHS4 0

KGLOBHS5 0

KGLOBHS6 0

KGLOBHS7 0

KGLOBHD0 31517050

KGLOBHD1 00

总结：通过以上简述，可以理出一个条理来，即通过一个sql或者通过等待事件中的library cache handler能查找出在library cache中与之相关的内容，如同dump出来内容一样，过程如下：

以sql select count(*) from t5为例：

1） select * from X$KGLOB where kglnaobj like 'select count(*) from t5%';这个执行最少会有2条记录；

2） 从第一步中找到不一样（kglhdadr,kglhdpar）的那条记录，执行

Select * from v$kgltr where kglhdadr=&kglhdadr;

以等待事件pipe get 为例：

1) select * from X$KGLOB where kglhdadr='9DEE1F9B0';

2) 得到kglhdadr,kglhdpar，如果两个内容不一样，则执行

select * from X$KGLOB where kglhdpar=&kglhdpar;

select * from x$kgltr where kglhdadr=&kglhdadr;

如果两个内容一样，则

select * from X$KGLOB where kglhdpar=&kglhdpar;

找到kglhdadr与kglhdpar不一样的那个kglhdadr的值,

执行 select * from x$kgltr where kglhdadr=$kglhdadr;

1) 在第二步中，会得到translation table中的内容kgltrorg,kgltrfnl

2) 查询 select * from x$kglob where kglhdadr=&kgltrfnl;

以上我是借助了x$kgltr这张表，从而将sql的内容从上到下简单查了一遍，还可以借助其他方法，如v$object_dependency

(select * from v$object_dependency where from_address=&kglhdpar

或者

Select * from v$object_dependency where from_hash=&kglnahsh)

还有其他多种方法，如x$kgldp

(select * from v$kgldp where kglhdpar=&kglhdpar)

另heap中的内容可查看x$ksmhp ,如

select * from x$ksmhp t where ksmchds=hextoraw('3165316C');

其中，3165316C是heap0的address，也可以查看其他heap地址中的内容。

。。。。待续。。。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25093763/viewspace-1044219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25093763/viewspace-1044219/

cishu8308

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
x$kglob/x$kgltr 中library cache信息查看（结果同dump内容）

Kgl 解释 kernel layer generic layer library cache manager 以下两个表的说明可以先简单看下，然后看完这节内容之后再回过头来看下。X$kglob 表说明如下：Kglhdadr (...
复制链接

扫一扫