link:
http://www.eygle.com/internal/shared_pool-3.htm
基本命令：
alter session set events 'immediate trace name library_cache level ll';
其中ll代表level级别,对于9.2.0及以后版本,不同level含义如下:
level =1 ,转储library cache统计信息
level =2 ,转储hash table概要
level =4 ,转储library cache对象，只包含基本信息
level =8 ,转储library cache对象，包含详细信息(包括child references,pin waiters等)
level =16,增加heap sizes信息
level =32,增加heap信息
library cache由一个hash表组成,而hash表是一个由hash buckets组成的数组.
每个hash bucket都是包含library cache handle的一个双向链表。
library cache handle指向library cache object和一个引用列表.
library cache对象进一步分为:依赖表、子表和授权表等
我们看一下library cache的结构:
通过
alter session set events 'immediate trace name library_cache level 4'
获得以下输出(这部分信息来自oracle8i,trace文件可以从www.eygle.com上找到)
点击这里下载: hsbi_ora_4614.trc
第一部分(等价于level 1):
library cache statistics:
gets hit ratio pins hit ratio reloads invalids namespace
---------- --------- ---------- --------- ---------- ---------- ---------
619658171 0.9999160 2193292112 0.9999511 9404 380 crsr
79698558 0.9998832 424614847 0.9999108 13589 0 tabl/prcd/type
163399 0.9979926 163402 0.9978948 16 0 body/tybd
0 0.0000000 0 0.0000000 0 0 trgr
34 0.0294118 35 0.0571429 0 0 indx
18948 0.9968862 24488 0.9953855 0 0 clst
0 0.0000000 0 0.0000000 0 0 obje
0 0.0000000 0 0.0000000 0 0 pipe
0 0.0000000 0 0.0000000 0 0 lob
0 0.0000000 0 0.0000000 0 0 dir
0 0.0000000 0 0.0000000 0 0 queu
0 0.0000000 0 0.0000000 0 0 objg
0 0.0000000 0 0.0000000 0 0 prop
0 0.0000000 0 0.0000000 0 0 jvsc
0 0.0000000 0 0.0000000 0 0 jvre
0 0.0000000 0 0.0000000 0 0 robj
0 0.0000000 0 0.0000000 0 0 reip
0 0.0000000 0 0.0000000 0 0 cpob
115071 0.9992179 115071 0.9930999 704 0 evnt
0 0.0000000 0 0.0000000 0 0 summ
0 0.0000000 0 0.0000000 0 0 dimn
0 0.0000000 0 0.0000000 0 0 ctx
0 0.0000000 0 0.0000000 0 0 outl
0 0.0000000 0 0.0000000 0 0 ruls
0 0.0000000 0 0.0000000 0 0 rmgr
0 0.0000000 0 0.0000000 0 0 unused
0 0.0000000 0 0.0000000 0 0 ppln
0 0.0000000 0 0.0000000 0 0 pcls
0 0.0000000 0 0.0000000 0 0 subs
0 0.0000000 0 0.0000000 0 0 locs
0 0.0000000 0 0.0000000 0 0 rmob
0 0.0000000 0 0.0000000 0 0 rsmd
699654181 0.9999117 2618209955 0.9999440 23713 380 cumulative
这部分信息也就是v$librarycache中显示的.
第二部分(等价于level 2中的输出):
library cache hash table: size=509 count=354
bucket 0:
bucket 1:
bucket 2: *
bucket 3:
bucket 4:
bucket 5: *
bucket 6: *
bucket 7:
bucket 8: **
bucket 9: ***
bucket 10: *
bucket 11: *
bucket 12: ***
bucket 13: *
bucket 14: *
bucket 15:
bucket 16: *
bucket 17:
bucket 18: *
bucket 19:
bucket 20:
bucket 21: *
bucket 22:
bucket 23:
bucket 24: *
bucket 25:
bucket 26:
bucket 27: ***
bucket 28:
bucket 29: **
bucket 30:
bucket 31:
bucket 32: ***
bucket 33: *
bucket 34:
bucket 35:
bucket 36: **
bucket 37:
bucket 38: **
bucket 39: *
bucket 40: *
bucket 41:
bucket 42:
bucket 43:
bucket 44:
bucket 45:
bucket 46: ****
bucket 47:
bucket 48:
bucket 49: *
bucket 50: *
bucket 51:
bucket 52: ***
bucket 53: **
bucket 54:
bucket 55: *
bucket 56:
bucket 57:
bucket 58:
bucket 59: *
bucket 60: **
bucket 61:
bucket 62: *
bucket 63:
bucket 64: *
bucket 65:
bucket 66:
bucket 67: *
bucket 68:
bucket 69: **
bucket 70:
bucket 71:
bucket 72: *
bucket 73:
bucket 74:
bucket 75: *
bucket 76: **
bucket 77:
bucket 78: ****
bucket 79:
bucket 80: *
bucket 81: *
bucket 82:
bucket 83: **
bucket 84: *
bucket 85:
bucket 86:
bucket 87:
bucket 88:
bucket 89: *
bucket 90: *
bucket 91:
bucket 92: *
bucket 93: *
bucket 94: *
bucket 95:
bucket 96: *
bucket 97:
bucket 98:
bucket 99: ***
bucket 100: *
bucket 101:
bucket 102: *
bucket 103:
bucket 104: *
bucket 105:
bucket 106:
bucket 107: ****
bucket 108:
bucket 109:
bucket 110:
bucket 111: *
bucket 112: **
bucket 113:
bucket 114:
bucket 115:
bucket 116: *
bucket 117:
bucket 118: *****
bucket 119:
bucket 120: *
bucket 121:
bucket 122:
bucket 123:
bucket 124:
bucket 125: *
bucket 126:
bucket 127:
bucket 128: *
bucket 129:
bucket 130: *
bucket 131: *
bucket 132:
bucket 133:
bucket 134:
bucket 135: *
bucket 136:
bucket 137:
bucket 138:
bucket 139: *
bucket 140: *
bucket 141: *
bucket 142:
bucket 143: *
bucket 144:
bucket 145: ***
bucket 146:
bucket 147: *
bucket 148:
bucket 149:
bucket 150: **
bucket 151:
bucket 152:
bucket 153: *
bucket 154:
bucket 155:
bucket 156:
bucket 157:
bucket 158:
bucket 159:
bucket 160:
bucket 161:
bucket 162:
bucket 163:
bucket 164: *
bucket 165: *
bucket 166:
bucket 167:
bucket 168:
bucket 169:
bucket 170: **
bucket 171:
bucket 172: *
bucket 173:
bucket 174:
bucket 175: *
bucket 176: *
bucket 177:
bucket 178:
bucket 179:
bucket 180:
bucket 181: *
bucket 182:
bucket 183:
bucket 184:
bucket 185: *
bucket 186:
bucket 187:
bucket 188: **
bucket 189:
bucket 190: *
bucket 191: *
bucket 192:
bucket 193:
bucket 194: *
bucket 195: **
bucket 196: *
bucket 197: **
bucket 198: ****
bucket 199: *
bucket 200: *
bucket 201: *
bucket 202: **
bucket 203:
bucket 204:
bucket 205: **
bucket 206:
bucket 207:
bucket 208: *
bucket 209: **
bucket 210:
bucket 211: *
bucket 212: *
bucket 213: *
bucket 214:
bucket 215:
bucket 216:
bucket 217: *
bucket 218: *
bucket 219:
bucket 220:
bucket 221: *
bucket 222:
bucket 223: *
bucket 224:
bucket 225:
bucket 226: *
bucket 227:
bucket 228: *
bucket 229: **
bucket 230: *
bucket 231:
bucket 232: **
bucket 233:
bucket 234: *
bucket 235: *
bucket 236:
bucket 237:
bucket 238: *
bucket 239:
bucket 240: **
bucket 241: **
bucket 242: **
bucket 243: ***
bucket 244:
bucket 245: *
bucket 246:
bucket 247:
bucket 248: **
bucket 249:
bucket 250:
bucket 251: **
bucket 252:
bucket 253: *
bucket 254: *
bucket 255:
bucket 256:
bucket 257: **
bucket 258: *
bucket 259:
bucket 260:
bucket 261: *
bucket 262: **
bucket 263: ***
bucket 264:
bucket 265: *
bucket 266:
bucket 267: *
bucket 268: *
bucket 269:
bucket 270:
bucket 271: **
bucket 272: *
bucket 273:
bucket 274: *
bucket 275: *
bucket 276: **
bucket 277:
bucket 278:
bucket 279:
bucket 280:
bucket 281: **
bucket 282: *
bucket 283: *
bucket 284: *
bucket 285: *
bucket 286:
bucket 287: *
bucket 288:
bucket 289:
bucket 290: **
bucket 291:
bucket 292: *
bucket 293:
bucket 294: *
bucket 295:
bucket 296: *
bucket 297:
bucket 298:
bucket 299: **
bucket 300: *
bucket 301:
bucket 302: *
bucket 303: *
bucket 304: **
bucket 305: **
bucket 306:
bucket 307:
bucket 308: *
bucket 309:
bucket 310:
bucket 311: **
bucket 312: *
bucket 313:
bucket 314: *
bucket 315:
bucket 316:
bucket 317:
bucket 318:
bucket 319: ***
bucket 320: *
bucket 321: **
bucket 322: **
bucket 323:
bucket 324: *
bucket 325:
bucket 326: *
bucket 327: *
bucket 328: **
bucket 329:
bucket 330: *
bucket 331:
bucket 332:
bucket 333: *
bucket 334: *
bucket 335: ***
bucket 336: *
bucket 337: **
bucket 338: *
bucket 339: *
bucket 340:
bucket 341: *
bucket 342: *
bucket 343: **
bucket 344:
bucket 345:
bucket 346:
bucket 347: *
bucket 348:
bucket 349: ***
bucket 350: *
bucket 351:
bucket 352:
bucket 353:
bucket 354: *
bucket 355: **
bucket 356:
bucket 357:
bucket 358: **
bucket 359: *
bucket 360: *
bucket 361: **
bucket 362:
bucket 363:
bucket 364: *
bucket 365: *
bucket 366: **
bucket 367: *
bucket 368:
bucket 369: *
bucket 370:
bucket 371: ***
bucket 372:
bucket 373: *
bucket 374:
bucket 375:
bucket 376: *
bucket 377:
bucket 378:
bucket 379:
bucket 380:
bucket 381:
bucket 382:
bucket 383: **
bucket 384:
bucket 385:
bucket 386:
bucket 387: ***
bucket 388: *
bucket 389:
bucket 390:
bucket 391:
bucket 392:
bucket 393: *
bucket 394: *
bucket 395: *
bucket 396:
bucket 397:
bucket 398:
bucket 399:
bucket 400: **
bucket 401:
bucket 402:
bucket 403:
bucket 404:
bucket 405:
bucket 406:
bucket 407: *
bucket 408: *
bucket 409: *
bucket 410:
bucket 411: *
bucket 412:
bucket 413:
bucket 414:
bucket 415:
bucket 416: *
bucket 417:
bucket 418: *
bucket 419:
bucket 420: **
bucket 421: *
bucket 422:
bucket 423: **
bucket 424: ***
bucket 425:
bucket 426: *
bucket 427: *
bucket 428: **
bucket 429:
bucket 430:
bucket 431:
bucket 432:
bucket 433: *
bucket 434:
bucket 435: **
bucket 436: *
bucket 437: *
bucket 438:
bucket 439: *
bucket 440:
bucket 441:
bucket 442:
bucket 443: *
bucket 444:
bucket 445: *
bucket 446:
bucket 447: *
bucket 448:
bucket 449: *
bucket 450:
bucket 451:
bucket 452: *
bucket 453: *
bucket 454: *
bucket 455:
bucket 456:
bucket 457:
bucket 458: *
bucket 459: **
bucket 460:
bucket 461: **
bucket 462: *
bucket 463:
bucket 464: *
bucket 465: *
bucket 466:
bucket 467:
bucket 468:
bucket 469: *
bucket 470: *
bucket 471:
bucket 472: **
bucket 473: **
bucket 474:
bucket 475:
bucket 476:
bucket 477: *
bucket 478:
bucket 479: *
bucket 480: *
bucket 481: ***
bucket 482: **
bucket 483:
bucket 484:
bucket 485: **
bucket 486: **
bucket 487:
bucket 488: *
bucket 489: *
bucket 490:
bucket 491: **
bucket 492: *
bucket 493:
bucket 494:
bucket 495: *
bucket 496:
bucket 497:
bucket 498:
bucket 499:
bucket 500: ***
bucket 501:
bucket 502: *
bucket 503: *
bucket 504: *
bucket 505:
bucket 506: *
bucket 507:
bucket 508:
bucket 509:
bucket 510:
bucket 511:
在oracle8i中,oracle以一个很长的library cache hash table来记录library cache的使用情况
"*"代表该bucket中包含的对象的个数
在以上输出中我们看到bucket 198中包含四个对象.
我们在第三部分中可以找到bucket 198:
bucket 198:
library object handle: handle=2c2b4ac4
name=
select a.statement_id, a.timestamp, a.remarks, a.operation, a.options,
a.object_node, a.object_owner, a.object_name, a.object_instance,
a.object_type, a.optimizer, a.search_columns, a.id, a.parent_id,
a.position, a.cost, a.cardinality, a.bytes, a.other_tag,
a.partition_start, a.partition_stop, a.partition_id, a.other,
a.distribution
, rowid
from plan_table a
hash=60dd47a1 timestamp=08-27-2004 10:19:28
namespace=crsr flags=ron/tim/pn0/lrg/[10010001]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch=0
lwt=2c2b4adc[2c2b4adc,2c2b4adc] ltm=2c2b4ae4[2c2b4ae4,2c2b4ae4]
pwt=2c2b4af4[2c2b4af4,2c2b4af4] ptm=2c2b4b4c[2c2b4b4c,2c2b4b4c]
ref=2c2b4acc[2c2b4acc,2c2b4acc]
library object: object=2c0b1430
type=crsr flags=exs[0001] pflags= [00] status=vald load=0
children: size=16
child# table reference handle
------ -------- --------- --------
0 2c0b15ec 2c0b15b4 2c2c0d50
data blocks:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2c362290 2c0b14b4 i/-/a 0 none
library object handle: handle=2c3675d4
name=sys.dbms_standard
hash=50748ddb timestamp=null
namespace=body/tybd flags=tim/sml/[02000000]
kkkk-dddd-llll=0000-0011-0011 lock=0 pin=0 latch=0
lwt=2c3675ec[2c3675ec,2c3675ec] ltm=2c3675f4[2c3675f4,2c3675f4]
pwt=2c367604[2c367604,2c367604] ptm=2c36765c[2c36765c,2c36765c]
ref=2c3675dc[2c3675dc,2c3675dc]
library object: object=2c1528e8
flags=nex[0002] pflags= [00] status=vald load=0
data blocks:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2c367564 2c1529cc i/-/a 0 none
4 2c15297c 0 -/p/- 0 none
library object handle: handle=2c347dd8
name=select pos#,intcol#,col#,spare1 from icol$ where obj#=:1
hash=fa15ebe3 timestamp=07-28-2004 18:04:43
namespace=crsr flags=ron/tim/pn0/sml/[12010000]
kkkk-dddd-llll=0000-0001-0001 lock=0 pin=0 latch=0
lwt=2c347df0[2c347df0,2c347df0] ltm=2c347df8[2c347df8,2c347df8]
pwt=2c347e08[2c347e08,2c347e08] ptm=2c347e60[2c347e60,2c347e60]
ref=2c347de0[2c347de0,2c347de0]
library object: object=2c1cd1a0
type=crsr flags=exs[0001] pflags= [00] status=vald load=0
children: size=16
child# table reference handle
------ -------- --------- --------
0 2c1cd35c 2c1cd324 2c281678
1 2c1cd35c 2c352c50 2c0eeb8c
2 2c1cd35c 2c352c6c 2c2bb05c
data blocks:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2c2e8c58 2c1cd224 i/-/a 0 none
library object handle: handle=2c3a6484
name=sys.ts$
hash=bb42852e timestamp=04-24-2002 00:04:15
namespace=tabl/prcd/type flags=pkp/tim/kep/sml/[02900000]
kkkk-dddd-llll=0111-0111-0119 lock=0 pin=0 latch=0
lwt=2c3a649c[2c3a649c,2c3a649c] ltm=2c3a64a4[2c3a64a4,2c3a64a4]
pwt=2c3a64b4[2c3a64b4,2c3a64b4] ptm=2c3a650c[2c3a650c,2c3a650c]
ref=2c3a648c[2c0d4b14,2c09353c]
library object: object=2c3a626c
type=tabl flags=exs/loc[0005] pflags= [00] status=vald load=0
data blocks:
data# heap pointer status pins change
----- -------- -------- ------ ---- ------
0 2c3a8ea4 2c3a63b0 i/p/a 0 none
3 2c3a5828 0 -/p/- 0 none
4 2c3a6300 2c3a5960 i/p/a 0 none
8 2c3a6360 2c3a4f00 i/p/a 0 none
我们看到这里包含了四个对象.
我们再来看看oracle9i中的情况:
参考文件: hsjf_ora_15800.trc
library cache hash table: size=131072 count=217
buckets with more than 20 objects:
none
hash chain size number of buckets
--------------- -----------------
0 130855
1 217
2 0
3 0
4 0
5 0
6 0
7 0
8 0
9 0
10 0
11 0
12 0
13 0
14 0
15 0
16 0
17 0
18 0
19 0
20 0
>20 0
oracle9i中通过新的方式记录library cache的使用状况.
按不同的hash chain size代表library cache中包含不同对象的个数.
0表示free的bucket,>20表示包含超过20个对象的bucket的个数.
从以上列表中我们看到,包含一个对象的buckets有217个,包含0个对象的buckets有130855个.
我们来验证一下:
[[email protected] udump]$ cat hsjf_ora_15800.trc |grep bucket|more
bucket 12:
bucket 12 total object count=1
bucket 385:
bucket 385 total object count=1
bucket 865:
bucket 865 total object count=1
...
[[email protected] udump]$ cat hsjf_ora_15800.trc |grep bucket|wc -l
434
[[email protected] udump]$
434/2 = 217，证实了我们的猜想.
通过hash table算法的改进,oracle library cache管理的效率大大提高.
