SGA_MAX_SIZE - Set Maximum SGA Memory Limit in Oracle

SGA_MAX_SIZE

Overview

SGA_MAX_SIZE defines the maximum amount of memory that the SGA can ever occupy during the lifetime of an instance. It acts as a hard ceiling: SGA_TARGET can be raised dynamically at runtime, but it can never exceed SGA_MAX_SIZE. Because this is a static parameter, changing it requires an instance restart. Getting this value right at initial configuration avoids unnecessary downtime later when workloads grow and more SGA memory is needed.

Parameter Type: Static (requires restart) Default Value: Automatically set to the initial SGA size at startup (equal to SGA_TARGET if ASMM is used, or the sum of manual component sizes otherwise) Valid Range: Current SGA size to OS-dependent maximum Available Since: Oracle 9i Modifiable: Yes — SCOPE=SPFILE only (requires restart to take effect) PDB Modifiable: No (CDB-level parameter only)

Configuration

Viewing Current Value

-- Current in-memory value
SELECT name, value/1024/1024 AS value_mb, isdefault, description
FROM v$parameter
WHERE name = 'sga_max_size';

-- SPFILE value (what applies on next startup)
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'sga_max_size';

-- Compare SGA_MAX_SIZE against SGA_TARGET and current SGA usage
SELECT p.name, p.value/1024/1024 AS param_mb
FROM v$parameter p
WHERE p.name IN ('sga_max_size', 'sga_target', 'memory_max_target', 'memory_target')
ORDER BY p.name;

-- How much of SGA_MAX_SIZE headroom is currently unused
SELECT a.value AS sga_target,
       b.value AS sga_max_size,
       (b.value - a.value)/1024/1024 AS headroom_mb,
       ROUND(a.value / b.value * 100, 1) AS pct_used
FROM v$parameter a, v$parameter b
WHERE a.name = 'sga_target'
  AND b.name = 'sga_max_size';

Setting the Parameter

-- Set SGA_MAX_SIZE in SPFILE (takes effect after restart)
ALTER SYSTEM SET sga_max_size = 16G SCOPE=SPFILE;

-- Always set SGA_TARGET at the same time (or before restart)
ALTER SYSTEM SET sga_target = 8G SCOPE=SPFILE;

-- Verify both are set consistently before restarting
SELECT name, value/1024/1024 AS mb
FROM v$spparameter
WHERE name IN ('sga_max_size', 'sga_target', 'memory_max_target')
ORDER BY name;

Important: You cannot use SCOPE=BOTH or SCOPE=MEMORY for SGA_MAX_SIZE. It is always SPFILE-only and requires a restart.

Tuning Guidance

Recommended Values

Set SGA_MAX_SIZE to provide headroom above your current SGA_TARGET so you can increase SGA memory dynamically without a restart as workloads grow.

Environment	SGA_TARGET	SGA_MAX_SIZE Recommendation
Small DB	1GB	2GB (2x SGA_TARGET)
Medium DB	4GB	8GB (2x SGA_TARGET)
Large DB	16GB	24GB (1.5x SGA_TARGET)
Data Warehouse	32GB	48GB (1.5x SGA_TARGET)
Very large DB	64GB+	+25–50% headroom

A common rule of thumb is to set SGA_MAX_SIZE to 1.5–2x SGA_TARGET so that you can absorb planned growth or unexpected demand spikes without restarting the instance.

How to Size

-- Review SGA component usage over time to project future needs
SELECT component,
       current_size/1024/1024 AS current_mb,
       max_size/1024/1024 AS max_mb,
       user_specified_size/1024/1024 AS user_specified_mb
FROM v$sga_dynamic_components
ORDER BY max_size DESC;

-- Check if SGA_TARGET is already at the SGA_MAX_SIZE ceiling
SELECT CASE WHEN a.value >= b.value
            THEN 'WARNING: SGA_TARGET equals SGA_MAX_SIZE — no headroom'
            ELSE 'OK: ' || ROUND((b.value - a.value)/1024/1024,0) || ' MB headroom available'
       END AS status
FROM v$parameter a, v$parameter b
WHERE a.name = 'sga_target'
  AND b.name = 'sga_max_size';

-- Project needed SGA_MAX_SIZE based on SGA advisory
SELECT MAX(sga_size)/1024/1024 AS recommended_max_mb
FROM v$sga_target_advice
WHERE estd_db_time_factor <= 1.05;  -- Within 5% of optimal DB time

When to Leave SGA_MAX_SIZE at Default

In some situations, explicitly setting SGA_MAX_SIZE is unnecessary:

• When using full AMM (MEMORY_TARGET): Oracle manages both SGA and PGA. MEMORY_MAX_TARGET serves the same ceiling role. SGA_MAX_SIZE is still respected but less critical.
• When SGA will not grow: If the server is memory-constrained and SGA_TARGET will never need to increase, the default (equal to initial SGA size) is acceptable.
• RAC environments: Each node has its own SGA. Set SGA_MAX_SIZE per-instance using SID='instancename' qualifier to account for node-specific memory.

Monitoring

-- Alert if SGA_TARGET is close to SGA_MAX_SIZE ceiling (within 10%)
SELECT a.value/1024/1024 AS sga_target_mb,
       b.value/1024/1024 AS sga_max_size_mb,
       ROUND((b.value - a.value)/b.value * 100, 1) AS headroom_pct,
       CASE WHEN (b.value - a.value)/b.value < 0.10
            THEN 'ACTION NEEDED: Less than 10% headroom'
            ELSE 'OK' END AS alert
FROM v$parameter a, v$parameter b
WHERE a.name = 'sga_target'
  AND b.name = 'sga_max_size';

-- Track SGA resize history (dynamic resizes within the current max)
SELECT component,
       oper_type,
       initial_size/1024/1024 AS from_mb,
       final_size/1024/1024 AS to_mb,
       start_time
FROM v$sga_resize_ops
ORDER BY start_time DESC
FETCH FIRST 30 ROWS ONLY;

-- Confirm actual SGA memory footprint from the OS perspective
SELECT name, value/1024/1024 AS mb
FROM v$sga
ORDER BY value DESC;

Common Issues

Issue 1: Cannot Increase SGA_TARGET Beyond SGA_MAX_SIZE

Attempting to dynamically increase SGA_TARGET above SGA_MAX_SIZE fails immediately because the hard limit is enforced at runtime.

Resolution: Plan SGA_MAX_SIZE proactively. To increase it, update SPFILE and restart.

-- Diagnose the ceiling conflict
SELECT 'SGA_TARGET' AS param, value/1024/1024 AS mb FROM v$parameter WHERE name = 'sga_target'
UNION ALL
SELECT 'SGA_MAX_SIZE', value/1024/1024 FROM v$parameter WHERE name = 'sga_max_size';

-- Update ceiling in SPFILE for next restart
ALTER SYSTEM SET sga_max_size = 20G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 12G SCOPE=SPFILE;
-- Shutdown and startup to apply

Issue 2: SGA_MAX_SIZE Set Too Low at Installation

If SGA_MAX_SIZE was set too conservatively at build time, the instance cannot absorb future growth without downtime.

Resolution: Schedule a maintenance window to restart with a larger SGA_MAX_SIZE. In RAC, a rolling restart can minimize downtime.

-- Prepare SPFILE changes before the restart window
ALTER SYSTEM SET sga_max_size = 32G SCOPE=SPFILE;
ALTER SYSTEM SET sga_target = 20G SCOPE=SPFILE;

-- Verify SPFILE is correctly set before restarting
SELECT name, value FROM v$spparameter
WHERE name IN ('sga_max_size', 'sga_target')
ORDER BY name;

Issue 3: Conflict with AMM (MEMORY_TARGET)

When MEMORY_TARGET is set, Oracle manages the total memory budget (SGA + PGA). SGA_MAX_SIZE must be <= MEMORY_MAX_TARGET. If SGA_MAX_SIZE is set larger than MEMORY_MAX_TARGET, startup will fail.

Resolution: Ensure SGA_MAX_SIZE <= MEMORY_MAX_TARGET. The safest approach is to not set SGA_MAX_SIZE explicitly when using AMM, allowing Oracle to derive it automatically.

-- Verify consistency across all memory parameters
SELECT name, value/1024/1024 AS mb
FROM v$spparameter
WHERE name IN ('memory_target', 'memory_max_target', 'sga_target', 'sga_max_size', 'pga_aggregate_target')
ORDER BY name;

Related Parameters

• SGA_TARGET — The actual SGA allocation target; must be set <= SGA_MAX_SIZE
• MEMORY_TARGET — Full AMM mode; MEMORY_MAX_TARGET replaces SGA_MAX_SIZE as the ceiling in AMM
• PGA_AGGREGATE_TARGET — PGA counterpart; independent of SGA_MAX_SIZE
• PGA_AGGREGATE_LIMIT — Hard PGA ceiling analogous to SGA_MAX_SIZE for PGA

Related Errors

• ORA-04031: Unable to Allocate Shared Memory — SGA component exhaustion; increasing SGA_MAX_SIZE and SGA_TARGET resolves in many cases
• ORA-27125: Unable to Create Shared Memory Segment — OS-level failure to allocate the shared memory segment; SGA_MAX_SIZE set too large for available OS resources

Version Notes

Version	Notes
Oracle 9i	SGA_MAX_SIZE introduced to support dynamic SGA component resizing
Oracle 10g	SGA_TARGET introduced; SGA_MAX_SIZE becomes the ceiling for ASMM
Oracle 11g	MEMORY_MAX_TARGET introduced for AMM; SGA_MAX_SIZE still applies when AMM is not used
Oracle 12c+	PDB memory management introduced; SGA_MAX_SIZE applies at CDB level
Oracle 19c / 23ai	No functional changes; best practice remains setting SGA_MAX_SIZE 1.5–2x SGA_TARGET