Enable Automatic Memory Management (AMM) in Oracle Database 11g

Enable Automatic Memory Management (AMM) in Oracle Database 11g

AMM Parameters

memory_target (starting in 11g):  If memory_target  is set, then AMM is enabled:  If memory_target is set to non zero value and :sga_target, sga_max_size and pga_aggregate_target are set to 0, then 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, then 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 auto-tuned 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 auto-tuned. The sga_target will be initialized to a value of (memory_target-pga_aggregate_target).

Automatic memory management is configured using two new initialization parameters:

MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".

MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

If you are using UNIX/Linux, before you consider using AMM you should check the current size of your shared memory file system. On Linux you do this by issuing the following command.

# df -k /dev/shm
Filesystem           1K-blocks      Used Available Use% Mounted on
tmpfs                  1029884    350916    678968  35% /dev/shm
#

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and 
MEMORY_MAX_TARGET values, or Oracle will throw the following error.

ORA-00845: MEMORY_TARGET not supported on this system To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.

# umount tmpfs
# mount -t tmpfs shmfs -o size=1200m /dev/shm

Make the setting permanent by amending the "tmpfs" setting of the "/etc/fstab" file to look like this.

tmpfs                   /dev/shm                tmpfs   size=1200m      0 0


Enabling automatic memory management on a system that didn't previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.

MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, "maximum PGA allocated")

The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.

-- Individual values.
COLUMN name FORMAT A30
COLUMN value FORMAT A10

SELECT name, value
FROM   v$parameter
WHERE  name IN ('pga_aggregate_target', 'sga_target')
UNION
SELECT 'maximum PGA allocated' AS name, TO_CHAR(value) AS value
FROM   v$pgastat
WHERE  name = 'maximum PGA allocated';

-- Calculate MEMORY_TARGET

SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target
FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'sga_target') sga,
     (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = 'pga_aggregate_target') pga,
     (SELECT value FROM v$pgastat WHERE name = 'maximum PGA allocated') max_pga;
Assuming our required setting was 5G, we might issue the following statements.

CONN / AS SYSDBA

-- Set the static parameter. Leave some room for possible future growth without restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;

-- Set the dynamic parameters. Assuming Oracle has full control.
ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;
ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

-- Restart instance.
SHUTDOWN IMMEDIATE;
STARTUP;

Once the database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;



Reference: http://www.oracle-base.com/articles/11g/automatic-memory-management-11gr1.php