RE: Forcing ASMM component to shrink

Note that Oracle 10.2 *does* shrink shared pool automatically if needed...
it can even keep buffer cache buffers in shared pool starting from 10.2...
 
http://www.orafaq.com/maillist/oracle-l/2006/08/22/0958.htm
 
--
Regards,
Tanel Poder
http://blog.tanelpoder.com <http://blog.tanelpoder.com/> 

 


  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Vishal Gupta
Sent: Tuesday, December 04, 2007 16:28
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Forcing ASMM component to shrink



I have found the way to forcibly shrink shared pool dynamically.
Documentation stats that ASMM can only increase shared pool, it can not
shrink it. To shrink shared_pool_size immediately, you have to switch to
manual mode. After shrinking you can again switch back to ASMM mode.

 

 
<http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#s
thref383>
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/create.htm#st
href383 

Automatic Tuning and the Shared Pool

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.

Other related posts: