oracle shared pool深入讨论(三)

最新推荐文章于 2024-11-17 23:07:03 发布

cmp96226

最新推荐文章于 2024-11-17 23:07:03 发布

阅读量98

点赞数

文章标签：数据库数据结构与算法

oracle 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'
获得以下输出详见附件:

文件:	hsbi_ora_4614.zip
大小:	77KB
下载:	下载

第一部分(等价于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.zip
大小:	46KB
下载:	下载

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/548588/viewspace-981225/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/548588/viewspace-981225/