Enabling ASMM in 10g
Oracle Database Tips by Donald Burleson
I need to have automatic control over my SGA pools and I need to
know how to I turn-on the automatic shared memory management (ASMM)
feature of Oracle 10g?
You can enable and disable ASMM by changing the value of these
parameters to non-zero settings:
Many Oracle shops prefer the
flexibility of automatic memory management, while larger shops
prefer manual control over the sizing of their pools. In
practice, a human DBA can foresee an overall best-size for their
pools, while the ASMM is "reactive", responding to transient
changes in processing demands. Humans can see repeating
cycles and forecast future demands better than any reactive SGA
Note: ASMM and dynamic Oracle
memory management has measurable overhead. See
my important notes on
dynamic memory management.
Also, beware of these important
your PGA) for setting your pga_aggregate_target
parameter for maximum hash_join and minimum disk_sort
Once ASMM is enabled, Oracle will
disregard any init.ora file entries for the pool sizes below.
When displayed with a "show parameters" command, these
pools will show an arbitrary low or null value:
Data buffer pools:
How to disable AMM: See these
important notes on disabling AMM (Automatic Space Memory Management)
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.