MSSQL网站制作之关于shared pool的深切切磋(一)

有了rowbase的binlog后，我们来分析一下怎么实现闪回。平时的DML无非三种操作，增删改，先说三种操作的日志格式。
关于sharedpool的深切切磋(一)

link:

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

关于sharedpool的设置一向是一个争议较多的内容.
良多文章上说,sharedpool设置过年夜会带来分外的办理上的包袱，从而在某些前提下会招致功能的下落.

那末这个办理上的包袱指的是甚么内容呢?
本文对这个内容作必定的深切切磋.
本文只触及一个方面,后续的文章将从其他方面持续会商.

基本常识:

我们能够经由过程以下命令转储sharedpool共享内存的内容:

SQL>altersessionseteventsimmediatetracenameheapdumplevel2;

Sessionaltered.

本测试中援用的两个trace文件:

9i:

SQL>@gettrcname

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/hsjf/udump/hsjf_ora_24983.trc

8i:

SQL>@gettrcname

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/usr/oracle8/admin/guess/udump/guess_ora_22038.trc

SharedPool经由过程freelist办理free块,FreeList按分歧size分别Bucket
在Oracle8i中,分歧bucket的size局限以下所示(size显现的是下界限):

oracle:/usr/oracle8/admin/guess/udump>catguess_ora_22038.trc|grepBucket
Bucket0size=44
Bucket1size=76
Bucket2size=140
Bucket3size=268
Bucket4size=524
Bucket5size=1036
Bucket6size=2060
Bucket7size=4108
Bucket8size=8204
Bucket9size=16396
Bucket10size=32780


我们注重,在这里，小于76的块都位于Bucket0上;年夜于32780的块,都在Bucket10上
初始的,数据库启动今后,sharedpool多半是一连内存块
当空间分派利用今后,内存块入手下手被支解,碎片入手下手呈现，Bucket列表入手下手变长

Oracle哀求sharedpool空间时，起首进进响应的Bucket举行查找
假如找不到,则转向下一个非空的bucket,猎取第一个chunk
支解这个chunk,残剩部分会进进响应的Bucket,进一步增添碎片

终极的了局是,Bucket0上的内存块会愈来愈多,愈来愈碎小
(在我这个测试的小型的数据库上,Bucket0上的碎片已到达9030个
而shared_pool_size设置仅为150M)
一般假如每一个Bucket上的chunk过剩2000个，就被以为是sharepool碎片过量

而在年夜多半情形下,我们哀求的都是绝对小的chunk,如许搜刮Bucket0常常损耗了大批的工夫和资本
这大概招致sharepoolLatch被长工夫的持有,招致更多的sharepool合作

以是在Oracle9i之前,假如自觉的增年夜shared_pool_size或设置过年夜的shared_pool_size，常常会拔苗助长


我们看一下Oracle9i中的处置体例:

[oracle@jumperoracle]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-ProductiononWedAug1822:13:072004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


Connectedto:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>altersessionseteventsimmediatetracenameheapdumplevel2;

Sessionaltered.

SQL>@gettrcname

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/opt/oracle/admin/hsjf/udump/hsjf_ora_24983.trc

SQL>
SQL>!
[oracle@jumperoracle]$cd$admin
[oracle@jumperudump]$cathsjf_ora_24983.trc|grepBucket
Bucket0size=16
Bucket1size=20
Bucket2size=24
Bucket3size=28
Bucket4size=32
Bucket5size=36
Bucket6size=40
Bucket7size=44
Bucket8size=48
Bucket9size=52
Bucket10size=56
Bucket11size=60
Bucket12size=64
Bucket13size=68
Bucket14size=72
Bucket15size=76
Bucket16size=80
Bucket17size=84
Bucket18size=88
Bucket19size=92
Bucket20size=96
Bucket21size=100
Bucket22size=104
Bucket23size=108
Bucket24size=112
Bucket25size=116
Bucket26size=120
Bucket27size=124
Bucket28size=128
Bucket29size=132
Bucket30size=136
Bucket31size=140
Bucket32size=144
Bucket33size=148
Bucket34size=152
Bucket35size=156
Bucket36size=160
Bucket37size=164
Bucket38size=168
Bucket39size=172
Bucket40size=176
Bucket41size=180
Bucket42size=184
Bucket43size=188
Bucket44size=192
Bucket45size=196
Bucket46size=200
Bucket47size=204
Bucket48size=208
Bucket49size=212
Bucket50size=216
Bucket51size=220
Bucket52size=224
Bucket53size=228
Bucket54size=232
Bucket55size=236
Bucket56size=240
Bucket57size=244
Bucket58size=248
Bucket59size=252
Bucket60size=256
Bucket61size=260
Bucket62size=264
Bucket63size=268
Bucket64size=272
Bucket65size=276
Bucket66size=280
Bucket67size=284
Bucket68size=288
Bucket69size=292
Bucket70size=296
Bucket71size=300
Bucket72size=304
Bucket73size=308
Bucket74size=312
Bucket75size=316
Bucket76size=320
Bucket77size=324
Bucket78size=328
Bucket79size=332
Bucket80size=336
Bucket81size=340
Bucket82size=344
Bucket83size=348
Bucket84size=352
Bucket85size=356
Bucket86size=360
Bucket87size=364
Bucket88size=368
Bucket89size=372
Bucket90size=376
Bucket91size=380
Bucket92size=384
Bucket93size=388
Bucket94size=392
Bucket95size=396
Bucket96size=400
Bucket97size=404
Bucket98size=408
Bucket99size=412
Bucket100size=416
Bucket101size=420
Bucket102size=424
Bucket103size=428
Bucket104size=432
Bucket105size=436
Bucket106size=440
Bucket107size=444
Bucket108size=448
Bucket109size=452
Bucket110size=456
Bucket111size=460
Bucket112size=464
Bucket113size=468
Bucket114size=472
Bucket115size=476
Bucket116size=480
Bucket117size=484
Bucket118size=488
Bucket119size=492
Bucket120size=496
Bucket121size=500
Bucket122size=504
Bucket123size=508
Bucket124size=512
Bucket125size=516
Bucket126size=520
Bucket127size=524
Bucket128size=528
Bucket129size=532
Bucket130size=536
Bucket131size=540
Bucket132size=544
Bucket133size=548
Bucket134size=552
Bucket135size=556
Bucket136size=560
Bucket137size=564
Bucket138size=568
Bucket139size=572
Bucket140size=576
Bucket141size=580
Bucket142size=584
Bucket143size=588
Bucket144size=592
Bucket145size=596
Bucket146size=600
Bucket147size=604
Bucket148size=608
Bucket149size=612
Bucket150size=616
Bucket151size=620
Bucket152size=624
Bucket153size=628
Bucket154size=632
Bucket155size=636
Bucket156size=640
Bucket157size=644
Bucket158size=648
Bucket159size=652
Bucket160size=656
Bucket161size=660
Bucket162size=664
Bucket163size=668
Bucket164size=672
Bucket165size=676
Bucket166size=680
Bucket167size=684
Bucket168size=688
Bucket169size=692
Bucket170size=696
Bucket171size=700
Bucket172size=704
Bucket173size=708
Bucket174size=712
Bucket175size=716
Bucket176size=720
Bucket177size=724
Bucket178size=728
Bucket179size=732
Bucket180size=736
Bucket181size=740
Bucket182size=744
Bucket183size=748
Bucket184size=752
Bucket185size=756
Bucket186size=760
Bucket187size=764
Bucket188size=768
Bucket189size=772
Bucket190size=776
Bucket191size=780
Bucket192size=784
Bucket193size=788
Bucket194size=792
Bucket195size=796
Bucket196size=800
Bucket197size=804
Bucket198size=808
Bucket199size=812
Bucket200size=876
Bucket201size=940
Bucket202size=1004
Bucket203size=1068
Bucket204size=1132
Bucket205size=1196
Bucket206size=1260
Bucket207size=1324
Bucket208size=1388
Bucket209size=1452
Bucket210size=1516
Bucket211size=1580
Bucket212size=1644
Bucket213size=1708
Bucket214size=1772
Bucket215size=1836
Bucket216size=1900
Bucket217size=1964
Bucket218size=2028
Bucket219size=2092
Bucket220size=2156
Bucket221size=2220
Bucket222size=2284
Bucket223size=2348
Bucket224size=2412
Bucket225size=2476
Bucket226size=2540
Bucket227size=2604
Bucket228size=2668
Bucket229size=2732
Bucket230size=2796
Bucket231size=2860
Bucket232size=2924
Bucket233size=2988
Bucket234size=3052
Bucket235size=3116
Bucket236size=3180
Bucket237size=3244
Bucket238size=3308
Bucket239size=3372
Bucket240size=3436
Bucket241size=3500
Bucket242size=3564
Bucket243size=3628
Bucket244size=3692
Bucket245size=3756
Bucket246size=3820
Bucket247size=3884
Bucket248size=3948
Bucket249size=4012
Bucket250size=4108
Bucket251size=8204
Bucket252size=16396
Bucket253size=32780
Bucket254size=65548

我们看到,在Oracle9i中,FreeLists被分别为0~254,共255个Bucket
每一个Bucket包容的size局限
Bucket0~199包容size以4递增
Bucket200~249包容size以64递增

从Bucket249入手下手,Oracle各Bucket步上进一步增添:

Bucket249:4012~4107=96
Bucket250:4108~8203=4096
Bucket251:8204~16395=8192
Bucket252:16396~32779=16384
Bucket253:32780~65547=32768
Bucket254:>=65548

在Oracle9i中,关于小的chunk,Oracle增添了更多的Bucket来办理
0~199共200个Bucket,size以4为步长递增;200~249共50个Bucket,size以64递增.
如许每一个Bucket中包容的chunk数目年夜年夜削减,查找的效力得以进步.

这就是Oracle9i中sharedpool办理的加强,经由过程这个算法的改善
Oracle8i中,过年夜sharedpool带来的栓锁争用等功能成绩在某种水平上得以办理.
从理论上讲，完全可以为数据表里的每个字段分别建一个索引，但MySQL把同一个数据表里的索引总数限制为16个。

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

其中最有名的应该是row_number了。这个终于解决了用临时表生成序列号的历史，而且SQLServer2005的row_number比Oracle的更先进。因为它把Orderby集成到了一起，不用像Oracle那样还要用子查询进行封装。

一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

所以你总能得到相应的升级版本，来满足你的需求。