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

2024-07-21 02:11:33

字体：大中小

来源：转载

供稿：网友

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

link:

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

关于shared pool的设置一直是一个争议较多的内容.
很多文章上说,shared pool设置过大会带来额外的管理上的负担，从而在某些条件下会导致性能的下降.

那么这个管理上的负担指的是什么内容呢?
本文对这个内容作一定的深入探讨.
本文只涉及一个方面,后续的文章将从其他方面继续讨论.

基础知识:

我们可以通过如下命令转储shared pool共享内存的内容:

sql> alter session set events 'immediate trace name heapdump level 2';

session altered.

本测试中引用的两个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

shared pool通过free list管理free块,free list按不同size划分bucket
在oracle8i中,不同bucket的size范围如下所示(size显示的是下边界):

oracle:/usr/oracle8/admin/guess/udump>cat guess_ora_22038.trc|grep bucket
bucket 0 size=44
bucket 1 size=76
bucket 2 size=140
bucket 3 size=268
bucket 4 size=524
bucket 5 size=1036
bucket 6 size=2060
bucket 7 size=4108
bucket 8 size=8204
bucket 9 size=16396
bucket 10 size=32780

我们注意,在这里，小于76的块都位于bucket 0上;大于32780的块,都在bucket 10上
初始的,数据库启动以后,shared pool多数是连续内存块
当空间分配使用以后,内存块开始被分割,碎片开始出现，bucket列表开始变长

oracle请求shared pool空间时，首先进入相应的bucket进行查找
如果找不到,则转向下一个非空的bucket,获取第一个chunk
分割这个chunk,剩余部分会进入相应的bucket,进一步增加碎片

最终的结果是,bucket 0上的内存块会越来越多,越来越碎小
(在我这个测试的小型的数据库上,bucket 0上的碎片已经达到9030个
而shared_pool_size设置仅为150m)
通常如果每个bucket上的chunk多余2000个，就被认为是share pool碎片过多

而在大多数情况下,我们请求的都是相对小的chunk,这样搜索bucket 0往往消耗了大量的时间以及资源
这可能导致share pool latch被长时间的持有,导致更多的share pool竞争

所以在oracle9i之前,如果盲目的增大shared_pool_size或设置过大的shared_pool_size，往往会适得其反

我们看一下oracle9i中的处理方式:

[[email protected] oracle]$ sqlplus "/ as sysdba"

sql*plus: release 9.2.0.3.0 - production on wed aug 18 22:13:07 2004

copyright (c) 1982, 2002, oracle corporation. all rights reserved.

connected to:
oracle9i enterprise edition release 9.2.0.3.0 - production
with the partitioning, olap and oracle data mining options
jserver release 9.2.0.3.0 - production

sql> alter session set events 'immediate trace name heapdump level 2';

session altered.

sql> @gettrcname

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

sql>
sql> !
[[email protected] oracle]$ cd $admin
[[email protected] udump]$ cat hsjf_ora_24983.trc|grep bucket
bucket 0 size=16
bucket 1 size=20
bucket 2 size=24
bucket 3 size=28
bucket 4 size=32
bucket 5 size=36
bucket 6 size=40
bucket 7 size=44
bucket 8 size=48
bucket 9 size=52
bucket 10 size=56
bucket 11 size=60
bucket 12 size=64
bucket 13 size=68
bucket 14 size=72
bucket 15 size=76
bucket 16 size=80
bucket 17 size=84
bucket 18 size=88
bucket 19 size=92
bucket 20 size=96
bucket 21 size=100
bucket 22 size=104
bucket 23 size=108
bucket 24 size=112
bucket 25 size=116
bucket 26 size=120
bucket 27 size=124
bucket 28 size=128
bucket 29 size=132
bucket 30 size=136
bucket 31 size=140
bucket 32 size=144
bucket 33 size=148
bucket 34 size=152
bucket 35 size=156
bucket 36 size=160
bucket 37 size=164
bucket 38 size=168
bucket 39 size=172
bucket 40 size=176
bucket 41 size=180
bucket 42 size=184
bucket 43 size=188
bucket 44 size=192
bucket 45 size=196
bucket 46 size=200
bucket 47 size=204
bucket 48 size=208
bucket 49 size=212
bucket 50 size=216
bucket 51 size=220
bucket 52 size=224
bucket 53 size=228
bucket 54 size=232
bucket 55 size=236
bucket 56 size=240
bucket 57 size=244
bucket 58 size=248
bucket 59 size=252
bucket 60 size=256
bucket 61 size=260
bucket 62 size=264
bucket 63 size=268
bucket 64 size=272
bucket 65 size=276
bucket 66 size=280
bucket 67 size=284
bucket 68 size=288
bucket 69 size=292
bucket 70 size=296
bucket 71 size=300
bucket 72 size=304
bucket 73 size=308
bucket 74 size=312
bucket 75 size=316
bucket 76 size=320
bucket 77 size=324
bucket 78 size=328
bucket 79 size=332
bucket 80 size=336
bucket 81 size=340
bucket 82 size=344
bucket 83 size=348
bucket 84 size=352
bucket 85 size=356
bucket 86 size=360
bucket 87 size=364
bucket 88 size=368
bucket 89 size=372
bucket 90 size=376
bucket 91 size=380
bucket 92 size=384
bucket 93 size=388
bucket 94 size=392
bucket 95 size=396
bucket 96 size=400
bucket 97 size=404
bucket 98 size=408
bucket 99 size=412
bucket 100 size=416
bucket 101 size=420
bucket 102 size=424
bucket 103 size=428
bucket 104 size=432
bucket 105 size=436
bucket 106 size=440
bucket 107 size=444
bucket 108 size=448
bucket 109 size=452
bucket 110 size=456
bucket 111 size=460
bucket 112 size=464
bucket 113 size=468
bucket 114 size=472
bucket 115 size=476
bucket 116 size=480
bucket 117 size=484
bucket 118 size=488
bucket 119 size=492
bucket 120 size=496
bucket 121 size=500
bucket 122 size=504
bucket 123 size=508
bucket 124 size=512
bucket 125 size=516
bucket 126 size=520
bucket 127 size=524
bucket 128 size=528
bucket 129 size=532
bucket 130 size=536
bucket 131 size=540
bucket 132 size=544
bucket 133 size=548
bucket 134 size=552
bucket 135 size=556
bucket 136 size=560
bucket 137 size=564
bucket 138 size=568
bucket 139 size=572
bucket 140 size=576
bucket 141 size=580
bucket 142 size=584
bucket 143 size=588
bucket 144 size=592
bucket 145 size=596
bucket 146 size=600
bucket 147 size=604
bucket 148 size=608
bucket 149 size=612
bucket 150 size=616
bucket 151 size=620
bucket 152 size=624
bucket 153 size=628
bucket 154 size=632
bucket 155 size=636
bucket 156 size=640
bucket 157 size=644
bucket 158 size=648
bucket 159 size=652
bucket 160 size=656
bucket 161 size=660
bucket 162 size=664
bucket 163 size=668
bucket 164 size=672
bucket 165 size=676
bucket 166 size=680
bucket 167 size=684
bucket 168 size=688
bucket 169 size=692
bucket 170 size=696
bucket 171 size=700
bucket 172 size=704
bucket 173 size=708
bucket 174 size=712
bucket 175 size=716
bucket 176 size=720
bucket 177 size=724
bucket 178 size=728
bucket 179 size=732
bucket 180 size=736
bucket 181 size=740
bucket 182 size=744
bucket 183 size=748
bucket 184 size=752
bucket 185 size=756
bucket 186 size=760
bucket 187 size=764
bucket 188 size=768
bucket 189 size=772
bucket 190 size=776
bucket 191 size=780
bucket 192 size=784
bucket 193 size=788
bucket 194 size=792
bucket 195 size=796
bucket 196 size=800
bucket 197 size=804
bucket 198 size=808
bucket 199 size=812
bucket 200 size=876
bucket 201 size=940
bucket 202 size=1004
bucket 203 size=1068
bucket 204 size=1132
bucket 205 size=1196
bucket 206 size=1260
bucket 207 size=1324
bucket 208 size=1388
bucket 209 size=1452
bucket 210 size=1516
bucket 211 size=1580
bucket 212 size=1644
bucket 213 size=1708
bucket 214 size=1772
bucket 215 size=1836
bucket 216 size=1900
bucket 217 size=1964
bucket 218 size=2028
bucket 219 size=2092
bucket 220 size=2156
bucket 221 size=2220
bucket 222 size=2284
bucket 223 size=2348
bucket 224 size=2412
bucket 225 size=2476
bucket 226 size=2540
bucket 227 size=2604
bucket 228 size=2668
bucket 229 size=2732
bucket 230 size=2796
bucket 231 size=2860
bucket 232 size=2924
bucket 233 size=2988
bucket 234 size=3052
bucket 235 size=3116
bucket 236 size=3180
bucket 237 size=3244
bucket 238 size=3308
bucket 239 size=3372
bucket 240 size=3436
bucket 241 size=3500
bucket 242 size=3564
bucket 243 size=3628
bucket 244 size=3692
bucket 245 size=3756
bucket 246 size=3820
bucket 247 size=3884
bucket 248 size=3948
bucket 249 size=4012
bucket 250 size=4108
bucket 251 size=8204
bucket 252 size=16396
bucket 253 size=32780
bucket 254 size=65548

我们看到,在oracle9i中,free lists被划分为0~254,共255个bucket
每个bucket容纳的size范围
bucket 0~199 容纳size以 4 递增
bucket 200~249 容纳size以 64 递增

从bucket 249开始,oracle各bucket步长进一步增加:

bucket 249: 4012 ~4107 = 96
bucket 250: 4108 ~8203 = 4096
bucket 251: 8204 ~16395 = 8192
bucket 252: 16396~32779 = 16384
bucket 253: 32780~65547 = 32768
bucket 254: >=65548

在oracle9i中,对于小的chunk,oracle增加了更多的bucket来管理
0~199共200个bucket,size以4为步长递增;200~249共50个bucket,size以64递增.
这样每个bucket中容纳的chunk数量大大减少,查找的效率得以提高.

这就是oracle9i中shared pool管理的增强,通过这个算法的改进
oracle8i中,过大shared pool带来的栓锁争用等性能问题在某种程度上得以解决.

商业源码热门下载www.html.org.cn

上一篇：分区的威力(翻译)

下一篇：PL/SQL Developer 6.02发布