Oracle has taken a step ahead in making the memory management simple by introducing Automatic Memory Management (AMM) in 11g , by which Oracle dynamically manages both SGA and PGA.
MEMORY_TARGET and MEMORY_MAX_TARGET are the two initialization parameters related to AMM.
MEMORY_TARGET refers to the shared memory available to Oracle for dynamically managing PGA and SGA. This parameter can be changed dynamically. However, it can be increased only upto a maximum value equals to the MEMORY_MAX_TARGET parameter. By default, the parameter is set to 0 (zero).
MEMORY_MAX_TARGET refers to the maximum size upto which MEMORY_TARGET can be increased dynamically. This is a static parameter. If not set, then Oracle will set the value same as that of MEMORY_TARGET parameter.
Steps to enable Automatic Memory Management:
Step 1: Login to the database as sysdba
Step 2: Set the parameters to enable AMM
SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=1600M SCOPE=SPFILE;
SQL> ALTER SYSTEM SET MEMORY_TARGET=1400M SCOPE=SPFILE;
SQL> ALTER SYSTEM SET SGA_TARGET=0 SCOPE= SPFILE;
SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
Step3: Restart the instance.
As the last step, we have restarted the database, since MEMORY_MAX_TARGET cannot be set dynamically. Also ,SGA_TARGET and PGA_AGGREGATE_TARGET is set to zero in order to allow Oracle to tune SGA and instance PGA without any restriction.
Research and Article by : Anju