V$PGA_TARGET_ADVICE - Size PGA_AGGREGATE_TARGET with Oracle's Advisor

V$PGA_TARGET_ADVICE

Overview

V$PGA_TARGET_ADVICE contains Oracle’s built-in PGA memory advisor output, showing the estimated performance impact of setting PGA_AGGREGATE_TARGET to a range of values relative to the current setting. Each row represents a simulated target value and projects how many bytes would be processed optimally (in-memory) versus spilled to disk. DBAs use this view to make data-driven decisions about PGA sizing, balancing memory cost against the disk I/O savings from keeping sort and hash operations fully in memory.

View Type: Dynamic Performance View Available Since: Oracle 9i Release 2 Required Privileges: SELECT on V_$PGA_TARGET_ADVICE or SELECT_CATALOG_ROLE or SELECT ANY DICTIONARY

Key Columns

Column	Datatype	Description
PGA_TARGET_FOR_ESTIMATE	NUMBER	Hypothetical PGA_AGGREGATE_TARGET value being evaluated (bytes)
PGA_TARGET_FACTOR	NUMBER	Ratio of PGA_TARGET_FOR_ESTIMATE to the current PGA_AGGREGATE_TARGET; 1.0 = current setting
ADVICE_STATUS	VARCHAR2(3)	ON if the advisor is actively collecting data; OFF if statistics are insufficient
BYTES_PROCESSED	NUMBER	Estimated total bytes processed by all workarea operations at this target
ESTD_EXTRA_BYTES_RW	NUMBER	Estimated extra bytes read/written to disk (temp tablespace I/O) at this target
ESTD_PGA_CACHE_HIT_PERCENTAGE	NUMBER	Estimated percentage of workarea bytes processed in memory (higher = better)
ESTD_OVERALLOC_COUNT	NUMBER	Estimated number of times PGA would be over-allocated at this target; 0 is ideal
CON_ID	NUMBER	Container ID (Oracle 12c+); 0 = non-CDB or CDB root

Essential Queries

Basic Usage

Display the full PGA advisor recommendation set sorted from smallest to largest target:

SELECT
    ROUND(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024, 0)      AS target_mb,
    PGA_TARGET_FACTOR                                     AS factor,
    ADVICE_STATUS,
    ROUND(BYTES_PROCESSED          / 1024 / 1024 / 1024, 2) AS bytes_processed_gb,
    ROUND(ESTD_EXTRA_BYTES_RW      / 1024 / 1024, 0)     AS extra_disk_rw_mb,
    ESTD_PGA_CACHE_HIT_PERCENTAGE                         AS cache_hit_pct,
    ESTD_OVERALLOC_COUNT                                  AS overalloc_count
FROM
    V$PGA_TARGET_ADVICE
ORDER BY
    PGA_TARGET_FOR_ESTIMATE;

Monitoring Query

Identify the minimum PGA target that achieves zero over-allocations and the highest practical cache hit percentage — the two key sizing objectives:

SELECT
    ROUND(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024, 0)      AS target_mb,
    PGA_TARGET_FACTOR                                     AS factor,
    ESTD_PGA_CACHE_HIT_PERCENTAGE                         AS cache_hit_pct,
    ESTD_OVERALLOC_COUNT                                  AS overalloc_count,
    ROUND(ESTD_EXTRA_BYTES_RW      / 1024 / 1024, 0)     AS extra_disk_rw_mb,
    CASE
        WHEN ESTD_OVERALLOC_COUNT  = 0
         AND ESTD_PGA_CACHE_HIT_PERCENTAGE >= 90          THEN '*** RECOMMENDED ***'
        WHEN ESTD_OVERALLOC_COUNT  = 0                    THEN 'Acceptable'
        ELSE                                                   'Over-allocated'
    END                                                   AS recommendation
FROM
    V$PGA_TARGET_ADVICE
ORDER BY
    PGA_TARGET_FOR_ESTIMATE;

Combined with Other Views

Combine the advisor with current PGA statistics from V$PGASTAT to show the current baseline alongside the recommendations:

-- Current PGA statistics
SELECT
    'CURRENT'                                             AS scenario,
    ROUND(stat_target.VALUE / 1024 / 1024, 0)            AS target_mb,
    1.0                                                   AS factor,
    stat_hit.VALUE                                        AS cache_hit_pct,
    0                                                     AS overalloc_count,
    NULL                                                  AS extra_disk_rw_mb
FROM
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'aggregate PGA target parameter')  stat_target,
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'cache hit percentage')             stat_hit
UNION ALL
-- Advisor projections
SELECT
    'PROJECTED',
    ROUND(PGA_TARGET_FOR_ESTIMATE / 1024 / 1024, 0),
    PGA_TARGET_FACTOR,
    ESTD_PGA_CACHE_HIT_PERCENTAGE,
    ESTD_OVERALLOC_COUNT,
    ROUND(ESTD_EXTRA_BYTES_RW / 1024 / 1024, 0)
FROM
    V$PGA_TARGET_ADVICE
ORDER BY
    factor;

Advanced Analysis

Generate a formatted PGA sizing report suitable for a change-request document or performance review:

SELECT
    LPAD(ROUND(a.PGA_TARGET_FOR_ESTIMATE / 1024 / 1024, 0), 10)
                                                          AS "Target MB",
    LPAD(a.PGA_TARGET_FACTOR, 8)                         AS "Factor",
    LPAD(a.ESTD_PGA_CACHE_HIT_PERCENTAGE || '%', 12)     AS "Cache Hit%",
    LPAD(ROUND(a.ESTD_EXTRA_BYTES_RW / 1024 / 1024, 0), 14)
                                                          AS "Extra RW MB",
    LPAD(a.ESTD_OVERALLOC_COUNT, 12)                     AS "Over-Allocs",
    CASE
        WHEN a.PGA_TARGET_FACTOR < 1                      THEN 'SMALLER (save memory, more I/O)'
        WHEN a.PGA_TARGET_FACTOR = 1                      THEN '<<< CURRENT SETTING >>>'
        WHEN a.ESTD_OVERALLOC_COUNT > 0                   THEN 'WARNING: over-allocation'
        WHEN a.ESTD_PGA_CACHE_HIT_PERCENTAGE < 80         THEN 'CAUTION: low hit rate'
        WHEN a.ESTD_PGA_CACHE_HIT_PERCENTAGE >= 95
         AND a.ESTD_OVERALLOC_COUNT = 0                   THEN 'OPTIMAL'
        ELSE                                                   'Candidate'
    END                                                   AS "Assessment"
FROM
    V$PGA_TARGET_ADVICE a
ORDER BY
    a.PGA_TARGET_FOR_ESTIMATE;

Detect whether the current PGA target is causing over-allocation — a sign that the setting is too low for peak workloads:

SELECT
    ROUND(p.VALUE / 1024 / 1024, 0)                      AS current_target_mb,
    a.ESTD_PGA_CACHE_HIT_PERCENTAGE                       AS current_cache_hit_pct,
    a.ESTD_OVERALLOC_COUNT                                AS current_overalloc_count,
    ROUND(a.ESTD_EXTRA_BYTES_RW / 1024 / 1024, 0)        AS current_extra_disk_rw_mb,
    CASE
        WHEN a.ESTD_OVERALLOC_COUNT > 0
            THEN 'PGA target too low — increase PGA_AGGREGATE_TARGET'
        WHEN a.ESTD_PGA_CACHE_HIT_PERCENTAGE < 80
            THEN 'Cache hit rate low — consider increasing PGA_AGGREGATE_TARGET'
        ELSE
            'PGA target appears adequate'
    END                                                   AS diagnosis
FROM
    V$PGA_TARGET_ADVICE a
    JOIN V$PARAMETER p ON p.NAME = 'pga_aggregate_target'
WHERE
    a.PGA_TARGET_FACTOR = 1;

Common Use Cases

• Initial PGA sizing — On a newly provisioned database, after a week of representative workload, query this view to find the first target level where ESTD_OVERALLOC_COUNT drops to zero; that is your minimum viable PGA target.
• Memory pressure reduction — When the server is memory-constrained, use the advice rows with PGA_TARGET_FACTOR < 1 to quantify how much additional disk I/O would result from reducing PGA allocation.
• Batch window optimization — Before a large ETL or data warehouse load, temporarily increase PGA_AGGREGATE_TARGET and validate with this view that it would push the cache hit percentage above 95%, eliminating multi-pass sort/hash operations.
• Proactive over-allocation detection — ESTD_OVERALLOC_COUNT > 0 at the current factor (1.0) is a definitive signal that sessions are exceeding the aggregate target, causing unpredictable performance.
• Change request justification — The ESTD_EXTRA_BYTES_RW column quantifies the disk I/O savings from a proposed PGA increase in bytes, providing a measurable metric for change advisory boards.

Related Views

• V$PGASTAT — Current PGA usage statistics including the actual cache hit percentage, aggregate bytes allocated, and optimal/one-pass/multi-pass execution counts.
• V$SQL_WORKAREA_HISTOGRAM — Distribution of workarea sizes across all executions; shows how many operations used optimal, one-pass, or multi-pass memory.
• V$SQL_WORKAREA_ACTIVE — Currently active workarea allocations per session; useful for identifying which SQL statements are consuming PGA right now.
• V$SGA_TARGET_ADVICE — The SGA equivalent of this view; use both together to balance total instance memory between SGA and PGA.
• V$PARAMETER — Check PGA_AGGREGATE_TARGET, PGA_AGGREGATE_LIMIT, and MEMORY_TARGET to understand the current parameter context for this advisor.

Version Notes

• Oracle 9i R2: V$PGA_TARGET_ADVICE introduced alongside the Automatic PGA Memory Management feature. Requires STATISTICS_LEVEL = TYPICAL or ALL to populate.
• Oracle 10g: PGA_AGGREGATE_LIMIT added as an absolute cap; the advisor models stay below this cap in their projections.
• Oracle 11g: Advisor now accounts for parallel query PGA consumption. STATISTICS_LEVEL = BASIC disables the advisor; upgrade to TYPICAL or ALL to re-enable.
• Oracle 12c: In a CDB, each PDB has an independent PGA advisor (CON_ID column added). PGA_AGGREGATE_LIMIT can be set at the PDB level from 12.2 onward.
• Oracle 19c: No structural changes. The advisor remains the primary tool for PGA sizing; Oracle recommends targeting >= 90% cache hit percentage.
• Oracle 21c / 23ai: Machine Learning and vector operation workloads may generate large temporary PGA allocations not well-modeled by historical advisor data; consider setting STATISTICS_LEVEL = ALL during representative ML workload runs to improve advisor accuracy.