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/