Link: http://www.eygle.com/internal/shared_pool-3.htm
Basic command:
Alter Session Set Events 'Immediate Trace Name Library_Cache Level Ll';
Where LL represents the Level level, for 9.2.0 and later, different levels are as follows: Level = 1, dump library cache statistics level = 2, dump hash table profile = 4, dump library cache object, only contain Basic information Level = 8, dump the library cache object, including details (including Child References, Pin Waiters, etc.) level = 16, increase the HEAP Sizes information level = 32, increase HEAP information
Library Cache consists of a Hash table, and the Hash table is an array consisting of Hash Buckets.
Each hash bucket is a two-way linked list containing library cache handsle. Library Cache Handle points to Library Cache Object and a reference list .Library Cache object is further divided into: dependent table, sub-table and authorization form, etc.
Let's take a look at the structure of Library Cache:
Get the following output by Alter Session Name Library_Cache Level 4 '(this part of the information is from Oracle8i, the trace file can be found from www.eygle.com) Click here to download:
HSBI_ORA_4614.TRC
The first part (equivalent to 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.0000000 0 0 RMGR 0 0.0000000 0 0.0000000 0 0 000000 0 0 0 PPLN 0 0.0000000 0 0.00000 0 0.0000000 0 0.0000000 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.999911724181 0.999117 2618209955 0.999440 23713 380 Cumulative This part of the information is displayed in V $ librarycache. The second part (equivalent to the output in Level 2):
Library Cache Hash Table: Size = 509 count =
354Bucket 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 15: Bucket 16: * Bucket 17: Bucket 18: * Bucket 19: Bucket 20: Bucket 21: * Bucket 22: Bucket 23: Bucket 24: * Bucket 25: BUCKET 26: BUCKET 26: BUCKET 26: 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 59: 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 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 103: Bucket 104: * Bucket 105: Bucket 106: Bucket 107: **** Bucket 108: Bucket 109: Bucket 110: BUCKET 111: * BUCKET 112: ** Bucket 113: Bucket 114: Bucket 116: * Bucket 117: Bucket 118: ***** Bucket 119: Bucket 120: * Bucket 121: BUCKET 122: BUCKET 123: BUCKET 124: BUCKET 125: * Bucket 126:
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 145: *** Bucket 146: Bucket 147: * Bucket 148: Bucket 149: Bucket 150: ** Bucket 151: Bucket 152: Bucket 153: * Bucket 154: bucket 155: 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 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 187: BUCKET 187: BUCKET 188: 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 204: Bucket 205: ** Bucket 206: Bucket 207: Bucket 208: * Bucket 209: ** Bucket 210: BUCKET 211: * Bucket 212: * Bucket 213: * 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 243: *** Bucket 243: *** Bucket 243: Bucket 245: * Bucket 246: BUCKET 247: BUCKET 248: ** Bucket 249: Bucket 250: Bucket 251: ** Bucket 252: Bucket 253: * BUCKET 254:
* Bucket 255: 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 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 297: Bucket 298: Bucket 299: ** Bucket 300: * Bucket 301: BUCKET 302: * Bucket 303: * Bucket 304: ** Bucket 305: ** Bucket 306: BUCKET 307: Bucket 308: 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 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 354: * Bucket 355: ** Bucket 356: Bucket 357: Bucket 358: ** Bucket 359: * Bucket 360: * Bucket 361: ** Bucket 362: Bucket 363: BUCKET 364: * BUCKET 365: * 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 383: ** Bucket 384: Bucket 385: Bucket 387: *** Bucket 388: * Bucket 389: Bucket 390: Bucket 391: Bucket 392: BUCKET 393: * Bucket 394: * Bucket 395: * Bucket 396: Bucket 398: Bucket 399: 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 413: 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 440: Bucket 441: 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 4 55: BUCKET 457: BUCKET 457: * Bucket 459: ** Bucket 459: 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 484: Bucket 485: ** bucket 486: ** bucket 487: bucket 488: * bucket 489: * bucket 490: bucket 491: ** bucket 492: * bucket 493: Bucket 494: Bucket 495: 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: In Oracle8i, Oracle uses a long library cache hash table to record the number of use of library cache "*" on behalf of the object included in the bucket.
In the above output we see four objects in BUCKET 198.
We can find Bucket 198 in the third part:
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.cardinal, a.bytes, afher_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 childle ------ ---- ---- ---------- -------- 0 2C0B15EC 2C0B15B4 2C2C0D50 DATA BL Ocks: 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 loading = 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 We see that four objects are included.
Let's take a look at the situation in Oracle9i:
Reference file: HSJF_ORA_15800.TRC
Library Cache Hash Table: Size = 131072 Count = 217buckets with More 20 Objects: Nonehash 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> 20 0
The use of library cache is recorded in the new way. Press different Hash CHAIN SIZE to represent the number of different objects in Library Cache. 0 indicates the number of BUCKETs that contain more than 20 objects.
From the above list, we see that there is 217 buckets containing an object, including 130,855 BUCKETs containing 0 objects.
Let's verify:
[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, confirmed our conjecture.
ORCLE LIBRARY CACHE management is greatly improved by the improvement of the Hash Table algorithm.
