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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Vishal Gupta
Sent: Tuesday, December 04, 2007 16:28
To: oracle-l_at_freelists.org
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.  

--

http://www.freelists.org/webpage/oracle-l Received on Tue Dec 04 2007 - 10:17:54 CST