仓酷云

标题: MYSQL网页编程之关于shared pool的深切切磋(三) [打印本页]

作者: 飘灵儿 时间: 2015-1-16 22:50
标题: MYSQL网页编程之关于shared pool的深切切磋(三)
对于现有业务，可以轻松移植到MySQL。当你需要替换掉老的硬件，当你需要削减历史遗留下的老系统的时候，选用MySQL对于财务部门来说更具吸引力。
link:

http://www.eygle.com/internal/shared_pool-3.htm

基础命令：
ALTERSESSIONSETEVENTSimmediatetracenameLIBRARY_CACHElevelLL;

个中LL代表Level级别,关于9.2.0及今后版本,分歧Level寄义以下:
Level=1,转储Librarycache统计信息
Level=2,转储hashtable提要
Level=4,转储Librarycache工具，只包括基础信息
Level=8,转储Librarycache工具，包括具体信息(包含childreferences,pinwaiters等)
Level=16,增添heapsizes信息
Level=32,增添heap信息

Librarycache由一个hash表构成,而hash表是一个由hashbuckets构成的数组.

每一个hashbucket都是包括librarycachehandle的一个双向链表。
LibraryCacheHandle指向LibraryCacheObject和一个援用列表.
librarycache工具进一步分为:依附表、子表和受权表等

我们看一下librarycache的布局:

经由过程
ALTERSESSIONSETEVENTSimmediatetracenameLIBRARY_CACHElevel4
取得以下输入(这部分信息来自Oracle8i,Trace文件能够从www.eygle.com上找到)
点击这里下载:hsbi_ora_4614.trc
第一部分(等价于Level1):

LIBRARYCACHESTATISTICS:
getshitratiopinshitratioreloadsinvalidsnamespace
-------------------------------------------------------------------
6196581710.999916021932921120.99995119404380CRSR
796985580.99988324246148470.9999108135890TABL/PRCD/TYPE
1633990.99799261634020.9978948160BODY/TYBD
00.000000000.000000000TRGR
340.0294118350.057142900INDX
189480.9968862244880.995385500CLST
00.000000000.000000000OBJE
00.000000000.000000000PIPE
00.000000000.000000000LOB
00.000000000.000000000DIR
00.000000000.000000000QUEU
00.000000000.000000000OBJG
00.000000000.000000000PROP
00.000000000.000000000JVSC
00.000000000.000000000JVRE
00.000000000.000000000ROBJ
00.000000000.000000000REIP
00.000000000.000000000CPOB
1150710.99921791150710.99309997040EVNT
00.000000000.000000000SUMM
00.000000000.000000000DIMN
00.000000000.000000000CTX
00.000000000.000000000OUTL
00.000000000.000000000RULS
00.000000000.000000000RMGR
00.000000000.000000000UNUSED
00.000000000.000000000PPLN
00.000000000.000000000PCLS
00.000000000.000000000SUBS
00.000000000.000000000LOCS
00.000000000.000000000RMOB
00.000000000.000000000RSMD
6996541810.999911726182099550.999944023713380CUMULATIVE

这部分信息也就是v$librarycache中显现的.

第二部分(等价于Level2中的输入):

LIBRARYCACHEHASHTABLE:size=509count=354
BUCKET0:
BUCKET1:
BUCKET2:*
BUCKET3:
BUCKET4:
BUCKET5:*
BUCKET6:*
BUCKET7:
BUCKET8:**
BUCKET9:***
BUCKET10:*
BUCKET11:*
BUCKET12:***
BUCKET13:*
BUCKET14:*
BUCKET15:
BUCKET16:*
BUCKET17:
BUCKET18:*
BUCKET19:
BUCKET20:
BUCKET21:*
BUCKET22:
BUCKET23:
BUCKET24:*
BUCKET25:
BUCKET26:
BUCKET27:***
BUCKET28:
BUCKET29:**
BUCKET30:
BUCKET31:
BUCKET32:***
BUCKET33:*
BUCKET34:
BUCKET35:
BUCKET36:**
BUCKET37:
BUCKET38:**
BUCKET39:*
BUCKET40:*
BUCKET41:
BUCKET42:
BUCKET43:
BUCKET44:
BUCKET45:
BUCKET46:****
BUCKET47:
BUCKET48:
BUCKET49:*
BUCKET50:*
BUCKET51:
BUCKET52:***
BUCKET53:**
BUCKET54:
BUCKET55:*
BUCKET56:
BUCKET57:
BUCKET58:
BUCKET59:*
BUCKET60:**
BUCKET61:
BUCKET62:*
BUCKET63:
BUCKET64:*
BUCKET65:
BUCKET66:
BUCKET67:*
BUCKET68:
BUCKET69:**
BUCKET70:
BUCKET71:
BUCKET72:*
BUCKET73:
BUCKET74:
BUCKET75:*
BUCKET76:**
BUCKET77:
BUCKET78:****
BUCKET79:
BUCKET80:*
BUCKET81:*
BUCKET82:
BUCKET83:**
BUCKET84:*
BUCKET85:
BUCKET86:
BUCKET87:
BUCKET88:
BUCKET89:*
BUCKET90:*
BUCKET91:
BUCKET92:*
BUCKET93:*
BUCKET94:*
BUCKET95:
BUCKET96:*
BUCKET97:
BUCKET98:
BUCKET99:***
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:

在Oracle8i中,Oracle以一个很长的LIBRARYCACHEHASHTABLE来纪录LibraryCache的利用情形
"*"代表该Bucket中包括的工具的个数

在以上输入中我们看到Bucket198中包括四个工具.

我们在第三部分中能够找到bucket198:

BUCKET198:
LIBRARYOBJECTHANDLE:handle=2c2b4ac4
name=
SELECTa.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
FROMplan_tablea
hash=60dd47a1timestamp=08-27-200410:19:28
namespace=CRSRflags=RON/TIM/PN0/LRG/[10010001]
kkkk-dddd-llll=0000-0001-0001lock=0pin=0latch=0
lwt=2c2b4adc[2c2b4adc,2c2b4adc]ltm=2c2b4ae4[2c2b4ae4,2c2b4ae4]
pwt=2c2b4af4[2c2b4af4,2c2b4af4]ptm=2c2b4b4c[2c2b4b4c,2c2b4b4c]
ref=2c2b4acc[2c2b4acc,2c2b4acc]
LIBRARYOBJECT:object=2c0b1430
type=CRSRflags=EXS[0001]pflags=[00]status=VALDload=0
CHILDREN:size=16
child#tablereferencehandle
-------------------------------
02c0b15ec2c0b15b42c2c0d50
DATABLOCKS:
data#heappointerstatuspinschange
-------------------------------------
02c3622902c0b14b4I/-/A0NONE
LIBRARYOBJECTHANDLE:handle=2c3675d4
name=SYS.DBMS_STANDARD
hash=50748ddbtimestamp=NULL
namespace=BODY/TYBDflags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011lock=0pin=0latch=0
lwt=2c3675ec[2c3675ec,2c3675ec]ltm=2c3675f4[2c3675f4,2c3675f4]
pwt=2c367604[2c367604,2c367604]ptm=2c36765c[2c36765c,2c36765c]
ref=2c3675dc[2c3675dc,2c3675dc]
LIBRARYOBJECT:object=2c1528e8
flags=NEX[0002]pflags=[00]status=VALDload=0
DATABLOCKS:
data#heappointerstatuspinschange
-------------------------------------
02c3675642c1529ccI/-/A0NONE
42c15297c0-/P/-0NONE
LIBRARYOBJECTHANDLE:handle=2c347dd8
name=selectpos#,intcol#,col#,spare1fromicol$whereobj#=:1
hash=fa15ebe3timestamp=07-28-200418:04:43
namespace=CRSRflags=RON/TIM/PN0/SML/[12010000]
kkkk-dddd-llll=0000-0001-0001lock=0pin=0latch=0
lwt=2c347df0[2c347df0,2c347df0]ltm=2c347df8[2c347df8,2c347df8]
pwt=2c347e08[2c347e08,2c347e08]ptm=2c347e60[2c347e60,2c347e60]
ref=2c347de0[2c347de0,2c347de0]
LIBRARYOBJECT:object=2c1cd1a0
type=CRSRflags=EXS[0001]pflags=[00]status=VALDload=0
CHILDREN:size=16
child#tablereferencehandle
-------------------------------
02c1cd35c2c1cd3242c281678
12c1cd35c2c352c502c0eeb8c
22c1cd35c2c352c6c2c2bb05c
DATABLOCKS:
data#heappointerstatuspinschange
-------------------------------------
02c2e8c582c1cd224I/-/A0NONE
LIBRARYOBJECTHANDLE:handle=2c3a6484
name=SYS.TS$
hash=bb42852etimestamp=04-24-200200:04:15
namespace=TABL/PRCD/TYPEflags=PKP/TIM/KEP/SML/[02900000]
kkkk-dddd-llll=0111-0111-0119lock=0pin=0latch=0
lwt=2c3a649c[2c3a649c,2c3a649c]ltm=2c3a64a4[2c3a64a4,2c3a64a4]
pwt=2c3a64b4[2c3a64b4,2c3a64b4]ptm=2c3a650c[2c3a650c,2c3a650c]
ref=2c3a648c[2c0d4b14,2c09353c]
LIBRARYOBJECT:object=2c3a626c
type=TABLflags=EXS/LOC[0005]pflags=[00]status=VALDload=0
DATABLOCKS:
data#heappointerstatuspinschange
-------------------------------------
02c3a8ea42c3a63b0I/P/A0NONE
32c3a58280-/P/-0NONE
42c3a63002c3a5960I/P/A0NONE
82c3a63602c3a4f00I/P/A0NONE

我们看到这里包括了四个工具.

我们再来看看Oracle9i中的情形:

参考文件:hsjf_ora_15800.trc

LIBRARYCACHEHASHTABLE:size=131072count=217
Bucketswithmorethan20objects:
NONE
HashChainSizeNumberofBuckets
--------------------------------
0130855
1217
20
30
40
50
60
70
80
90
100
110
120
130
140
150
160
170
180
190
200
>200

Oracle9i中经由过程新的体例纪录LibraryCache的利用情况.
按分歧的HashChainSize代表LibraryCache中包括分歧工具的个数.
0暗示Free的Bucket,>20暗示包括凌驾20个工具的Bucket的个数.

从以上列表中我们看到,包括一个工具的Buckets有217个,包括0个工具的Buckets有130855个.

我们来考证一下:

[oracle@jumperudump]$cathsjf_ora_15800.trc|grepBUCKET|more
BUCKET12:
BUCKET12totalobjectcount=1
BUCKET385:
BUCKET385totalobjectcount=1
BUCKET865:
BUCKET865totalobjectcount=1
...
[oracle@jumperudump]$cathsjf_ora_15800.trc|grepBUCKET|wc-l
434
[oracle@jumperudump]$

434/2=217，证明了我们的料想.

经由过程HASHTABLE算法的改善,OracleLibraryCache办理的效力年夜年夜进步.
下面我将描述五个不使用MySQL的响亮理由。

作者: 简单生活 时间: 2015-1-17 23:16
记得在最开始使用2k的时候就要用到这个功能，可惜2k没有，现在有了作解决方案的朋友会很高兴吧。

作者: 深爱那片海 时间: 2015-1-21 10:23
你可以简单地认为适合的就是好，不适合就是不好。

作者: 乐观 时间: 2015-1-30 15:01
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

作者: 再现理想 时间: 2015-2-6 13:38
可以动态传入参数，省却了动态SQL的拼写。

作者: 柔情似水 时间: 2015-2-16 07:26
发几份SQL课件，以飨阅者

作者: 只想知道 时间: 2015-3-5 01:38
理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

作者: admin 时间: 2015-3-11 22:07
多加的系统视图和实时系统信息这些东西对DBA挑优非常有帮助，但是感觉粒度还是不太细。

作者: 不帅 时间: 2015-3-19 15:05
一个是把SQL语句写到客户端,可以使用DataSet进行加工;

作者: 冷月葬花魂 时间: 2015-3-28 16:02
连做梦都在想页面结构是怎么样的，绝非虚言

欢迎光临仓酷云 (http://ckuyun.com/)