MEMORY_TARGET AND SGA_MAX_SIZE PARAMETERS in oracle 11g


SGA_% parameters are enabled when AMM(Automatic memory management) is disable.

AMM enabled = SGA and PGA sizes are automatically tuned by oracle. We just set Memory_MAX_TARGET
and MEMORY_TARGET params and oracle give enough memory to the SGA and PGA whatever they need.
For ex: afternoon SGA has no empty memory space but PGA has a lot of them, oracle takes spaces
from PGA and give it to the SGA.

while AMM is enbled sga_target and sga_max_target are not considered (except lower bound limit,
if you set sga_target like 10G, then oracle can not takes too much space and cause to SGA has
less then 10G).

if you disable AMM, this means Memory_MAX_TARGET and MEMORY_TARGET are 0 any more and oracle
will not transfer memory spaced between SGA and PGA! from now, you need SGA_TARGET and
SGA_MAX_TARGET params to be set. so oracle will know how much space SGA will use and also
components of SGA can be auto-tuned (buffer cache, large pool etc).

SGA_MAX_SIZE:-
sga_max_size sets the maximum value for sga_target If sga_max_size is less than the sum of db_cache_size +
log_buffer + shared_pool_size + large_pool_size at initialization time, then the value of sga_max_size is ignored.

SGA_TARGET:-
It specifies the total amaount of SGA memory available to an instance. Setting this parameter makes Oracle distribute the available memory among various components - such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as required.
This new feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not be specified explicitely anymore.
sga_target cannot be higher than sga_max_size.


SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
+++++++++++++++++++++++++++++++++++++++++++++++++

SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.

The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. 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 value.

MEMORY_TARGET & MEMORY_MAX_TARGET 

you can manage SGA and PGA together rather than managing them separately.

If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET to 0 and set MEMORY_TARGET (and optionally MEMORY_MAX_TARGET) to non zero value, Oracle will manage both SGA components and PGA together within the limit specified by you.

If MEMORY_TARGET is set to 1024MB, Oracle will manage SGA and PGA components within itself.

If MEMORY_TARGET is set to non zero value:

SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_TARGET are set to 0, 60% of memory mentioned in MEMORY_TARGET
is allocated to SGA and rest 40% is kept for PGA.
SGA_TARGET and PGA_AGGREGATE_TARGET are set to non-zero values, these values will be considered minimum
values.
SGA_TARGET is set to non zero value and PGA_AGGREGATE_TARGET is not set. Still these values will be
autotuned and PGA_AGGREGATE_TARGET will be initialized with value of (MEMORY_TARGET-SGA_TARGET).
PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set. Still both parameters will be autotunes.
SGA_TARGET will be initialized to a value of (MEMORY_TARGET-PGA_AGGREGATE_TARGET).

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...