RE: Automatic Memeory Management - Max value for Dynamic Component
Date: Fri, 9 May 2008 10:51:08 -0400
There is no way to restrict the max value that a component can grow to, other than SGA_MAX_SIZE and SGA_TARGET, but you can restrict the minimum size a component can drop to which is where I believe your headed. Simply set a value for a component in the spfile/init file. Say "alter system set buffer_cache=512M scope=both;" thjat way yout buffer cache will never go below 512MB.
Dick Goulet / Capgemini
North America P&C / East Business Unit
Senior Oracle DBA / Hosting
Office: 508.573.1978 / Mobile: 508.742.5795 / www.capgemini.com Fax: 508.229.2019 / Email: richard.goulet_at_capgemini.com 45 Bartlett St. / Marlborough, MA 01752
Together: the Collaborative Business Experience
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Shivaswamy / ?????????
Sent: Friday, May 09, 2008 9:39 AM
Subject: Automatic Memeory Management - Max value for Dynamic Component
I don't know if others on the list have faced this issue.
We have automatic memory management [AMM] set on our 10GR1 database. The shared pool can grow without restrictions (untill it uses up all available memory to it). The 'default buffer cache' will be the loser. Performance can come down drastically as a result. We recently had a severe issue on this. Since then, I started monitoring the components from v$sga_dynamic_components. We can always argue on bad SQL but, I think we need to have a way to restrict the max value a component can grow to.
Is there a way to restrict the maximum value a dynamic component can grow to? I am interested to restrict the max value for Shared_pool. Now, only way I have is to reset the double underscore parameter for shared pool and bounce the database.
Please let me know your view on this.