PGA_AGGREGATE_LIMIT - Set Hard PGA Memory Limit in Oracle 12c+

PGA_AGGREGATE_LIMIT

Overview

PGA_AGGREGATE_LIMIT sets a hard upper bound on the total amount of PGA memory that all Oracle processes can consume simultaneously. Introduced in Oracle 12c, it acts as a safety net above PGA_AGGREGATE_TARGET: while the target is a soft goal that Oracle tries to respect, the limit is enforced unconditionally. When total PGA allocation reaches this limit, Oracle identifies and terminates the calls — or, in extreme cases, the sessions — with the largest PGA footprints to bring total usage back below the ceiling. This protects the operating system from being starved of memory by runaway sort or hash join operations.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: The greater of 2GB or 200% of PGA_AGGREGATE_TARGET (with a floor of 2GB) Valid Range: 0 (disabled) or any value >= 2GB and >= PGA_AGGREGATE_TARGET Available Since: Oracle 12c (12.1.0.1) Modifiable: Yes — SCOPE=BOTH PDB Modifiable: Yes (within CDB limits)

Configuration

Viewing Current Value

-- Check current PGA_AGGREGATE_LIMIT setting
SELECT name, value/1024/1024 AS value_mb, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'pga_aggregate_limit';

-- SPFILE value
SELECT name, value, isspecified
FROM v$spparameter
WHERE name = 'pga_aggregate_limit';

-- View all PGA-related parameters together
SELECT name, value/1024/1024 AS mb, isdefault
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit',
               'workarea_size_policy', 'memory_target')
ORDER BY name;

-- Current total PGA in use vs both limits
SELECT t.value/1024/1024 AS target_mb,
       l.value/1024/1024 AS hard_limit_mb,
       u.value/1024/1024 AS total_allocated_mb,
       ROUND(u.value/t.value*100,1) AS pct_of_target,
       ROUND(u.value/l.value*100,1) AS pct_of_limit
FROM v$pgastat u, v$parameter t, v$parameter l
WHERE u.name = 'total PGA allocated'
  AND t.name = 'pga_aggregate_target'
  AND l.name = 'pga_aggregate_limit';

Setting the Parameter

-- Set hard limit to 3x the aggregate target (conservative headroom)
ALTER SYSTEM SET pga_aggregate_limit = 12G SCOPE=BOTH;

-- Disable the hard limit entirely (0 = no enforcement; use with caution)
ALTER SYSTEM SET pga_aggregate_limit = 0 SCOPE=BOTH;

-- Typical pairing: target + limit
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;
ALTER SYSTEM SET pga_aggregate_limit  = 8G SCOPE=BOTH;

-- Verify the pair is consistent
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit')
ORDER BY name;

Constraint: PGA_AGGREGATE_LIMIT must be >= PGA_AGGREGATE_TARGET. Oracle will not allow a limit smaller than the target. The minimum permitted value (when non-zero) is 2GB.

Tuning Guidance

Recommended Values

The limit should be high enough that normal workload spikes never reach it, while still protecting against genuine runaway memory consumption.

PGA_AGGREGATE_TARGET	Recommended PGA_AGGREGATE_LIMIT
1GB	2GB (minimum; use with caution)
2GB	4GB – 6GB
4GB	8GB – 12GB
8GB	16GB – 24GB
16GB	32GB – 40GB
32GB	48GB – 64GB

General rule: Set PGA_AGGREGATE_LIMIT to 2–3x PGA_AGGREGATE_TARGET. Setting it too close to the target increases the risk of legitimate workloads triggering session kills. Setting it too high reduces its protective value.

How to Size

-- Check the peak PGA usage recorded since instance startup
SELECT name, value/1024/1024 AS mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'aggregate PGA auto target',
    'total PGA inuse',
    'total PGA allocated',
    'maximum PGA allocated',
    'global memory bound'
)
ORDER BY name;

-- Find sessions currently using the most PGA
SELECT s.sid, s.serial#, s.username, s.status,
       ROUND(p.pga_alloc_mem/1024/1024, 1) AS pga_alloc_mb,
       ROUND(p.pga_max_mem/1024/1024, 1) AS pga_peak_mb,
       s.sql_id, s.module
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE p.pga_alloc_mem > 100*1024*1024  -- Sessions using > 100MB PGA
ORDER BY p.pga_alloc_mem DESC
FETCH FIRST 25 ROWS ONLY;

-- Check for SQL statements with large work areas (potential limit triggers)
SELECT sql_id, operation_type,
       ROUND(estimated_optimal_size/1024/1024, 1) AS optimal_mb,
       ROUND(last_memory_used/1024/1024, 1) AS last_used_mb,
       last_execution
FROM v$sql_workarea
WHERE estimated_optimal_size > 500*1024*1024  -- Work areas > 500MB
ORDER BY estimated_optimal_size DESC
FETCH FIRST 20 ROWS ONLY;

What Happens When the Limit Is Reached

When total PGA allocation hits PGA_AGGREGATE_LIMIT, Oracle takes these actions in order:

1. Abort the largest PGA consumers: Oracle first attempts to abort the current SQL call (not the session) of the process with the greatest PGA usage. The session receives ORA-04036.
2. Escalate to session termination: If the total PGA is still above the limit after aborting the call, Oracle terminates the session itself.
3. Background process protection: Background processes (LGWR, DBWR, etc.) are never killed. If they are the top PGA consumers, the next-largest user process is targeted instead.

-- Monitor alert log for ORA-04036 occurrences
-- (run from the OS or via ADRCI)
-- adrci> show alert -tail 100

-- Check for recently terminated sessions (track in audit log)
SELECT os_username, db_username, action_name, extended_timestamp, return_code
FROM unified_audit_trail
WHERE return_code = 4036  -- ORA-04036
  AND extended_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
ORDER BY extended_timestamp DESC;

Monitoring

-- Dashboard: PGA target, limit, and current usage
SELECT
    ROUND(a.value/1024/1024, 0) AS target_mb,
    ROUND(b.value/1024/1024, 0) AS hard_limit_mb,
    ROUND(c.value/1024/1024, 0) AS current_alloc_mb,
    ROUND(d.value/1024/1024, 0) AS peak_alloc_mb,
    ROUND(c.value/b.value*100, 1) AS pct_of_limit,
    CASE WHEN c.value/b.value > 0.85 THEN 'WARNING: > 85% of limit'
         WHEN c.value/b.value > 0.70 THEN 'CAUTION: > 70% of limit'
         ELSE 'OK' END AS status
FROM v$parameter a
CROSS JOIN v$parameter b
CROSS JOIN v$pgastat c
CROSS JOIN v$pgastat d
WHERE a.name = 'pga_aggregate_target'
  AND b.name = 'pga_aggregate_limit'
  AND c.name = 'total PGA allocated'
  AND d.name = 'maximum PGA allocated';

-- Check V$PROCESS for any process currently near PGA limit threshold
SELECT COUNT(*) AS sessions_over_500mb
FROM v$process
WHERE pga_alloc_mem > 500*1024*1024;

-- PGA workarea efficiency (should be mostly optimal)
SELECT operation_type,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       ROUND(optimal_executions / NULLIF(optimal_executions +
             onepass_executions + multipasses_executions, 0) * 100, 1) AS optimal_pct
FROM v$sql_workarea_histogram
WHERE optimal_executions + onepass_executions + multipasses_executions > 0
ORDER BY operation_type;

Common Issues

Issue 1: Legitimate Workloads Triggering ORA-04036 Session Kills

Large ETL jobs, bulk analytical queries, or parallel query operations may legitimately require PGA beyond the limit, causing unexpected session termination.

Resolution: Increase both PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT proportionally. Identify the specific SQL causing high PGA usage and either optimize it or provide more memory.

-- Identify the top PGA-consuming SQL in the library cache
SELECT sql_id,
       ROUND(SUM(last_memory_used)/1024/1024, 1) AS total_last_used_mb,
       ROUND(SUM(estimated_optimal_size)/1024/1024, 1) AS total_optimal_mb,
       COUNT(*) AS workarea_count
FROM v$sql_workarea
GROUP BY sql_id
ORDER BY SUM(last_memory_used) DESC
FETCH FIRST 15 ROWS ONLY;

Issue 2: PGA_AGGREGATE_LIMIT Defaults to 2GB When PGA_AGGREGATE_TARGET Is Small

If PGA_AGGREGATE_TARGET is set to, say, 512MB, the default PGA_AGGREGATE_LIMIT (200% of target = ~1GB, but floor of 2GB applies) may be set too close to real-world peak usage on an active system.

Resolution: Explicitly set PGA_AGGREGATE_LIMIT to a value that reflects actual peak demand observed via maximum PGA allocated in V$PGASTAT.

-- Check maximum PGA ever allocated since instance startup
SELECT value/1024/1024 AS peak_pga_mb
FROM v$pgastat
WHERE name = 'maximum PGA allocated';

-- Set limit to peak + 50% buffer
-- e.g., if peak is 3GB: ALTER SYSTEM SET pga_aggregate_limit = 5G SCOPE=BOTH;

Issue 3: Disabling the Limit (Setting to 0) on Memory-Constrained Servers

Setting PGA_AGGREGATE_LIMIT = 0 removes all enforcement. On servers with limited RAM, this can allow Oracle processes to consume memory needed by the OS, leading to OOM kills of Oracle processes by the OS kernel.

Resolution: Never set PGA_AGGREGATE_LIMIT = 0 on production systems unless you have a specific, well-tested reason. Keep the limit enabled and sized generously rather than disabling it.

Related Parameters

• PGA_AGGREGATE_TARGET — The soft target that PGA_AGGREGATE_LIMIT enforces; limit must be >= target
• SGA_TARGET — SGA budget; total memory = SGA_TARGET + PGA_AGGREGATE_LIMIT must stay within available RAM
• MEMORY_TARGET — Full AMM mode; PGA_AGGREGATE_LIMIT still applies as a safety net even under AMM
• SGA_MAX_SIZE — Analogous ceiling for SGA

Related Errors

• ORA-04036: PGA Memory Limit Exceeded — Direct result of hitting PGA_AGGREGATE_LIMIT; session is killed by Oracle to protect the OS

Version Notes

Version	Notes
Oracle 12c (12.1.0.1)	PGA_AGGREGATE_LIMIT introduced
Oracle 12c R2	PDB-level PGA_AGGREGATE_LIMIT supported; CDB limit acts as ceiling for all PDBs
Oracle 18c	Default calculation (200% of PGA_AGGREGATE_TARGET, floor 2GB) unchanged
Oracle 19c	No functional changes; remains a best practice for production systems
Oracle 21c / 23ai	Behavior unchanged; continues to protect against runaway PGA consumption