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

    参考文件: 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管理的效率大大提高.

  • 相关阅读:
    Linux和windows 查看程序、进程的依赖库的方法
    ida pro 使用
    sql server相关
    nginx 日志log_format格式
    .yml文件格式
    svn加锁设置
    包含目录、库目录、附加包含目录、附加库目录、附加依赖项之详解
    visual studio 2017 (vs2017安装)
    OpenResty编译安装
    微信扫描二维码登录网站技术原理
  • 原文地址:https://www.cnblogs.com/youngerger/p/8538548.html
Copyright © 2020-2023  润新知