oracle11 share pool,关于shared pool的深入探讨(三)

基本命令：

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中的情况:

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管理的效率大大提高.