MEMORY_TARGET - Enable Automatic Memory Management (AMM) in Oracle

MEMORY_TARGET

Overview

MEMORY_TARGET enables Automatic Memory Management (AMM), Oracle’s most automated memory management mode where the database engine manages the total memory budget across both the SGA and PGA together. When set to a non-zero value, Oracle continuously monitors SGA and PGA demand across all sessions and redistributes memory between the two to minimize response time, without any manual intervention. MEMORY_TARGET essentially absorbs the roles of both SGA_TARGET and PGA_AGGREGATE_TARGET into a single parameter. While AMM is convenient, it has important OS-level prerequisites — particularly on Linux — and is not universally recommended for all production deployments.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 0 (AMM disabled) Valid Range: 0 to MEMORY_MAX_TARGET Available Since: Oracle 11g Modifiable: Yes — SCOPE=BOTH (within MEMORY_MAX_TARGET ceiling) PDB Modifiable: No (CDB-level parameter only)

Configuration

Viewing Current Value

-- Current MEMORY_TARGET and MEMORY_MAX_TARGET settings
SELECT name, value/1024/1024 AS value_mb, isdefault, ismodified, description
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target')
ORDER BY name;

-- SPFILE values
SELECT name, value, isspecified
FROM v$spparameter
WHERE name IN ('memory_target', 'memory_max_target', 'sga_target', 'pga_aggregate_target')
ORDER BY name;

-- Full memory management mode picture
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target', 'sga_target',
               'sga_max_size', 'pga_aggregate_target', 'pga_aggregate_limit')
ORDER BY name;

-- Current AMM distribution between SGA and PGA
SELECT component, current_size/1024/1024 AS current_mb,
       min_size/1024/1024 AS min_mb,
       max_size/1024/1024 AS max_mb
FROM v$memory_dynamic_components
ORDER BY current_size DESC;

Setting the Parameter

-- Enable AMM (MEMORY_MAX_TARGET must be set first if increasing ceiling)
-- Step 1: Set the ceiling in SPFILE (requires restart if increasing)
ALTER SYSTEM SET memory_max_target = 16G SCOPE=SPFILE;

-- Step 2: Set MEMORY_TARGET (can be dynamic up to MEMORY_MAX_TARGET)
ALTER SYSTEM SET memory_target = 12G SCOPE=BOTH;

-- Zero out SGA_TARGET and PGA_AGGREGATE_TARGET when using AMM
-- (Oracle ignores them under AMM, but zeroing avoids confusion)
ALTER SYSTEM SET sga_target = 0 SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target = 0 SCOPE=BOTH;

-- Disable AMM (revert to ASMM + APMM)
ALTER SYSTEM SET memory_target = 0 SCOPE=BOTH;
ALTER SYSTEM SET sga_target = 8G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

Important: MEMORY_MAX_TARGET is a static parameter — increasing it requires an instance restart. MEMORY_TARGET can be changed dynamically but cannot exceed MEMORY_MAX_TARGET.

Linux /dev/shm Requirement

On Linux, AMM uses POSIX shared memory files in /dev/shm. The size of /dev/shm must be at least as large as MEMORY_TARGET (and ideally as large as MEMORY_MAX_TARGET). If /dev/shm is undersized, Oracle startup fails with ORA-00845.

# Check current /dev/shm size (run as OS user)
df -h /dev/shm

# Temporarily resize /dev/shm (lost on reboot)
mount -o remount,size=16G /dev/shm

# Permanently resize: edit /etc/fstab
# Change or add this line:
# tmpfs  /dev/shm  tmpfs  defaults,size=16g  0  0
# Then: mount -o remount /dev/shm

-- Verify MEMORY_TARGET fits within /dev/shm
-- (Compare output of df -h /dev/shm against this query)
SELECT value/1024/1024 AS memory_target_mb
FROM v$parameter
WHERE name = 'memory_target';

Tuning Guidance

Recommended Values

Environment	Total RAM	Recommended MEMORY_TARGET
Development / Test	8GB	4GB – 6GB
Small production OLTP	16GB	8GB – 12GB
Medium production	32GB	16GB – 24GB
Large production	64GB+	30–50% of total RAM

Leave at least 20–25% of total RAM for the OS, network buffers, and non-Oracle processes.

AMM vs ASMM: When to Use Each

Use AMM (MEMORY_TARGET) when:

• Running on Windows (no /dev/shm limitation)
• Development or test environments where simplicity is valued
• Workloads with highly variable SGA/PGA balance (shifts between OLTP and batch)
• Smaller databases where total memory < 8GB

Prefer ASMM (SGA_TARGET + PGA_AGGREGATE_TARGET) when:

• Running on Linux (avoid /dev/shm sizing issues)
• Oracle RAC (AMM is not supported in RAC)
• Exadata (AMM is not supported)
• Large memory systems (> 32GB) where Oracle’s AMM rebalancing can cause latency spikes
• When HugePages are required (HugePages are incompatible with AMM on Linux)

-- Check if running on RAC (AMM not supported in RAC)
SELECT value FROM v$parameter WHERE name = 'cluster_database';

-- Check HugePages usage (incompatible with AMM on Linux)
-- Run this from OS: grep -i huge /proc/meminfo

How to Size MEMORY_TARGET

Use the V$MEMORY_TARGET_ADVICE view to model optimal memory allocation:

-- AMM memory advisory
SELECT memory_size_mb AS total_memory_mb,
       memory_size_factor,
       estd_db_time,
       estd_db_time_factor
FROM v$memory_target_advice
ORDER BY memory_size_mb;

Look for the point where estd_db_time_factor approaches 1.0 — increasing beyond that point provides minimal benefit.

-- Review current actual distribution to validate sizing
SELECT component,
       current_size/1024/1024 AS current_mb,
       min_size/1024/1024 AS min_mb,
       max_size/1024/1024 AS max_mb,
       user_specified_size/1024/1024 AS user_specified_mb
FROM v$memory_dynamic_components
ORDER BY current_size DESC;

-- How AMM is currently balancing SGA vs PGA
SELECT 'SGA' AS pool,
       SUM(current_size)/1024/1024 AS current_mb
FROM v$sga_dynamic_components
UNION ALL
SELECT 'PGA',
       SUM(p.value)/1024/1024
FROM v$pgastat p
WHERE p.name = 'total PGA allocated';

Monitoring

-- Track MEMORY_TARGET utilization
SELECT t.value/1024/1024 AS target_mb,
       mx.value/1024/1024 AS max_target_mb,
       ROUND(t.value/mx.value*100, 1) AS pct_of_max
FROM v$parameter t, v$parameter mx
WHERE t.name = 'memory_target'
  AND mx.name = 'memory_max_target';

-- Monitor AMM resize operations
SELECT component, oper_type, oper_mode,
       initial_size/1024/1024 AS from_mb,
       final_size/1024/1024 AS to_mb,
       start_time, end_time, status
FROM v$memory_resize_ops
ORDER BY start_time DESC
FETCH FIRST 30 ROWS ONLY;

-- Alert if AMM is frequently resizing (indicates memory pressure)
SELECT COUNT(*) AS resize_ops_last_hour,
       SUM(CASE WHEN status = 'COMPLETE' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN status = 'ERROR' THEN 1 ELSE 0 END) AS errors
FROM v$memory_resize_ops
WHERE start_time > SYSDATE - 1/24;

-- Compare AMM advisory to current setting
SELECT memory_size_mb, estd_db_time_factor,
       CASE WHEN memory_size_factor = 1 THEN '<-- Current setting'
            WHEN estd_db_time_factor = (SELECT MIN(estd_db_time_factor)
                                        FROM v$memory_target_advice)
            THEN '<-- Optimal'
            ELSE NULL END AS note
FROM v$memory_target_advice
ORDER BY memory_size_mb;

Common Issues

Issue 1: ORA-00845 on Linux — /dev/shm Too Small

The most common AMM problem on Linux. Oracle uses POSIX shared memory backed by /dev/shm. If MEMORY_TARGET exceeds the available /dev/shm size, startup fails.

Resolution: Resize /dev/shm or switch from AMM to ASMM.

-- Diagnose: check what MEMORY_TARGET is set to
SELECT value/1024/1024 AS memory_target_mb
FROM v$spparameter
WHERE name = 'memory_target';

-- Workaround: switch to ASMM instead of AMM
ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 0 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 8G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=SPFILE;
-- Then restart

Issue 2: AMM Not Supported in RAC

Oracle RAC does not support AMM. Attempting to use MEMORY_TARGET in a RAC environment results in an error at startup or the parameter being silently ignored (depending on release).

Resolution: Use ASMM (SGA_TARGET) and APMM (PGA_AGGREGATE_TARGET) in all RAC environments.

-- Confirm cluster_database status
SELECT value FROM v$parameter WHERE name = 'cluster_database';
-- If TRUE, do not use MEMORY_TARGET

-- Safe memory config for RAC
ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 10G SCOPE=SPFILE SID='*';
ALTER SYSTEM SET pga_aggregate_target = 3G SCOPE=SPFILE SID='*';

Issue 3: HugePages Incompatibility on Linux

Linux HugePages (large pages) provide significant performance benefits for large SGA allocations, but they are incompatible with POSIX shared memory used by AMM. If HugePages are configured in /etc/sysctl.conf, AMM cannot use them, negating the performance advantage.

Resolution: Disable AMM and use ASMM with HugePages. The SGA will be mapped to HugePages; PGA remains in regular 4KB pages.

-- Confirm HugePages are configured (from OS): grep -i huge /proc/meminfo

-- Disable AMM to allow HugePages usage
ALTER SYSTEM SET memory_target = 0 SCOPE=SPFILE;
ALTER SYSTEM SET memory_max_target = 0 SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 12G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=SPFILE;
-- Restart instance; SGA will use HugePages automatically

Related Parameters

• SGA_TARGET — ASMM alternative to AMM; preferred on Linux; must be 0 when AMM is active
• PGA_AGGREGATE_TARGET — PGA management under ASMM; superseded by MEMORY_TARGET in AMM mode
• SGA_MAX_SIZE — Still applies as ceiling for SGA component within AMM total
• PGA_AGGREGATE_LIMIT — Hard PGA ceiling; remains active even when MEMORY_TARGET is set

Related Errors

• ORA-00845: MEMORY_TARGET Not Supported — Linux /dev/shm too small; the primary reason to switch from AMM to ASMM
• ORA-04031: Unable to Allocate Shared Memory — Shared pool or other SGA component exhaustion; can occur under AMM if MEMORY_TARGET is too small

Version Notes

Version	Notes
Oracle 11g	MEMORY_TARGET and AMM introduced; not available in 10g
Oracle 11g R2	AMM behavior refined; V$MEMORY_TARGET_ADVICE available
Oracle 12c	AMM not supported for PDBs; MEMORY_TARGET remains CDB-level only
Oracle 12c R2+	AMM still not supported in RAC or Exadata — use ASMM
Oracle 19c	AMM support unchanged; HugePages incompatibility remains; ASMM recommended for Linux production
Oracle 21c / 23ai	AMM behavior unchanged; documentation continues to recommend ASMM on Linux for performance-sensitive workloads