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

显示全部楼层 · 2005-2-28 12:57:00

基本命令：
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]
LIBRARY CACHE STATISTICS:
gets hit ratio pins hit ratioreloads invalids namespace
---------- --------- ---------- --------- ---------- ---------- ---------
619658171 0.9999160 2193292112 0.9999511 9404380 CRSR
79698558 0.9998832424614847 0.999910813589
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.996886224488 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.9930999704
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.999944023713380 CUMULATIVE.
[/php]
这部分信息也就是v$librarycache中显示的统计信息.

千问 · 2005-2-28 12:57:00

第二部分(等价于Level 2中的输出):
[php]
LIBRARY CACHE HASH TABLE: size=509 count=354
BUCKET0:
BUCKET1:
BUCKET2: *
BUCKET3:
BUCKET4:
BUCKET5: *
BUCKET6: *
BUCKET7:
BUCKET8: **
BUCKET9: ***
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: ***
BUCKET100: *
BUCKET101:
BUCKET102: *
BUCKET103:
BUCKET104: *
BUCKET105:
BUCKET106:
BUCKET107: ****
BUCKET108:
BUCKET109:
BUCKET110:
BUCKET111: *
BUCKET112: **
BUCKET113:
BUCKET114:
BUCKET115:
BUCKET116: *
BUCKET117:
BUCKET118: *****
BUCKET119:
BUCKET120: *
BUCKET121:
BUCKET122:
BUCKET123:
BUCKET124:
BUCKET125: *
BUCKET126:
BUCKET127:
BUCKET128: *
BUCKET129:
BUCKET130: *
BUCKET131: *
BUCKET132:
BUCKET133:
BUCKET134:
BUCKET135: *
BUCKET136:
BUCKET137:
BUCKET138:
BUCKET139: *
BUCKET140: *
BUCKET141: *
BUCKET142:
BUCKET143: *
BUCKET144:
BUCKET145: ***
BUCKET146:
BUCKET147: *
BUCKET148:
BUCKET149:
BUCKET150: **
BUCKET151:
BUCKET152:
BUCKET153: *
BUCKET154:
BUCKET155:
BUCKET156:
BUCKET157:
BUCKET158:
BUCKET159:
BUCKET160:
BUCKET161:
BUCKET162:
BUCKET163:
BUCKET164: *
BUCKET165: *
BUCKET166:
BUCKET167:
BUCKET168:
BUCKET169:
BUCKET170: **
BUCKET171:
BUCKET172: *
BUCKET173:
BUCKET174:
BUCKET175: *
BUCKET176: *
BUCKET177:
BUCKET178:
BUCKET179:
BUCKET180:
BUCKET181: *
BUCKET182:
BUCKET183:
BUCKET184:
BUCKET185: *
BUCKET186:
BUCKET187:
BUCKET188: **
BUCKET189:
BUCKET190: *
BUCKET191: *
BUCKET192:
BUCKET193:
BUCKET194: *
BUCKET195: **
BUCKET196: *
BUCKET197: **
BUCKET198: ****
BUCKET199: *
BUCKET200: *
BUCKET201: *
BUCKET202: **
BUCKET203:
BUCKET204:
BUCKET205: **
BUCKET206:
BUCKET207:
BUCKET208: *
BUCKET209: **
BUCKET210:
BUCKET211: *
BUCKET212: *
BUCKET213: *
BUCKET214:
BUCKET215:
BUCKET216:
BUCKET217: *
BUCKET218: *
BUCKET219:
BUCKET220:
BUCKET221: *
BUCKET222:
BUCKET223: *
BUCKET224:
BUCKET225:
BUCKET226: *
BUCKET227:
BUCKET228: *
BUCKET229: **
BUCKET230: *
BUCKET231:
BUCKET232: **
BUCKET233:
BUCKET234: *
BUCKET235: *
BUCKET236:
BUCKET237:
BUCKET238: *
BUCKET239:
BUCKET240: **
BUCKET241: **
BUCKET242: **
BUCKET243: ***
BUCKET244:
BUCKET245: *
BUCKET246:
BUCKET247:
BUCKET248: **
BUCKET249:
BUCKET250:
BUCKET251: **
BUCKET252:
BUCKET253: *
BUCKET254: *
BUCKET255:
BUCKET256:
BUCKET257: **
BUCKET258: *
BUCKET259:
BUCKET260:
BUCKET261: *
BUCKET262: **
BUCKET263: ***
BUCKET264:
BUCKET265: *
BUCKET266:
BUCKET267: *
BUCKET268: *
BUCKET269:
BUCKET270:
BUCKET271: **
BUCKET272: *
BUCKET273:
BUCKET274: *
BUCKET275: *
BUCKET276: **
BUCKET277:
BUCKET278:
BUCKET279:
BUCKET280:
BUCKET281: **
BUCKET282: *
BUCKET283: *
BUCKET284: *
BUCKET285: *
BUCKET286:
BUCKET287: *
BUCKET288:
BUCKET289:
BUCKET290: **
BUCKET291:
BUCKET292: *
BUCKET293:
BUCKET294: *
BUCKET295:
BUCKET296: *
BUCKET297:
BUCKET298:
BUCKET299: **
BUCKET300: *
BUCKET301:
BUCKET302: *
BUCKET303: *
BUCKET304: **
BUCKET305: **
BUCKET306:
BUCKET307:
BUCKET308: *
BUCKET309:
BUCKET310:
BUCKET311: **
BUCKET312: *
BUCKET313:
BUCKET314: *
BUCKET315:
BUCKET316:
BUCKET317:
BUCKET318:
BUCKET319: ***
BUCKET320: *
BUCKET321: **
BUCKET322: **
BUCKET323:
BUCKET324: *
BUCKET325:
BUCKET326: *
BUCKET327: *
BUCKET328: **
BUCKET329:
BUCKET330: *
BUCKET331:
BUCKET332:
BUCKET333: *
BUCKET334: *
BUCKET335: ***
BUCKET336: *
BUCKET337: **
BUCKET338: *
BUCKET339: *
BUCKET340:
BUCKET341: *
BUCKET342: *
BUCKET343: **
BUCKET344:
BUCKET345:
BUCKET346:
BUCKET347: *
BUCKET348:
BUCKET349: ***
BUCKET350: *
BUCKET351:
BUCKET352:
BUCKET353:
BUCKET354: *
BUCKET355: **
BUCKET356:
BUCKET357:
BUCKET358: **
BUCKET359: *
BUCKET360: *
BUCKET361: **
BUCKET362:
BUCKET363:
BUCKET364: *
BUCKET365: *
BUCKET366: **
BUCKET367: *
BUCKET368:
BUCKET369: *
BUCKET370:
BUCKET371: ***
BUCKET372:
BUCKET373: *
BUCKET374:
BUCKET375:
BUCKET376: *
BUCKET377:
BUCKET378:
BUCKET379:
BUCKET380:
BUCKET381:
BUCKET382:
BUCKET383: **
BUCKET384:
BUCKET385:
BUCKET386:
BUCKET387: ***
BUCKET388: *
BUCKET389:
BUCKET390:
BUCKET391:
BUCKET392:
BUCKET393: *
BUCKET394: *
BUCKET395: *
BUCKET396:
BUCKET397:
BUCKET398:
BUCKET399:
BUCKET400: **
BUCKET401:
BUCKET402:
BUCKET403:
BUCKET404:
BUCKET405:
BUCKET406:
BUCKET407: *
BUCKET408: *
BUCKET409: *
BUCKET410:
BUCKET411: *
BUCKET412:
BUCKET413:
BUCKET414:
BUCKET415:
BUCKET416: *
BUCKET417:
BUCKET418: *
BUCKET419:
BUCKET420: **
BUCKET421: *
BUCKET422:
BUCKET423: **
BUCKET424: ***
BUCKET425:
BUCKET426: *
BUCKET427: *
BUCKET428: **
BUCKET429:
BUCKET430:
BUCKET431:
BUCKET432:
BUCKET433: *
BUCKET434:
BUCKET435: **
BUCKET436: *
BUCKET437: *
BUCKET438:
BUCKET439: *
BUCKET440:
BUCKET441:
BUCKET442:
BUCKET443: *
BUCKET444:
BUCKET445: *
BUCKET446:
BUCKET447: *
BUCKET448:
BUCKET449: *
BUCKET450:
BUCKET451:
BUCKET452: *
BUCKET453: *
BUCKET454: *
BUCKET455:
BUCKET456:
BUCKET457:
BUCKET458: *
BUCKET459: **
BUCKET460:
BUCKET461: **
BUCKET462: *
BUCKET463:
BUCKET464: *
BUCKET465: *
BUCKET466:
BUCKET467:
BUCKET468:
BUCKET469: *
BUCKET470: *
BUCKET471:
BUCKET472: **
BUCKET473: **
BUCKET474:
BUCKET475:
BUCKET476:
BUCKET477: *
BUCKET478:
BUCKET479: *
BUCKET480: *
BUCKET481: ***
BUCKET482: **
BUCKET483:
BUCKET484:
BUCKET485: **
BUCKET486: **
BUCKET487:
BUCKET488: *
BUCKET489: *
BUCKET490:
BUCKET491: **
BUCKET492: *
BUCKET493:
BUCKET494:
BUCKET495: *
BUCKET496:
BUCKET497:
BUCKET498:
BUCKET499:
BUCKET500: ***
BUCKET501:
BUCKET502: *
BUCKET503: *
BUCKET504: *
BUCKET505:
BUCKET506: *
BUCKET507:
BUCKET508:
BUCKET509:
BUCKET510:
BUCKET511:
.
[/php]
在Oracle8i中,Oracle以一个很长的LIBRARY CACHE HASH TABLE来记录Library Cache的使用情况
"*"代表该Bucket中包含的对象的个数
在Oracle9i之前,初始的,Oracle会分配一定数量的Bucket,在达到一定条件时,Bucket会增加,也就是Hash Table会变得月来越长.
在Bucket增加时,Hash Table会重组,这时会产生shared pool的打嗝(hiccup)现象,通常3~5秒

千问 · 2005-2-28 12:57:00

在以上输出中我们看到Bucket 198中包含四个对象.
我们在第三部分中可以找到bucket 198:
[php]
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 2c0b15ec2c0b15b4 2c2c0d50
DATA BLOCKS:
data# heappointer 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# heappointer status pins change
----- -------- -------- ------ ---- ------
0 2c367564 2c1529cc I/-/A 0 NONE
4 2c15297c0 -/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 2c1cd35c2c1cd324 2c281678
1 2c1cd35c2c352c50 2c0eeb8c
2 2c1cd35c2c352c6c 2c2bb05c
DATA BLOCKS:
data# heappointer 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# heappointer status pins change
----- -------- -------- ------ ---- ------
0 2c3a8ea4 2c3a63b0 I/P/A 0 NONE
3 2c3a58280 -/P/- 0 NONE
4 2c3a6300 2c3a5960 I/P/A 0 NONE
8 2c3a6360 2c3a4f00 I/P/A 0 NONE
.
[/php]
我们看到这里的确包含了四个对象.

千问 · 2005-2-28 12:57:00

我们再来看看Oracle9i中的情况:
参考文件: hsjf_ora_15800.trc
[php]
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
.
[/php]
Oracle9i中通过新的方式记录Library Cache的使用状况.
按不同的Hash Chain Size代表Library Cache中包含不同对象的个数.
0表示Free的Bucket,>20表示包含超过20个对象的Bucket的个数.
从以上列表中我们看到,包含一个对象的Buckets有217个,包含0个对象的Buckets有130855个.
我们来验证一下:
[php]
[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]$
[/php]
434/2 = 217，证实了我们的猜想.
通过HASH TABLE算法的改进,Oracle Library Cache管理的效率得以提高.

千问 · 2005-2-28 12:57:00

千问 · 2005-2-28 12:57:00

good!继续学习中...

千问 · 2005-2-28 12:57:00

都不明白斑竹sql的意思，

千问 · 2005-2-28 12:57:00

eygle大师的文章总是这么深入