關于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發布