MEMORY_TARGET vs SGA_TARGET vs PGA_AGGREGATE_TARGET

Purpose:

• Automatic memory management is introduced into Oracle 11g. This can be configured using a target memory size initialization parameter MEMORY_TARGET and a maximum memory size initialization parameter MEMORY_MAX_TARGET. 
• Oracle Database then tunes to MEMORY_TARGET size, by distributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA). 

Scope and Application

• If  MEMORY_TARGET is set to a non-zero value.

·                If  SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively. MEMORY_TARGET can take values from SGA_TARGET + PGA_AGGREGATE_TARGET to MEMORY_MAX_TARGET.  

  

·                If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET). 

·                If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)) and will auto-tune subcomps. 

·                If neither is set, they will be auto-tuned without any minimum or default values. We will have a policy of distributing the total memory set by memory_target parameter in a fixed ratio to the the SGA and PGA during initialization. The policy is to give 60% for sga and 40% for PGA at startup. 

• If MEMORY_TARGET is not set or set to set to 0 explicitly (default value is 0 for 11g): 

·                If SGA_TARGET is set we will only auto-tune the sizes of the sub-components of the SGA. PGA will be autotuned independent of whether it is explicitly set or not. Though the whole SGA(SGA_TARGET) and the PGA(PGA_AGGREGATE_TARGET) will not be auto-tuned, i.e., will not grow or shrink automatically. 

·                If neither SGA_TARGET nor PGA_AGGREGATE_TARGET is set, we will follow the same policy as we have today; PGA will be auto-tuned and the SGA will not be auto-tuned and parameters for some of the sub-components will have to be set explicitly (for SGA_TARGET).

·                If only MEMORY_MAX_TARGET is set, MEMORY_TARGET will default to 0 and we will not auto tune sga and pga. It will default to 10gR2 behavior within sga and pga. 

·                If sga_max_size is not user set, we will internally set it to MEMORY_MAX_TARGET. 

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.


If you wish to monitor the decisions made by Automatic Memory Management following views can be useful

·                V$MEMORY_DYNAMIC_COMPONENTS has the current status of all memorycomponents

·                V$MEMORY_RESIZE_OPS has a circular history buffer of the last 800 SGA resize requests