We have separated SAP PI application and our Oracle database 11g on two hosts. With this SAP oracle database on same host are running a lot of another non sap DBs. So its hard to calculate how much of resources we can use. We need to adjust these parameters because recently was another DB installed on that host and hardware is now not enough, so we would like to check if some of our parameters can be decreased because they are just dev and qua systems.
So my idea is to fix obviously wrong parametrization and try it with less memory and see if I have any issue then.
Here are our current parameters:
pga_aggregate_target big integer 3092376453
sga_target big integer 4528M
shared_pool_size big integer 2224M
db_cache_size big integer 2224M
memory_max_target big integer 0
memory_target big integer 0
Oracle DBA guys want to turn it to AMM by re-set these values
alter system set pga_aggregate_target=0 scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set shared_pool_size=0 scope=spfile;
alter system set db_cache_size=0 scope=spfile;
alter system set memory_max_target=4G scope=spfile;
alter system set memory_target=2G scope=spfile;
What solution would you prefer? Thanks a lot for your inputs.
We do not recommend the usage of AMM or ASMM, as there are some known issues that are very common like the ORA-04030 and ORA-04031. Also you most likely won't never reach the same performance of a fine tuned database with fixed memory areas. So though the use is allowed it is not advisable.
Hope this helps.