SGA_TARGET - Configure Automatic SGA Memory Management in Oracle

SGA_TARGET

Overview

SGA_TARGET enables Automatic Shared Memory Management (ASMM), allowing Oracle to automatically distribute memory between the major SGA components — the buffer cache, shared pool, large pool, Java pool, and streams pool — without requiring individual pool sizes to be set manually. When set to a non-zero value, Oracle continuously monitors component usage and reallocates memory between auto-tuned components to meet workload demand. This is the recommended memory management mode for most Oracle 10g through 18c installations that are not using full AMM (MEMORY_TARGET).

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 0 (ASMM disabled) Valid Range: 0 to OS-dependent maximum Available Since: Oracle 10g Modifiable: Yes — SCOPE=BOTH (SPFILE + running instance) PDB Modifiable: Yes (within CDB limits)

Configuration

Viewing Current Value

-- Check current SGA_TARGET setting and status
SELECT name, value, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'sga_target';

-- Check SPFILE value (what will be used on next startup)
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'sga_target';

-- View all current SGA component allocations
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,
       last_oper_type, last_oper_mode
FROM v$sga_dynamic_components
ORDER BY current_size DESC;

-- Summary of total SGA allocation
SELECT name, value/1024/1024 AS value_mb
FROM v$sga
ORDER BY value DESC;

Setting the Parameter

-- Enable ASMM with a specific SGA size (e.g., 4GB)
ALTER SYSTEM SET sga_target = 4G SCOPE=BOTH;

-- Disable ASMM (reverts to manually-specified component sizes)
ALTER SYSTEM SET sga_target = 0 SCOPE=BOTH;

-- Set in SPFILE only (takes effect on next restart)
ALTER SYSTEM SET sga_target = 8G SCOPE=SPFILE;

-- Increase SGA_TARGET dynamically (cannot exceed SGA_MAX_SIZE)
ALTER SYSTEM SET sga_target = 6G SCOPE=BOTH;

Tuning Guidance

Recommended Values

Environment	Typical SGA_TARGET
Small DB (< 50 users, OLTP)	512MB – 2GB
Medium DB (50-500 users, mixed)	2GB – 8GB
Large DB (500+ users, OLTP)	8GB – 32GB
Data Warehouse / Reporting	16GB – 128GB+
Oracle 19c+ with ASMM	20–40% of total RAM

For OLTP workloads, the buffer cache typically benefits most from available SGA memory. For data warehouse workloads with large sort and hash join operations, consider a larger PGA rather than a larger SGA.

How to Size

Use Oracle’s built-in advisory view V$SGA_TARGET_ADVICE to determine the optimal value. This view models the estimated cache hit ratio at different SGA_TARGET sizes.

-- Query SGA_TARGET advisor for sizing recommendations
SELECT sga_size_mb AS sga_target_mb,
       sga_size_factor,
       estd_db_time,
       estd_db_time_factor,
       estd_physical_reads
FROM v$sga_target_advice
ORDER BY sga_size_mb;

Look for the point where estd_db_time_factor flattens out — increasing SGA_TARGET beyond that point yields diminishing returns.

-- Identify which SGA components are hitting their minimum (memory pressure)
SELECT component, current_size/1024/1024 AS current_mb,
       min_size/1024/1024 AS min_mb,
       CASE WHEN current_size = min_size THEN 'AT MINIMUM - PRESSURE'
            ELSE 'OK' END AS pressure_status
FROM v$sga_dynamic_components
WHERE min_size > 0
ORDER BY component;

-- Check SGA resize operations history
SELECT component, oper_type, oper_mode,
       initial_size/1024/1024 AS initial_mb,
       final_size/1024/1024 AS final_mb,
       start_time, end_time, status
FROM v$sga_resize_ops
ORDER BY start_time DESC
FETCH FIRST 20 ROWS ONLY;

Sizing Formula

A common starting point:

SGA_TARGET = (Total RAM * 0.40) for dedicated server OLTP
SGA_TARGET = (Total RAM * 0.60) for dedicated server data warehouse
SGA_TARGET = (Total RAM * 0.25) for shared server or mixed workloads

Always ensure SGA_TARGET <= SGA_MAX_SIZE and leave sufficient memory for the OS, PGA, and other processes.

Monitoring

-- Check if SGA_TARGET is actively tuning components
SELECT component,
       current_size/1024/1024 AS current_mb,
       last_oper_type,
       last_oper_mode,
       last_oper_time
FROM v$sga_dynamic_components
WHERE last_oper_type != 'STATIC'
ORDER BY last_oper_time DESC NULLS LAST;

-- Monitor for ORA-04031 (shared pool memory pressure)
SELECT pool, name, bytes/1024/1024 AS mb
FROM v$sgastat
WHERE pool = 'shared pool'
  AND name IN ('free memory', 'library cache', 'row cache')
ORDER BY bytes DESC;

-- Check current total SGA usage vs target
SELECT a.value/1024/1024 AS sga_target_mb,
       b.value/1024/1024 AS sga_max_size_mb,
       ROUND(a.value/b.value*100, 1) AS pct_of_max
FROM v$parameter a, v$parameter b
WHERE a.name = 'sga_target'
  AND b.name = 'sga_max_size';

Common Issues

Issue 1: SGA_TARGET Cannot Exceed SGA_MAX_SIZE

If you attempt to set SGA_TARGET to a value larger than SGA_MAX_SIZE, the command will fail with an error. SGA_MAX_SIZE is a static parameter and cannot be changed without a restart.

Resolution: Either set SGA_TARGET within the current SGA_MAX_SIZE limit, or update SGA_MAX_SIZE in the SPFILE and restart the instance.

-- Verify the current ceiling
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('sga_target', 'sga_max_size')
ORDER BY name;

-- Increase SGA_MAX_SIZE (requires restart)
ALTER SYSTEM SET sga_max_size = 16G SCOPE=SPFILE;
-- Then restart and set SGA_TARGET

Issue 2: Manually-Pinned Component Sizes Prevent Auto-Tuning

If you have set DB_CACHE_SIZE, SHARED_POOL_SIZE, or other component parameters to explicit non-zero values, those values act as minimums under ASMM and reduce the memory Oracle can freely redistribute.

Resolution: Set manually-specified component sizes to 0 to allow full auto-tuning, or set them only as lower bounds if you need guaranteed minimums.

-- Check for manually-pinned SGA components
SELECT name, value/1024/1024 AS specified_mb
FROM v$parameter
WHERE name IN ('db_cache_size','shared_pool_size','large_pool_size',
               'java_pool_size','streams_pool_size')
  AND value > 0;

-- Remove a manual pin (allow Oracle to auto-size)
ALTER SYSTEM SET shared_pool_size = 0 SCOPE=BOTH;

Issue 3: ASMM Disabled When MEMORY_TARGET Is Set

If MEMORY_TARGET is set to a non-zero value, Oracle uses full AMM and ignores SGA_TARGET for auto-tuning purposes. This can cause confusion when both parameters are set.

Resolution: Use either AMM (MEMORY_TARGET) or ASMM (SGA_TARGET), not both. On Linux systems where /dev/shm is undersized, prefer ASMM over AMM.

-- Check which memory management mode is active
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('memory_target', 'memory_max_target', 'sga_target', 'pga_aggregate_target')
ORDER BY name;

Related Parameters

• SGA_MAX_SIZE — Sets the maximum ceiling for SGA; SGA_TARGET cannot exceed this value
• PGA_AGGREGATE_TARGET — Controls the PGA memory complement to SGA_TARGET under ASMM
• MEMORY_TARGET — Full AMM alternative; when set, takes precedence over SGA_TARGET
• PGA_AGGREGATE_LIMIT — Hard ceiling on total PGA consumption

Related Errors

• ORA-04031: Unable to Allocate Shared Memory — Insufficient shared pool or other SGA component; often resolved by increasing SGA_TARGET
• ORA-00845: MEMORY_TARGET Not Supported — Occurs when using AMM on Linux with undersized /dev/shm; switch to ASMM (SGA_TARGET) as a workaround

Version Notes

Version	Notes
Oracle 10g	ASMM and SGA_TARGET introduced
Oracle 11g	Full AMM (MEMORY_TARGET) introduced; SGA_TARGET still preferred on Linux
Oracle 12c+	PDB-level SGA_TARGET supported within CDB memory limits
Oracle 19c+	No functional changes; ASMM remains the recommended mode on Linux
Oracle 21c / 23ai	SGA_TARGET behavior unchanged; compatible with all current releases