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