Oracle 11g R1下的自动内存管理（AMM）(1)

MEMORY_TARGET=SGA_TARGET+GREATEST(PGA_AGGREGATE_TARGET,"maximumPGAallocated")

-- 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;

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;

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;