Question: Can you please explain the Oracle sga_max_size
parameter?
Answer: Wasting expensive RAM resources is a bad thing and the goal
of the Oracle DBA is to maximize the SGA region with intelligent SGA
sizing techniques in relation to sga_max_size.
The sga_max_size was introduced for databases where the
DBA did not know how to optimize the SGA pools manually. Today, a
competent DBA can do a better job that automatic shared memory
management, and large Oracle database still use manual SGA pool sizing.
Note that sga_max_size is for
Oracle 10g only, and in 11g and beyond, Oracle automatic
memory management is configured using the memory_target and
memory_max_target initialization parameters. The
memory_target parameter specifies the amount of shared memory available
for Oracle to use when dynamically controlling the SGA and PGA. The
memory_max_target AMM parameter specifies the max size that
memory_target may take.
The Oracle sga_max_size parameter sets the hard limit
up to which sga_target can dynamically adjust sizes. Usually,
sga_max_size and sga_target will be the same value, but there
may be times when you want to have the capability to adjust for peak
loads. By setting the Oracle sga_max_size parameter higher than
sga_target, you allow dynamic adjustment of the sga_target
parameter.
AIX Note for sga_target: There
are reported cases where sga_target cannot exceed two
gigabytes. This is because of the need to edit the
/etc/security/limits file and look for Soft DATA segment, it should
have the value of -1 (Unlimited).
Beware OF AMM:
There are reports where
automatic memory management (implemented by setting the
sga_target and sga_max_size
parameters) can cause performance problem, especially for databases with
undersized RAM resources.
See
Oracle (AMM)
resize operations
can hurt performance.
In sum, all large
mission-critical databases should have an expert DBA manually configure
and optimize their SGA pool sizes.
Also, larger
shops many forgo AMM and manually size their shared_pool_size
and db_cache_size.
A human DBA can frequently do a better job in sizing the RAM pools,
especially when using
predictive tuning techniques, which anticipate changes in workload
and adjust the pools before a shortage occurs.
See my
additional notes on Oracle sga_max_size:
and include the URL for the page.
