关于shared pool的深入探讨(一)
关于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中的处理方式:
[oracle@jumper 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> ! [oracle@jumper oracle]$ cd $admin [oracle@jumper 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带来的栓锁争用等性能问题在某种程度上得以解决.