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

Sys登录，执行此sql：

select*fromX$KGLOBwherekglhdadr='2A5790C4';

执行结果如下：

ADDR08FDF118

INDX1093

INST_ID1

KGLHDADR2A5790C4

KGLHDPAR2A5790C4

KGLHDCLT2

KGLNAOWN

KGLNAOBJselect count(*) from t5

KGLFNOBJ

KGLNADLK

KGLNAHSH691064661

KGLNAHSV9a5fb5d584eb42ce5f55b9c92930cf55

KGLNATIM2011-01-06 14:19:16

KGLNAPTM

KGLHDNSP0

KGLHDLMD1

KGLHDPMD0

KGLHDFLG302055636

KGLHDOBJ2EABD654

KGLHDLDC1

KGLHDIVC0

KGLHDEXC0

KGLHDLKC1

KGLHDKMK1

KGLHDDMK1

KGLHDAMK0

KGLOBFLG1

KGLOBSTA1

KGLOBTYP0

KGLOBHMK1

KGLOBHS01433

KGLOBHS10

KGLOBHS20

KGLOBHS30

KGLOBHS40

KGLOBHS50

KGLOBHS60

KGLOBHS70

KGLOBHD03170CFCC

KGLOBHD100

KGLOBHD200

KGLOBHD300

KGLOBHD400

KGLOBHD500

KGLOBHD600

KGLOBHD700

KGLOBPC00

KGLOBPC60

KGLOBTP000

KGLOBT000

KGLOBT010

KGLOBT023

KGLOBT035ypdtt4nm1mup

KGLOBT040

KGLOBT050

KGLOBT350

KGLOBT060

KGLOBT070

KGLOBT080

KGLOBT0965535

KGLOBT100

KGLOBT110

KGLOBT120

KGLOBT130

KGLOBT140

KGLOBT150

KGLOBT160

KGLOBT172147483644

KGLOBT182147483644

KGLOBT19-625018272

KGLOBT200

KGLOBT210

KGLOBT220

KGLOBT230

KGLOBT240

KGLOBT250

KGLOBT260

KGLOBT280

KGLOBT290

KGLOBT300

KGLOBT310

KGLOBT270

KGLOBT320

KGLOBT330

KGLOBWAP0

KGLOBWCC0

KGLOBWCL0

KGLOBWUI0

KGLOBWDW0

KGLOBT420

KGLOBT430

KGLOBT449581

KGLOBT4549

KGLOBT463047282718

KGLOBT470

KGLOBT490

KGLOBT500

KGLOBTL08

KGLOBTL10

KGLOBTS0SQL*Plus

KGLOBTS1

KGLOBTN0

KGLOBTN1

KGLOBTN2

KGLOBTN3

KGLOBTN4

KGLOBTN5

KGLOBTS2

KGLOBTS3

KGLOBTS5

KGLOBTT0

KGLOBCCE

KGLOBCCEH0

KGLOBCLA

KGLOBCLC0

KGLOBCCC0

KGLOBTS4

KGLOBCBCA

KGLOBT480

KGLOBDSO0

KGLOBDEX0

KGLOBDPX0

KGLOBDLD0

KGLOBDIV0

KGLOBDPS0

KGLOBDDR0

KGLOBDDW0

KGLOBDBF0

KGLOBDRO0

KGLOBDCP0

KGLOBDEL0

KGLOBDFT0

KGLOBDEF0

KGLOBDUI0

KGLOBDCL0

KGLOBDAP0

KGLOBDCC0

KGLOBDPL0

KGLOBDJV0

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 referencehandle position flags

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

0 2e8f2aa02e8f27a0

READ ONLY DEPENDENCIES: count=1 size=16

dependency#table referencehandle flags

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

0 2e927edc2e927c5c

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

originalfinal

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

DATA BLOCKS:

data#heappointerstatus pins change whr alloc(K)size(K)

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

0 3165316c 2e927c70 I/P/A/-/-0 NONE002.333.14

6 2e8f26d4 2e66c254 I/-/A/-/-0 NONE002.384.00

Sys登录，执行sql：

select*fromX$KGLtrwherekglhdadr='2A578FE0';

结果如下：

ADDRINDXINST_IDKGLHDADRKGLTRORGKGLTRFNL

097F18D821112A578FE02A578E882A578E88

select*fromX$KGLOBwherekglhdadr='2A578FE0';

ADDR08FDE10C

INDX1094

INST_ID1

KGLHDADR2A578FE0

KGLHDPAR

KGLHDCLT2

KGLNAOWN

KGLNAOBJselect count(*) from t5

KGLFNOBJ

KGLNADLK

KGLNAHSH691064661

KGLNAHSV9a5fb5d584eb42ce5f55b9c92930cf55

KGLNATIM2011-01-06 14:19:16

KGLNAPTM

KGLHDNSP0

KGLHDLMD1

KGLHDPMD0

KGLHDFLG268500992

KGLHDOBJ2E927B20

KGLHDLDC1

KGLHDIVC0

KGLHDEXC3

KGLHDLKC1

KGLHDKMK0

KGLHDDMK65

KGLHDAMK0

KGLOBFLG1

KGLOBSTA1

KGLOBTYP0

KGLOBHMK1

KGLOBHS03496

KGLOBHS10

KGLOBHS20

KGLOBHS30

KGLOBHS40

KGLOBHS50

KGLOBHS64096

KGLOBHS70

KGLOBHD03165316C

KGLOBHD100

KGLOBHD200

KGLOBHD300

KGLOBHD400

KGLOBHD500

KGLOBHD62E8F26D4

KGLOBHD700

KGLOBPC00

KGLOBPC60

KGLOBTP000

KGLOBT00-32496

KGLOBT010

KGLOBT023

KGLOBT035ypdtt4nm1mup

KGLOBT043

KGLOBT053

KGLOBT353

KGLOBT06110705

KGLOBT07389178

KGLOBT082028

KGLOBT090

KGLOBT102016

KGLOBT11532

KGLOBT123

KGLOBT131556

KGLOBT144703

KGLOBT153

KGLOBT161044

KGLOBT1754

KGLOBT1854

KGLOBT19-625018272

KGLOBT301231860717

KGLOBWAP0

KGLOBWCC0

KGLOBWCL0

KGLOBWUI270202

KGLOBWDW0

KGLOBT420

KGLOBT430

KGLOBT449581

KGLOBT4549

KGLOBT463047282718

KGLOBT471.78072365088998E19

KGLOBT491.78072365088998E19

KGLOBT503427055676

KGLOBTL08

KGLOBTL10

KGLOBTS0SQL*Plus

KGLOBTS1

KGLOBTN0331

KGLOBTN1

KGLOBTN2

KGLOBTN3

....

KGLOBTS5SYS$USERS

KGLOBTT02011-01-06 14:19:16

KGLOBCCEE289FB89A1E49800BA001000AEF9C3E2CFEA331056415555519521105555551545545558591555449665851D551105855515551515255245A5154A8E09502020000020000000000100001000000002002000027D000000402D00000032001010000080839FCB0C090000A01600104A8E09504646262040262320030020003020A000

KGLOBCCEH2659209391

KGLOBCLA2011-01-06 14:19:23

KGLOBCLC0

KGLOBCCC1

KGLOBTS4SCOTT

....

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#heappointerstatus pins change whr alloc(K)size(K)

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

0 31517050 2e8f9820 I/-/A/-/-0 NONE000.520.00

8 2e8f99b0 2e8ff48c I/-/A/-/-0 NONE000.391.05

9 2e8f9a48 2df220b4 I/-/A/-/-0 NONE000.171.05

10 2e8f9a98 2eadf4f8 I/-/A/-/-0 NONE000.111.05

BUCKET 32439 total object count=1

Sys查询sql：

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

select*fromX$KGLtablewherekglhdadr='2A578E88';

Sys查询sql：

select*fromX$KGLOBwherekglhdadr='2A578E88';

ADDR090C9FEC

INDX686

INST_ID1

KGLHDADR2A578E88

KGLHDPAR2A578E88

KGLHDCLT3

KGLNAOWNSCOTT

KGLNAOBJT5

KGLFNOBJ

KGLNADLK

KGLNAHSH840990391

KGLNAHSV7ff3733e0b2451a8befa0c5632207eb7

KGLNATIM2011-01-05 10:08:17

KGLNAPTM

KGLHDNSP1

KGLHDLMD1

KGLHDPMD0

KGLHDFLG33554432

KGLHDOBJ2E8F9788

KGLHDLDC1

KGLHDIVC0

KGLHDEXC0

KGLHDLKC1

KGLHDKMK0

KGLHDDMK1

KGLHDAMK0

KGLOBFLG5

KGLOBSTA1

KGLOBTYP2

KGLOBHMK0

KGLOBHS0344

KGLOBHS10

KGLOBHS20

KGLOBHS30

KGLOBHS40

KGLOBHS50

KGLOBHS60

KGLOBHS70

KGLOBHD031517050

KGLOBHD100

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

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

1)select*fromX$KGLOBwherekglnaobjlike'select count(*) from t5%';这个执行最少会有2条记录；

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

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

以等待事件pipe get为例：

1)select*fromX$KGLOBwherekglhdadr='9DEE1F9B0';

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

select*fromX$KGLOBwherekglhdpar=&kglhdpar;

select*fromx$kgltrwherekglhdadr=&kglhdadr;

如果两个内容一样，则

select*fromX$KGLOBwherekglhdpar=&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*fromx$ksmhp twhereksmchds=hextoraw('3165316C');

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

。。。。待续。。。