Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
We are in the process of configuring our database instances to use AMM. Here are the changes we implemented:
alter system set lock_sga = false;
alter system set sga_target = 0;
alter system set pga_aggregate_target = 0;
alter system set memory_target = 25G;
alter system set memory_max_target = 40G SCOPE = SPFILE;
In two of our instances, the worked wonderfully and the results of the change were:
SQL> show parameters sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 40G
sga_target big integer 0
SQL> show parameters pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameters memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 40G
memory_target big integer 25G
shared_memory_address integer 0
SQL>
however, in the third instance had these results:
SQL> show parameters sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3G
sga_target big integer 0
SQL> show parameters pga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target big integer 0
SQL> show parameters memory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
memory_max_target big integer 40G
memory_target big integer 25G
shared_memory_address integer 0
SQL>
The SGA_MAX_SIZE is set to 3G where it should have been set to 40G (the value of the MEMORY_MAX_TARGET). Our users are now complaining of slowness on this instance.
It was my understanding that the SGA_MAX_SIZE should have been set to the MEMORY_MAX_TARGET. I've seen some reference to if the SGA_MAX_SIZE was previously set, then the MEMORY_MAX_TARGET would be ignored and the SGA_MAX_SIZE would remain its current value. This sounds right, since the previous "Manual" settings were SGA/PGA = 3G/1G.
Can anyone confirm this? To fix this, do I simply need to re-run the aforementioned settings, but add: "alter system set sga_max_size = 0;" to the beginning?
Any suggestions are greatly appreciated.
Craig...