Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Shared Pool Shrinking.... Need clarification

630732Mar 30 2009 — edited Mar 30 2009
Hi All Experts around the World,

My Question is
Q --> If we use automatic memory management, will the shared pool size shrink

Here is the Oracle documentation link which says it will not shrink :

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#BABGCBHI

Scroll down and you will see this heading : Automatic Tuning and the Shared Pool
( I have pasted what is written there in the below line )
When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasing in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, because the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find granules that can be freed. Therefore, the tuning algorithm only tries to increase the shared pool in conservative increments, starting from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.
Lets say we have shared_pool_size=50M and SGA_TARGET is also set to some value.
Here shared_pool_size will be the minimum value that oracle will allocate when instance starts...

Now if we have pinned PL/SQL packages, open cursors in shared pool which are occupying more than 50M (lets say 100M) then during shrinking operation oracle will not shrink shared pool below 100M. Thats correct, right?

Looking ahead for your responses

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 27 2009
Added on Mar 30 2009
3 comments
1,321 views