MEMORY_TARGET parameter in Oracle11g

MEMORY_TARGET,MEMORY_MAX_TARGET parameters are introduced in oracle 11g. Using these parameters, you can manage SGA and PGA together rather than managing them separately (using SGA_TARGET, SGA_MAX_SIZE , PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g).

Before 11g we used SGA_TARGET and SGA_MAX_SIZE parameters to dynamically size SGA components such as SHARED_POOL_SIZE,LARGE_POOL_SIZE,JAVA_POOL_SIZE. PGA was controlled separately using GA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY in 10g.

From 11g, if we are using MEMORY_TARGET parameter, SGA and PGA sizes are controlled dynamically using this parameter. If you set SGA_TARGET, SGA_MAX_SIZE and PGA_AGGREGATE_SIZE 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.

The Memory_Max_Target Parameter

this is similar to sga_max_size. Memory_max_target provides for a maximum size that you can dynamically increase memory_target to. Therefore, within the constraints of memory_max_target, you can easily adjust the overall memory allocated to the database at any given time, dynamically.

Oracle will always configure the memory_max_target parameter if it is not set manually with a derived value. This derived value is set to a value equal to the value of the memory_target parameter. Oracle will set memory_max_target to a value of 0 if memory_target is not set. If memory_target is not set and memory_max_target is set to a value greater than zero, then you can modify memory settings dynamically by changing memory_target, or by dynamically altering the other specific memory parameter settings. The figure below illustrates the auto memory parameter dependency.

For instance :

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).

With this version, oracle has become smart as in exchanging memory between SGA and PGAs. This is a huge achievement.

When starting up, Oracle takes up memory equal to MEMORY_TARGET (or MEMORY_MAX_TARGET if mentioned) from Operating System RAM and manage its reqources within itself.

This feature helps DBA to allocate chunk of memory to a particular instance without worrying about the subcateogary allocations of different components.

Relationship of Memory Parameters