Configuring Automatic Memory Management (AMM) in Oracle11g

~ Configuring Automatic Memory Management (AMM)
~ Applicable to : Oracle Database 11g

-- Using DBCA
 When creating the database manually, simply set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database using DBCA

-- Reconfigure AMM if not configured

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;

-- Bounce the database

Note: Create pfile before all change. So that you can use your earlier setups.