Note: ASMM and dynamic memory
management has considerable overhead. Burleson
does not recommend AMM for large Oracle databases
because separate SGA pools are better managed by a
DBA than via any automated software. AMM is
best for small systems that do not have a DBA.
important notes on
dynamic memory management problems.
Memory Management has "issues" since its inception and by 11g
release 2 it remains problematic, and in some cases ASMM should be disabled in 11g
release 2. See MOSC note 793845.1 titled: "High
direct path read waits in 11g" for complete details.
When using AMM you have to
consider the interaction of these parameters:
Osama Mustafa )
11g): If the sga_target is set to
some value then the automatic shared memory
management (ASMM) is enabled, the sga_target
value can be adjusted up to the sga_max_size
parameter, not more than sga_max_size parameter
in 11g): If memory_target is set,
then AMM is enabled:
If memory_target is set to non zero value
sga_max_size and pga_aggregate_target are
set to 0, then 60% of memory mentioned in
memory_target is allocated to SGA and rest
40% is kept for PGA.
pga_aggregate_target are set to non-zero
values, then these values will be considered
sga_target is set to
non zero value and pga_aggregate_target is
not set. still these values will be
auto-tuned and pga_aggregate_target will be
initialized with value of
set and sga_target is not set. Both
parameters will be auto-tuned. The
sga_target will be initialized to a value of
||60% of memory_target to SGA, 40% to PGA
||pga_aggregate_target = memory_target -
||sga_target is set to memory_target -
Oracle heuristic tuning is a well-known scientific
approach that has been codified inside the Oracle
Automatic Shared Memory Manager (AMM) and Oracle Data
Mining tools. Heuristic techniques are well-proven
and accepted within the scientific community. The
Heuristic approach is very straightforward. We
observe our Oracle environment, searching for
statistically-valid correlations, and apply these
"rules of thumb" to new situations.
When Oracle9i first allowed "alter system" commands to
morph the SGA, Oracle 10g introduced Automatic Memory Management, a reactive
tool to re-size the RAM regions. While AMM is fine for smaller systems,
there are reports of AMM causing performance problems:
The best example is the Oracle10g Automatic Shared Memory
Manager (ASMM, or AMM) which observes historical buffer cache
information and builds a statistically-reliable
predictive model on re-sizing the data buffer.
Using the rule-of-thumb "Increase the data buffer
cache size until the marginal benefit declines",
AMM can estimate the optimal cache size (the
working-set of frequently-referenced data blocks).
When the marginal benefit of adding
additional data blocks is plotted, the
output looks like the following predictive
model from v$db_cache_advice (x$kcbcbh).
When this data is plotted, the result is a
typical 1/x curve as shown above. For an
undersized buffer, a large reduction in disk
I/O is achieved with a small increase in the
size of a small RAM buffer.
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 docs note this on memory_max_target:
"For the MEMORY_MAX_TARGET
initialization parameter, decide on a maximum amount
of memory that you would want to allocate to the
database for the foreseeable future.
That is, determine the maximum
value for the sum of the SGA and instance PGA
Note: When using AMM, the
values for the "traditional" pool parameters (db_cache_size,
shared_pool_size, &c) are not ignored. Rather,
they will specify the minimum size that Oracle will
always maintain for each sub-area in the SGA.
Warnings about AMM
Quest Software's Guy Harrison has
about using the AMM:
"When you use MTS and AMM (or
ASMM) together, PL/SQL programs that try to create
large collections can effectively consume all
available server memory with disastrous consequences
AMM allocates virtually all memory
on the system to the large pool in order to
accommodate the PL/SQL memory request. First it
consumes the buffer cache, then it reduces the
PGA_AGGREGATE_TARGET - all the way to zero!"
For more details on using automatic memory
management (AMM), see:
How to disable AMM: See these
important notes on disabling AMM (Automatic
Space Memory Management)