PGA_AGGREGATE_TARGET - Size Oracle PGA Memory for Optimal Performance

PGA_AGGREGATE_TARGET

Overview

PGA_AGGREGATE_TARGET enables Automatic PGA Memory Management (APMM), instructing Oracle to automatically manage the size of work areas (sort, hash join, bitmap merge) across all dedicated server sessions so that their total PGA usage stays within the specified aggregate target. Without this parameter (or with it set to 0), each session’s SORT_AREA_SIZE and HASH_AREA_SIZE are fixed, leading to either memory waste or excessive disk-based operations. With APMM, Oracle dynamically sizes individual work areas based on current demand, maximizing in-memory operations while respecting the overall memory budget.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: 10MB, or 20% of SGA size (whichever is greater) when SGA_TARGET is set Valid Range: 10MB to 4096GB Available Since: Oracle 9i Modifiable: Yes — SCOPE=BOTH PDB Modifiable: Yes (within CDB limits in 12c+)

Configuration

Viewing Current Value

-- Current PGA_AGGREGATE_TARGET setting
SELECT name, value/1024/1024 AS value_mb, isdefault, ismodified, description
FROM v$parameter
WHERE name = 'pga_aggregate_target';

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

-- Check whether automatic PGA management is active
SELECT name, value
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'workarea_size_policy');

-- Current PGA usage summary across all sessions
SELECT round(pga_used_mem/1024/1024,1) AS used_mb,
       round(pga_alloc_mem/1024/1024,1) AS alloc_mb,
       round(pga_freeable_mem/1024/1024,1) AS freeable_mb,
       round(pga_max_mem/1024/1024,1) AS max_mb,
       sid, username, program
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE s.type = 'USER'
ORDER BY pga_alloc_mem DESC
FETCH FIRST 20 ROWS ONLY;

Setting the Parameter

-- Enable APMM with a 4GB aggregate target
ALTER SYSTEM SET pga_aggregate_target = 4G SCOPE=BOTH;

-- Increase for a large data warehouse workload
ALTER SYSTEM SET pga_aggregate_target = 16G SCOPE=BOTH;

-- Disable automatic PGA management (revert to manual SORT_AREA_SIZE)
ALTER SYSTEM SET pga_aggregate_target = 0 SCOPE=BOTH;
-- Note: setting to 0 also sets workarea_size_policy = MANUAL

-- Confirm workarea_size_policy is AUTO when APMM is active
SELECT name, value FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'workarea_size_policy');

Tuning Guidance

Recommended Values

Environment	Starting Recommendation
OLTP (small work areas)	512MB – 2GB
Mixed OLTP + reporting	2GB – 8GB
Data Warehouse (large sorts/joins)	8GB – 32GB+
Batch processing	4GB – 16GB
Oracle 11g+ general guideline	20% of total RAM for dedicated servers

For OLTP workloads where most operations are index lookups and small DML, PGA needs are modest. For data warehouses with large sorts, hash joins, and bitmap operations, a generous PGA_AGGREGATE_TARGET can dramatically reduce I/O by keeping work areas in memory.

How to Size Using V$PGA_TARGET_ADVICE

Oracle’s PGA advisory models the estimated over-allocation ratio and cache hit percentage at various PGA_AGGREGATE_TARGET settings. Query it while the instance is under representative load.

-- PGA TARGET advisory: find the "knee" of the curve
SELECT pga_target_for_estimate/1024/1024 AS target_mb,
       pga_target_factor,
       estd_pga_cache_hit_percentage,
       estd_overalloc_count,
       estd_extra_bytes_rw/1024/1024 AS extra_disk_io_mb
FROM v$pga_target_advice
ORDER BY pga_target_for_estimate;

Interpreting the results:

• estd_pga_cache_hit_percentage — aim for 95%+; below 80% indicates significant spill-to-disk
• estd_overalloc_count — should be 0; a non-zero value means the target is too small and Oracle is forced to over-allocate
• estd_extra_bytes_rw — disk I/O Oracle would have to perform for sort/hash spills; minimize this

-- Find the minimum PGA_AGGREGATE_TARGET that eliminates over-allocation
SELECT MIN(pga_target_for_estimate)/1024/1024 AS min_safe_target_mb
FROM v$pga_target_advice
WHERE estd_overalloc_count = 0;

-- Current actual PGA statistics (compare against target)
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',
    'total freeable PGA memory',
    'PGA memory freed back to OS'
)
ORDER BY name;

Understanding Work Area Execution Modes

Each SQL operation using PGA memory (sort, hash join, bitmap merge) executes in one of three modes:

-- Check work area operation statistics
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%workarea%'
  OR name LIKE '%sort%'
ORDER BY name;

-- Detailed work area execution mode breakdown
SELECT operation_type,
       optimal_executions,
       onepass_executions,
       multipasses_executions,
       active_time_total
FROM v$sql_workarea_histogram
ORDER BY operation_type;

• Optimal: Work area fits entirely in memory — fastest
• One-pass: Work area must spill to disk once — moderate performance impact
• Multi-pass: Work area spills to disk multiple times — severe performance impact; indicates PGA_AGGREGATE_TARGET is too small

Target: 95%+ optimal, 5% or less one-pass, 0% multi-pass for most workloads.

Monitoring

-- Real-time PGA usage vs target
SELECT a.value/1024/1024 AS pga_target_mb,
       b.value/1024/1024 AS total_pga_allocated_mb,
       c.value/1024/1024 AS total_pga_inuse_mb,
       ROUND(b.value / a.value * 100, 1) AS pct_of_target
FROM v$pgastat a, v$pgastat b, v$pgastat c
WHERE a.name = 'aggregate PGA target parameter'
  AND b.name = 'total PGA allocated'
  AND c.name = 'total PGA inuse';

-- Sessions with highest PGA usage (find memory hogs)
SELECT s.sid, s.serial#, s.username, s.program, 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
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE p.pga_alloc_mem > 50*1024*1024  -- Sessions using > 50MB PGA
ORDER BY p.pga_alloc_mem DESC;

-- Check for excessive sort spills (indicator PGA_AGGREGATE_TARGET too small)
SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (disk)', 'sorts (memory)', 'sorts (rows)')
ORDER BY name;

Common Issues

Issue 1: Excessive Disk Sorts Despite Adequate RAM

If sorts (disk) in V$SYSSTAT is high, or V$PGA_TARGET_ADVICE shows a low cache hit percentage, the PGA_AGGREGATE_TARGET is too small for the workload.

Resolution: Increase PGA_AGGREGATE_TARGET. Use the advisory to find the value where estd_overalloc_count reaches 0.

-- Quick check: ratio of disk sorts to memory sorts
SELECT ds.value AS disk_sorts, ms.value AS memory_sorts,
       ROUND(ds.value / NULLIF(ms.value, 0) * 100, 2) AS disk_sort_pct
FROM v$sysstat ds, v$sysstat ms
WHERE ds.name = 'sorts (disk)'
  AND ms.name = 'sorts (memory)';
-- Disk sort % > 5% warrants investigation

Issue 2: PGA_AGGREGATE_TARGET Set to 0 (Manual Mode Active)

If PGA_AGGREGATE_TARGET is 0, automatic PGA management is disabled and WORKAREA_SIZE_POLICY is set to MANUAL. In this mode, every session uses the same fixed SORT_AREA_SIZE, which is typically very inefficient.

Resolution: Enable APMM by setting a non-zero PGA_AGGREGATE_TARGET.

SELECT name, value FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'workarea_size_policy', 'sort_area_size');

-- Enable APMM
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

Issue 3: PGA Memory Exceeds PGA_AGGREGATE_LIMIT (12c+)

In Oracle 12c and later, if total PGA allocation approaches PGA_AGGREGATE_LIMIT, Oracle will start terminating the sessions with the largest PGA allocations. This often surfaces as unexpected ORA-04036 errors.

Resolution: Increase PGA_AGGREGATE_TARGET and review PGA_AGGREGATE_LIMIT to ensure the hard limit is proportional. See the PGA_AGGREGATE_LIMIT page for details.

-- Check both limits
SELECT name, value/1024/1024 AS mb
FROM v$parameter
WHERE name IN ('pga_aggregate_target', 'pga_aggregate_limit')
ORDER BY name;

Related Parameters

• PGA_AGGREGATE_LIMIT — Hard ceiling on total PGA; must be >= 2x PGA_AGGREGATE_TARGET
• SGA_TARGET — SGA counterpart; together SGA_TARGET + PGA_AGGREGATE_TARGET should stay within total available RAM
• MEMORY_TARGET — Full AMM; manages SGA + PGA together; MEMORY_TARGET replaces PGA_AGGREGATE_TARGET in AMM mode
• SGA_MAX_SIZE — Upper bound for SGA growth

Related Errors

• ORA-04036: PGA Memory Limit Exceeded — Session killed because total PGA exceeded PGA_AGGREGATE_LIMIT; typically resolved by increasing PGA_AGGREGATE_TARGET and PGA_AGGREGATE_LIMIT

Version Notes

Version	Notes
Oracle 9i	PGA_AGGREGATE_TARGET and APMM introduced
Oracle 10g	Advisory (V$PGA_TARGET_ADVICE) improvements; WORKAREA_SIZE_POLICY defaults to AUTO
Oracle 11g R2	Default changed to 20% of SGA when SGA_TARGET is set
Oracle 12c	PGA_AGGREGATE_LIMIT introduced as a hard cap above PGA_AGGREGATE_TARGET
Oracle 12c R2+	PDB-level PGA_AGGREGATE_TARGET supported
Oracle 19c / 23ai	No functional changes; APMM remains default and recommended