An in-depth discussion on shared pool (III.)

Link

Http://www.eygle.com/internal/shared_pool-3.htm




Basic commands:
ALTER session SET EVENTS ' immediate trace name Library_cache level LL ';

where ll represents level levels, for 9.2.0 and later versions, different level meanings are as follows:
Level = 1, dump library cache statistics
Level = 2, dump hash Table overview
Level = 4, dump library cache object, contains only basic information
Level = 8, Dump library cache object, containing details (including child references,pin waiters, etc.)
Level = 16, increase heap sizes information
Level = 32, increase heap information

The Library cache consists of a hash table, and a hash table is an array of hash buckets.

Each hash bucket is a two-way list containing the library cache handle.
The library cache handle points to the library cache object and a list of references.
Library Cache object is further divided into: dependent table, child table and authorization table, etc.

Let's look at the structure of the library cache:

Pass
ALTER session SET EVENTS ' immediate trace name Library_cache Level 4 '
Obtain the following output (this part of the information from the Oracle8i,trace file can be found from the 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
79698558 0.9998832 424614847 0.9999108 13589 0 tabl/prcd/type
163399 0.9979926 163402 0.9978948 0 body/tybd
0 0.0000000 0 0.0000000 0 0 trgr
0.0294118 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

This part of the information is also shown in V$librarycache.

Part Two (equivalent to the output in 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:


In Oracle8i, Oracle uses a long library cache HASH table to record the use of the library cache
"*" represents the number of objects contained in the bucket

In the above output we see that bucket 198 contains four objects.

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.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= [] 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= [] 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= [] 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= [] 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 there are four objects here.

Let's take a look at the situation in Oracle9i:

Reference file: HSJF_ORA_15800.TRC


LIBRARY CACHE HASH table:size=131072 count=217
Buckets with the more than 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 in the new way to record the use of library cache.
A different hash Chain size represents the number of different objects contained in the library cache.
0 Bucket,>20 representing free represents the number of bucket that contain more than 20 objects.

As we can see from the list above, there are 217 buckets containing one object, and 130,855 buckets containing 0 objects.

Let's verify:


[Oracle@jumper udump]$ cat hsjf_ora_15800.trc |grep Bucket|more
BUCKET 12:
BUCKET 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 confirms our conjecture.

With the improvement of hash table algorithm, the efficiency of Oracle Library cache management is greatly improved.