|
Note: ASMM and dynamic memory
management has considerable overhead. See my
important notes on
Oracle
dynamic memory management.
Oracle heuristic tuning is a well-known scientific
approach that has been codified inside the Oracle
10g 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
sizes.”
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
this warning
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:
http://www.dba-oracle.com/o10g_15.htm
http://www.dba-oracle.com/art_so_fav_10g.htm
How to disable AMM: See these
important notes on disabling AMM (Automatic
Space Memory Management)
|
