[转帖]关于shared pool的深入探讨(三)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 4742 | 回复: 0

主题： [转帖]关于shared pool的深入探讨(三)

kim

注册用户

等级：中校
经验：1729
发帖：222
精华：0
注册：2011-7-21
状态：离线
发送短消息息给kim

加好友发送短消息息给kim

发消息

发表于：

2011-8-15 10:52:49 | [全部帖] [楼主帖]

楼主

关于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 Object进一步分为:依赖表、子表和授权表等

我们看一下library cache的结构:

通过

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 4'

获得以下输出(这部分信息来自Oracle8i,Trace文件可以从
www.eygle.com上找到)

点击这里下载: hsbi_ora_4614.trc

第一部分(等价于Level 1):

PHP code:

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中的输出):

PHP code:

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中包含的对象的个数

在Oracle9i之前,初始的,Oracle会分配一定数量的Bucket,在达到一定条件时,Bucket会增加,也就是Hash Table会变得月来越长.

在Bucket增加时,Hash Table会重组,这时会产生shared pool的打嗝(hiccup)现象,通常3~5秒

在以上输出中我们看到Bucket 198中包含四个对象.

我们在第三部分中可以找到bucket 198:

PHP code:

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
PHP code:



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个.

我们来验证一下:

PHP code:

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

本版精华
热门帖子

操作引用/回复

总帖数

每页帖数

1/1页

返回列表

用户登录

Weblogic中间件技术论坛

Tuxedo中间件技术论坛

数据库论坛

Java论坛

Linux/unix论坛

网站地图