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 ';
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):
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 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 ';
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:
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.
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:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.
