5

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

1 Answer 1

5

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.

3
  • I think alter system reset sga_max_size; will raise an error. Something like alter system reset sga_max_size scope=spfile; and a restart of the db will be necessary if an spfile is used.
    – miracle173
    Commented Apr 25, 2014 at 7:24
  • 1
    It doesn't raise an error here (but indeed a restart is necessary), but I'm testing on 12c so maybe it's required in 11.2.
    – Mat
    Commented Apr 25, 2014 at 7:38
  • 1
    Actually no error is raised and your statement works in 11r2 too. sorry
    – miracle173
    Commented Apr 25, 2014 at 12:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.