 |
|
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
parameters.
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.
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
large server.
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 this on memory_target
and memory_max_target:
MEMORY_TARGET specifies the Oracle system-wide usable memory.
The database
tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA
and PGA as needed. In a text initialization parameter file, if you omit the
line for MEMORY_MAX_TARGET and include a value for MEMORY_TARGET, the
database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET.
If you omit the line for MEMORY_TARGET and include a value for
MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero.
After startup, you can then dynamically change MEMORY_TARGET to a non-zero
value, provided that it does not exceed the value of MEMORY_MAX_TARGET.
The docs also 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.”
Issues with memory_target in 11g - Steve Karam
I was
working on an 11g client yesterday. Their settings were:
memory_target = 16G
sga_target = 0
db_cache_size = 0
shared_pool_size = 0
etc.
For
whatever reason, Oracle would not allocate over 64 MB for the buffer cache.
We tried to set the minimum by doing:
alter
system set db_cache_size = 6G
But we
received this error:
ERROR at
line 1:
ORA-32017: failure in updating SPFILE
ORA-00384: Insufficient
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
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
