SGA_MAX_SIZE & SGA_TARGET / MEMORY_TARGET & MEMORY_MAX_TARGET

As and when Oracle release new version of database, DBA gets new bunch of features.Before Oracle 9i, DBA has to maintain the whole bunch of memory parameters like DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, set of PGA parameters *_AREA_SIZE, LOG_BUFFER and so on. Real complexity before 9i is assigning correct values to *_AREA_SIZE (PGA) parameters as these values is not for whole instance; it is for EACH oracle user process(Oracle user process count can grow upto maximum of PROCESSES settings).

In 9i, Oracle introducted a new parameters called PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY which helps users to assign a single value for the whole instance's PGA and Oracle instance itself will do self mangement for PGA memory. In earlier version, DBA has to assign value for _AREA_SIZE parameters which assign equal size for all sessions.

Oracle 10g has gone to next level from 9i wherein it automates the memory management of whole SGA. Instead of DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE and LOG_BUFFER parameter; user has to manage just SGA_TARGET and SGA_MAX_SIZE

Oracle 11g has gone to the next level from 10g of automatic memory management where in the whole instance can be controlled by initialization parameter MEMORY_TARGET and a maximum memory size initialization parameter MEMORY_MAX_TARGET. Oracle Database then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA).

SGA_TARGET and SGA_MAX_SIZE

*SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don’t use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow.

*SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE.SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE.SGA_MAX_SIZE sets the maximum value for sga_target.

*SGA_TAGET is 10G feature used to change the sga size dynamically .it specifies the total amount of SGA memory available to an instance. This feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size are affected.

*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 and 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.

*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 (But sum of SGA_TARGET and PGA_AGGREGATE_TARGET should be less than or equal to MEMORY_TARGET).

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