Oracle Automatic Memory Management - SGA_MAX_SIZE

When using Automatic Memory Management, what value should SGA_MAX_SIZE be? Should it be 0? These are my current settings, and I'm unsure whether to leave SGA_MAX_SIZE or set it to 0.

memory_max_target     2147483648
memory_target         2147483648
pga_aggregate_target  0
sga_max_size          1610612736
sga_target            0

Oracle 11gR2

The docs for sga_max_size say:


On 64-bit platforms and non-Windows 32-bit platforms, when either MEMORY_TARGET or MEMORY_MAX_TARGET is specified, the default value of SGA_MAX_SIZE is set to the larger of the two parameters.

And according to Oracle document Automatic Memory Management (AMM) on 11g (Doc ID 443746.1), if you have both sga_target and pga_aggregate_target unset, then:


If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by MEMORY_TARGET parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% to the SGA and 40% to the PGA at startup.

So the simplest thing to do if you want Oracle to completely control the SGA is to reset it and let the default apply:

SQL> alter system reset sga_max_size scope=spfile;

(And restart.)

If you want AMM but would like minimum values for the SGA or the PGA (or both), do so by setting the corresponding _target parameter:


The preceding steps [to set up AMM] instruct you to set SGA_TARGET and PGA_AGGREGATE_TARGET to zero so that the sizes of the SGA and instance PGA are tuned up and down as required, without restrictions. You can omit the statements that set these parameter values to zero and leave either or both of the values as positive numbers. In this case, the values act as minimum values for the sizes of the SGA or instance PGA.

Whatever you do, don't set the PGA or SGA target or max size to greater than the memory target. This prevents instance startup.

StackExchange Database Administrators Q#63875, answer score: 5