An in-depth discussion on shared pool (i.)

Source: Internet
Author: User
Tags range

An in-depth discussion on shared pool (i.)



The setting of shared pool is always a controversial subject.
In many articles, shared pool sets up an additional management burden that can lead to performance degradation under certain conditions.

So what does the burden of management mean?
This article makes a certain in-depth discussion on this content.
This article covers only one aspect, and subsequent articles will continue to be discussed in other ways.

Basic knowledge:

We can dump the contents of shared pool memory through the following command:

Sql> alter session SET events ' immediate trace name heapdump Level 2 ';

Session altered.

Two trace files referenced in this test:


Sql> @gettrcname



Sql> @gettrcname


Shared Pool manages free blocks via free list, and free list is divided into different size bucket
In Oracle8i, the size range of the different bucket is shown below (the size shows the bottom boundary):

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 size=32780

We note that here, the blocks less than 76 are located on bucket 0, and the blocks greater than 32780 are on bucket 10.
Initial, after database startup, shared pool is mostly contiguous memory blocks
When the space allocation is used, the memory blocks begin to be segmented, the fragments begin to appear, and the bucket list begins to grow.

When Oracle requests shared pool space, it first goes to the appropriate bucket to find
If it is not found, it moves to the next non-empty bucket to get the first chunk
Split this chunk, the remainder will go into the corresponding bucket, further increasing the fragment

The end result is that the memory blocks on Bucket 0 will be more and more broken and smaller
(on the small database of my test, the fragment on Bucket 0 has reached 9,030
And the Shared_pool_size setting is only 150M)
Usually if the chunk on each bucket is more than 2000, it is considered share pool is too fragmented.

And in most cases, we're asking for relatively small chunk, so searching bucket 0 often consumes a lot of time and resources
This could lead to share pool latch being held for a long time, leading to more share pool competition

So before oracle9i, if blindly increase shared_pool_size or set too large shared_pool_size, often counterproductive

Let's take a look at how the oracle9i is handled:

[Oracle@jumper oracle]$ Sqlplus "/As SYSDBA"

Sql*plus:release on Wed Aug 18 22:13:07 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release
With the partitioning, OLAP and Oracle Data Mining options
Jserver Release

Sql> alter session SET events ' immediate trace name heapdump Level 2 ';

Session altered.

Sql> @gettrcname


[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 size=56
Bucket size=60
Bucket size=64
Bucket size=68
Bucket size=72
Bucket size=76
Bucket size=80
Bucket size=84
Bucket size=88
Bucket size=92
Bucket size=96
Bucket size=100
Bucket size=104
Bucket size=108
Bucket size=112
Bucket size=116
Bucket size=120
Bucket size=124
Bucket size=128
Bucket size=132
Bucket size=136
Bucket size=140
Bucket size=144
Bucket size=148
Bucket size=152
Bucket size=156
Bucket size=160
Bucket Notoginseng size=164
Bucket size=168
Bucket size=172
Bucket size=176
Bucket size=180
Bucket size=184
Bucket size=188
Bucket size=192
Bucket size=196
Bucket size=200
Bucket size=204
Bucket size=208
Bucket size=212
Bucket size=216
Bucket Wuyi size=220
Bucket size=224
Bucket size=228
Bucket size=232
Bucket size=236
Bucket size=240
Bucket size=244
Bucket size=248
Bucket size=252
Bucket size=256
Bucket size=260
Bucket size=264
Bucket size=268
Bucket size=272
Bucket size=276
Bucket size=280
Bucket size=284
Bucket size=288
Bucket size=292
Bucket size=296
Bucket size=300
Bucket size=304
Bucket size=308
Bucket size=312
Bucket size=316
Bucket size=320
Bucket size=324
Bucket size=328
Bucket size=332
Bucket size=336
Bucket Bayi size=340
Bucket size=344
Bucket size=348
Bucket size=352
Bucket size=356
Bucket size=360
Bucket size=364
Bucket size=368
Bucket size=372
Bucket size=376
Bucket size=380
Bucket size=384
Bucket size=388
Bucket size=392
Bucket size=396
Bucket size=400
Bucket size=404
Bucket size=408
Bucket size=412
Bucket size=416
Bucket size=420
Bucket 102 size=424
Bucket size=428
Bucket size=432
Bucket size=436
Bucket size=440
Bucket size=444
Bucket 108 size=448
Bucket 109 size=452
Bucket size=456
Bucket size=460
Bucket 112 size=464
Bucket 113 size=468
Bucket 114 size=472
Bucket size=476
Bucket 116 size=480
Bucket 117 size=484
Bucket 118 size=488
Bucket 119 size=492
Bucket size=496
Bucket 121 size=500
Bucket 122 size=504
Bucket 123 size=508
Bucket 124 size=512
Bucket 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 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 size=580
Bucket 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 size=616
Bucket 151 size=620
Bucket 152 size=624
Bucket 153 size=628
Bucket 154 size=632
Bucket 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 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 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 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 size=876
Bucket 201 size=940
Bucket size=1004
Bucket 203 size=1068
Bucket 204 size=1132
Bucket 205 size=1196
Bucket 206 size=1260
Bucket 207 size=1324
Bucket 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 size=2156
Bucket 221 size=2220
Bucket 222 size=2284
Bucket 223 size=2348
Bucket 224 size=2412
Bucket size=2476
Bucket size=2540
Bucket 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 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 size=4108
Bucket 251 size=8204
Bucket 252 size=16396
Bucket 253 size=32780
Bucket 254 size=65548

We see that in oracle9i, free lists is divided into 0~254, a total of 255 bucket
Size range that each bucket accommodates
Bucket 0~199 accommodates size with 4 increments
Bucket 200~249 accommodates size with 64 increments

Starting with bucket 249, Oracle's bucket steps are further increased:

Bucket 249:4,012 ~4107 = 96
Bucket 250:4,108 ~8203 = 4096
Bucket 251:8,204 ~16395 = 8192
Bucket 252:16396~32779 = 16384
Bucket 253:32780~65547 = 32768
Bucket 254: >=65548

In Oracle9i, for small chunk,oracle added more bucket to manage
0~199 a total of 200 bucket,size to 4 as step increments, 200~249 a total of 50 bucket,size to 64 increments.
In this way, the number of chunk accommodated in each bucket is greatly reduced and the search efficiency is improved.

This is the enhancement of shared pool management in Oracle9i, which is improved by this algorithm
In Oracle8i, performance issues such as lock contention for excessive shared pool are resolved to some extent.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.