Discussion on Shared Pool (1)
LINK:
http://www.eygle.com/internal/shared_pool-1.htm
About Shared Pool settings have always been more controversial. Many articles say that Shared Pool sets a conference to bring additional management, so that under certain conditions will result in a decline in performance.
So what is the content of this management? This article is in-depth discussion on this content. This article only involves an aspect, follow-up articles will continue to discuss from other aspects.
Basic knowledge:
We can dump the contents of Shared Pool shared memory by using the following command:
SQL> Alter Session Set Events 'Immediate Trace Name Heapdump Level 2'
Session altered.
Two trace files referenced in this test:
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 Management Free blocks via free List, free list Pressing BUCKET in Oracle8i in Oracle8i, free List, as shown below (Size display is the lower 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 10 size = 32780
We note, here, the blocks of less than 76 are on Bucket 0; more than 32780 blocks, all in Bucket 10, after the database is started, the Shared Pool Most is the continuous memory block When the spatial distribution is used, the memory block begins. Split, fragment begins, the BUCKET list begins to grow
When Oracle requests Shared pool space, first enter the corresponding BUCKET for finding If you can't find it, turn to the next non-empty bucket, get the first CHUNK split this Chunk, and the remainder will enter the corresponding Bucket, further increase the fragmentation
The final result is that there will be more and more memory blocks on Bucket 0 (on the small database of this test), the fragment on Bucket 0 has reached 9030 and the shared_pool_size set is only 150m). If there is more than 2,000 Chunk on each bucket, it is considered to be too much Share Pool Debris.
In most cases, we have requested a relatively small Chunk so that Bucket 0 often consumes a lot of time and resources, which may cause Share Pool Latch to hold for a long time, leading to more Share Pool competition.
So before oracle9i, if blind increase Shared_Pool_Size or set too much shared_pool_size, it will often be counterproductive
Let's take a look at 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 - Productionwith The Partitioning, OLAP AND ORACLE DATA MINING OPTIONSJSERVER 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 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 = 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 = 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 = 20 8 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 Buck et 123 size = 508 Bucket 124 size = 512 125 size = 516 Bucket 126 size = 520 Bucket 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 size = 1388 size = 1452 210 size Bucket 208 Bucket 209 Bucket = 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 size = 2284 Bucket 223 size = 2348 Bucket Bucket 222 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 we have seen, in Oracle9i, Free Lists are divided into 0 ~ 254, a total of 255 Bucket Each Bucket accommodated Size range Bucket 0 ~ 199 to accommodate Size to increment by Bucket 200 ~ 249 to accumulate SIZE in 64
Starting from Bucket 249, Oracle's Bucket steps further increase:
Bucket 249: 4012 ~ 4107 = 96bucket 250: 4108 ~ 8203 = 4096bucket 251: 8204 ~ 16395 = 8192bucket 252: 16396 ~ 32779 = 16384Bucket 253: 32780 ~ 65547 = 32768bucket 254:> = 65548
In Oracle9i, for small Chunk, Oracle adds more Bucket to manage 0 ~ 199 builds, Size is incremented by 4, 200 ~ 249, a total of 50 buckets, SIZE increments in 64. Such each The number of CHUNKs accommodated in BUCKET greatly reduced, and the efficiency of finding is improved.
This is the enhancement of Shared Pool management in Oracle9i. Through the improvement of this algorithm Oracle8i, performance issues such as the bolt-locking treated context brought by excessive Shared Pool are solved some extent.
