eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« 关于shared pool的深入探讨(四) | Blog首页 | 关于shared pool的深入探讨(二) »

关于shared pool的深入探讨(三)
modb.pro

作者：eygle | 【转载请注】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接：
基本命令：

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

 

 

<<上一页 下一页>>


历史上的今天...
    >> 2021-10-31文章:
    >> 2012-10-31文章:
    >> 2011-10-31文章:
    >> 2010-10-31文章:
    >> 2008-10-31文章:
    >> 2007-10-31文章:
    >> 2006-10-31文章:
    >> 2005-10-31文章:

By eygle on 2004-10-31 09:24 | Comments (0) | Internal | 83 |

CopyRight © 2004~2020 云和恩墨，成就未来！, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话：400-600-8755 业务咨询：010-59007017-7040 or 7037 业务合作: marketing@enmotech.com