基本命令:
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个.
我们来验证一下:
[oracle@jumper 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 ...
[oracle@jumper udump]$ cat hsjf_ora_15800.trc |grep BUCKET|wc -l
434
[oracle@jumper udump]$
|
434/2 = 217,证实了我们的猜想.
通过HASH TABLE算法的改进,Oracle Library Cache管理的效率大大提高.