Oracle 11g memory_target parameter
Oracle Tips by Burleson Consulting
This short article examines the new PGA memory management in Oracle 11g,
using the new memory_target and memory_max_target
Prior to Oracle 11g, the DBA set the sga_target and
sga_max_size parameters, allowing Oracle to reallocate RAM
within the SGA. The PGA was independent, as governed by
the pga_aggregate_target parameter.
Oracle will automatically set sga_max_size to 60% of
the setting for memory_target
Now in Oracle 11g we see the memory_max_target parameter
which governs the total maximum RAM for both the PGA and SGA
regions and the new memory_target parameter which governs
the existing sizes. This allows RAM to be de-allocated
from the SGA and transferred to the PGA.
Note: When using AMM (by setting
memory_target, and/or sga_target, 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.
This is an important Oracle11g new feature because it lays the
foundation for inter-instance RAM memory sharing.
We are seeing the
second age of mainframe computing, and server consolidation
where it's not uncommon to find a dozen instances on a single
As of Oracle 10g, Automatic Memory Management (AMM) only allows
shifting of RAM within the confines of sga_max_size.
Now in Oracle 11g, we see this new Automatic Shared Memory
Management (ASMM). The memory_target parameter is dynamic
(changeable with "alter system" commands), whereby RAM
can be de-allocated from an instances SGA/PGA and re-allocated
to another instance.
The Oracle 11g documentation notes that the memory_target
specifies the system-wide RAM and explains that the memory_max_target=memory_target
if you do not explicitly set memory_max_target.
The docs also note that the memory_max_target specifies
the maximum amount of RAM that you would allocate for all of
Oracle, all SGA and PGA regions combined.
Issues with memory_target in 11g - Steve Karam
working on an 11g client yesterday. Their settings were:
memory_target = 16G
sga_target = 0
db_cache_size = 0
shared_pool_size = 0
whatever reason, Oracle would not allocate over 64 MB for the buffer cache.
We tried to set the minimum by doing:
system set db_cache_size = 6G
received this error:
ORA-32017: failure in updating SPFILE
memory to grow cache
Pretty weird, right?
So we set memory_target = 0, which set the
sga_target = 640MB and kept all the other pools at 0. Then we
set the sga_target to 0, and were finally allowed to use more
memory for the buffer cache.
So even though the new AMM parameter was
set very high, Oracle was still going by the rules of the underlying
sga_target parameter, which was incorrect.
I wanted to pass
this around so you all know that memory_target in 11g is buggy. For
that matter, sga_target has always seemed buggy as well with the
way it can harm system performance.
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.