Skip to content
Oracle DBA Scripts & Database Utilities

V$PGASTAT - Monitor PGA Memory Usage, Cache Hit Ratio & Over-Allocation

V$PGASTAT

Section titled “V$PGASTAT”

Overview

Section titled “Overview”

V$PGASTAT provides aggregate PGA (Program Global Area) memory statistics for the entire Oracle instance, accumulated since startup. It is the primary view for answering questions such as: how much PGA memory is currently allocated, what fraction of sort and hash operations are completing in memory versus spilling to disk, and whether the PGA_AGGREGATE_TARGET is being respected. Unlike V$PGA_TARGET_ADVICE (which is forward-looking), V$PGASTAT reflects what has actually happened since the database started.

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

Key Columns

Section titled “Key Columns”
ColumnDatatypeDescription
NAMEVARCHAR2(64)Statistic name
VALUENUMBERStatistic value
UNITVARCHAR2(12)Unit of measure: bytes, microseconds, or a count
CON_IDNUMBERContainer ID (Oracle 12c+); 0 = non-CDB or CDB root

Key statistic names and their meaning:

Statistic NameMeaning
aggregate PGA target parameterCurrent value of PGA_AGGREGATE_TARGET (bytes)
aggregate PGA auto targetActual working target Oracle uses internally, may differ slightly
total PGA inusePGA memory currently in active use across all sessions
total PGA allocatedTotal PGA memory currently allocated (inuse + overhead)
maximum PGA allocatedHigh-water mark of PGA allocation since instance startup
total freeable PGA memoryPGA memory eligible to be freed back to the OS
process countNumber of processes currently attached to the instance
max processes everMaximum process count since startup
PGA memory freed back to OSCumulative bytes returned to the OS
total PGA used for auto workareasPGA used specifically by automatic workarea operations
maximum PGA used for auto workareasHigh-water mark of automatic workarea PGA usage
total PGA used for manual workareasPGA used by manual (WORK_AREA_SIZE_POLICY=MANUAL) workarea ops
maximum PGA used for manual workareasHigh-water mark of manual workarea PGA usage
over allocation countNumber of times PGA target was exceeded since startup
bytes processedTotal bytes processed by all workarea operations
extra bytes read/writtenBytes spilled to disk (temp tablespace) due to insufficient PGA
cache hit percentagePercent of workarea bytes processed in memory (optimal)
recompute count (total)Number of times the advisor statistics were recomputed

Essential Queries

Section titled “Essential Queries”

Basic Usage

Section titled “Basic Usage”

Display all PGA statistics with formatted values and units:

SELECT
    NAME,
    CASE
        WHEN UNIT = 'bytes'
            THEN ROUND(VALUE / 1024 / 1024, 2) || ' MB'
        WHEN UNIT = 'microseconds'
            THEN VALUE || ' us'
        ELSE
            TO_CHAR(VALUE, 'FM999,999,999,999') || ' ' || UNIT
    END                                                   AS formatted_value,
    UNIT
FROM
    V$PGASTAT
ORDER BY
    NAME;

Monitoring Query

Section titled “Monitoring Query”

PGA health dashboard — the four metrics most relevant to a production DBA in a single query:

SELECT
    ROUND(MAX(CASE NAME WHEN 'total PGA allocated'
                        THEN VALUE END) / 1024 / 1024, 0)  AS total_pga_alloc_mb,
    ROUND(MAX(CASE NAME WHEN 'total PGA inuse'
                        THEN VALUE END) / 1024 / 1024, 0)  AS total_pga_inuse_mb,
    ROUND(MAX(CASE NAME WHEN 'maximum PGA allocated'
                        THEN VALUE END) / 1024 / 1024, 0)  AS max_pga_ever_mb,
    ROUND(MAX(CASE NAME WHEN 'aggregate PGA target parameter'
                        THEN VALUE END) / 1024 / 1024, 0)  AS pga_target_mb,
    MAX(CASE NAME WHEN 'cache hit percentage'
                  THEN VALUE END)                          AS cache_hit_pct,
    MAX(CASE NAME WHEN 'over allocation count'
                  THEN VALUE END)                          AS over_alloc_count,
    ROUND(MAX(CASE NAME WHEN 'extra bytes read/written'
                        THEN VALUE END) / 1024 / 1024, 0)  AS extra_disk_rw_mb,
    ROUND(MAX(CASE NAME WHEN 'bytes processed'
                        THEN VALUE END) / 1024 / 1024 / 1024, 2)
                                                           AS bytes_processed_gb
FROM
    V$PGASTAT;

Combined with Other Views

Section titled “Combined with Other Views”

Compare current PGA consumption against the configured PGA target and PGA limit, and show per-session breakdown of the top PGA consumers:

-- Instance-level PGA summary
SELECT
    'Instance Summary'                                    AS scope,
    ROUND(g.pga_alloc  / 1024 / 1024, 0)                 AS alloc_mb,
    ROUND(g.pga_target / 1024 / 1024, 0)                 AS target_mb,
    ROUND(g.pga_limit  / 1024 / 1024, 0)                 AS limit_mb,
    g.cache_hit_pct,
    g.over_allocs
FROM (
    SELECT
        MAX(CASE NAME WHEN 'total PGA allocated'            THEN VALUE END) AS pga_alloc,
        MAX(CASE NAME WHEN 'aggregate PGA target parameter' THEN VALUE END) AS pga_target,
        MAX(CASE NAME WHEN 'cache hit percentage'           THEN VALUE END) AS cache_hit_pct,
        MAX(CASE NAME WHEN 'over allocation count'          THEN VALUE END) AS over_allocs,
        0                                                                   AS pga_limit
    FROM V$PGASTAT
) g
UNION ALL
-- Top 10 sessions by PGA usage
SELECT
    s.USERNAME || ' (SID=' || s.SID || ')',
    ROUND(s.VALUE / 1024 / 1024, 0),
    NULL,
    NULL,
    NULL,
    NULL
FROM (
    SELECT
        se.SID,
        ss.USERNAME,
        st.VALUE
    FROM
        V$SESSTAT  st
        JOIN V$SESSION    ss ON ss.SID        = st.SID
        JOIN V$STATNAME   sn ON sn.STATISTIC# = st.STATISTIC#
                            AND sn.NAME       = 'session pga memory'
    ORDER BY
        st.VALUE DESC
    FETCH FIRST 10 ROWS ONLY
) s;

Advanced Analysis

Section titled “Advanced Analysis”

Calculate the ratio of optimal to one-pass to multi-pass workarea executions — the key metric for understanding sort and hash join efficiency:

SELECT
    opt.VALUE                                             AS optimal_execs,
    onepass.VALUE                                         AS onepass_execs,
    multi.VALUE                                           AS multipass_execs,
    opt.VALUE + onepass.VALUE + multi.VALUE               AS total_execs,
    CASE
        WHEN opt.VALUE + onepass.VALUE + multi.VALUE = 0
            THEN NULL
        ELSE ROUND(opt.VALUE * 100.0 /
                   (opt.VALUE + onepass.VALUE + multi.VALUE), 1)
    END                                                   AS pct_optimal,
    CASE
        WHEN opt.VALUE + onepass.VALUE + multi.VALUE = 0
            THEN NULL
        ELSE ROUND(multi.VALUE * 100.0 /
                   (opt.VALUE + onepass.VALUE + multi.VALUE), 2)
    END                                                   AS pct_multipass
FROM
    (SELECT VALUE FROM V$PGASTAT
     WHERE  NAME = 'workarea executions - optimal')       opt,
    (SELECT VALUE FROM V$PGASTAT
     WHERE  NAME = 'workarea executions - onepass')       onepass,
    (SELECT VALUE FROM V$PGASTAT
     WHERE  NAME = 'workarea executions - multipass')     multi;

Estimate PGA memory growth trend by comparing current allocation to the historical maximum and free memory:

SELECT
    ROUND(alloc.VALUE    / 1024 / 1024, 0)               AS current_alloc_mb,
    ROUND(maxalloc.VALUE / 1024 / 1024, 0)               AS max_ever_alloc_mb,
    ROUND(freeable.VALUE / 1024 / 1024, 0)               AS freeable_mb,
    ROUND(target.VALUE   / 1024 / 1024, 0)               AS target_mb,
    ROUND((alloc.VALUE - target.VALUE) / 1024 / 1024, 0) AS overage_mb,
    CASE
        WHEN alloc.VALUE > target.VALUE
            THEN 'WARNING: PGA allocation exceeds target'
        WHEN alloc.VALUE > target.VALUE * 0.9
            THEN 'CAUTION: PGA allocation at 90% of target'
        ELSE
            'OK'
    END                                                   AS status
FROM
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated')            alloc,
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'maximum PGA allocated')          maxalloc,
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total freeable PGA memory')      freeable,
    (SELECT VALUE FROM V$PGASTAT WHERE NAME = 'aggregate PGA target parameter') target;

Snapshot PGA statistics into a table for trending over time (suitable for a cron job or DBMS_SCHEDULER job):

-- First, create the snapshot table once:
-- CREATE TABLE dba_pga_snapshots (
--     snap_time         DATE,
--     total_alloc_mb    NUMBER,
--     total_inuse_mb    NUMBER,
--     max_alloc_mb      NUMBER,
--     target_mb         NUMBER,
--     cache_hit_pct     NUMBER,
--     over_alloc_count  NUMBER,
--     extra_disk_rw_mb  NUMBER,
--     bytes_proc_gb     NUMBER
-- );


-- Populate it with this INSERT:
INSERT INTO dba_pga_snapshots (
    snap_time,
    total_alloc_mb,
    total_inuse_mb,
    max_alloc_mb,
    target_mb,
    cache_hit_pct,
    over_alloc_count,
    extra_disk_rw_mb,
    bytes_proc_gb
)
SELECT
    SYSDATE,
    ROUND(MAX(CASE NAME WHEN 'total PGA allocated'            THEN VALUE END) / 1048576, 0),
    ROUND(MAX(CASE NAME WHEN 'total PGA inuse'                THEN VALUE END) / 1048576, 0),
    ROUND(MAX(CASE NAME WHEN 'maximum PGA allocated'          THEN VALUE END) / 1048576, 0),
    ROUND(MAX(CASE NAME WHEN 'aggregate PGA target parameter' THEN VALUE END) / 1048576, 0),
    MAX(CASE NAME  WHEN 'cache hit percentage'                THEN VALUE END),
    MAX(CASE NAME  WHEN 'over allocation count'               THEN VALUE END),
    ROUND(MAX(CASE NAME WHEN 'extra bytes read/written'       THEN VALUE END) / 1048576, 0),
    ROUND(MAX(CASE NAME WHEN 'bytes processed'                THEN VALUE END) / 1073741824, 2)
FROM
    V$PGASTAT;


COMMIT;

Common Use Cases

Section titled “Common Use Cases”
  • Cache hit percentage baseline — Query after a representative business day to establish a normal cache_hit_percentage; sustained values below 80% indicate PGA under-provisioning.
  • Over-allocation detection — A non-zero over_allocation_count since the last instance startup means the PGA target has been exceeded; use V$PGA_TARGET_ADVICE to determine by how much to increase PGA_AGGREGATE_TARGET.
  • Multi-pass operation elimination — The workarea executions - multipass statistic should be zero in a well-tuned system; any non-zero value means sort or hash operations are doing three or more passes over temp data, severely degrading performance.
  • PGA limit enforcement — In Oracle 12c+, PGA_AGGREGATE_LIMIT provides a hard ceiling; compare total PGA allocated against this limit to detect sessions being aborted for exceeding the limit (ORA-04036).
  • Memory fragmentation review — A large gap between total PGA allocated and total PGA inuse (i.e., high freeable PGA memory) suggests memory fragmentation; a database restart or idle session cleanup may help.
  • Workload characterization — Comparing total PGA used for auto workareas vs. manual workareas indicates whether any legacy sessions are still using WORK_AREA_SIZE_POLICY = MANUAL, bypassing automatic management.
Section titled “Related Views”
  • V$PGA_TARGET_ADVICE — Forward-looking advisor showing projected cache hit percentage and over-allocation counts at different PGA target values.
  • V$SQL_WORKAREA_HISTOGRAM — Distribution of workarea executions by size bucket and mode (optimal/onepass/multipass); drills down into the aggregate counts visible in V$PGASTAT.
  • V$SQL_WORKAREA_ACTIVE — Currently active workarea allocations with session and SQL ID; pinpoints which statements are using PGA right now.
  • V$PROCESS — Per-process PGA columns (PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM) provide the per-session detail that V$PGASTAT aggregates.
  • V$SGA — The SGA counterpart; together with V$PGASTAT provides a complete picture of all Oracle instance memory consumption.

Version Notes

Section titled “Version Notes”
  • Oracle 9i R2: V$PGASTAT introduced with Automatic PGA Memory Management. Requires STATISTICS_LEVEL = TYPICAL or ALL; BASIC disables the workarea statistics.
  • Oracle 10g: Additional workarea execution breakdown statistics added (optimal, onepass, multipass counts).
  • Oracle 11g: PGA_AGGREGATE_LIMIT parameter added as a hard cap; corresponding statistics for limit enforcement added to V$PGASTAT.
  • Oracle 12c: CON_ID column added for Multitenant. Each PDB has its own PGA statistics scope; connect to the PDB to see PDB-specific values. PGA_AGGREGATE_LIMIT can be set per-PDB from 12.2.
  • Oracle 19c: No structural changes to V$PGASTAT. Oracle recommends monitoring cache hit percentage via AWR (DBA_HIST_PGASTAT) for trend analysis rather than querying V$PGASTAT directly.
  • Oracle 21c / 23ai: No schema changes. Vector and machine learning workloads may significantly increase PGA demand due to large in-memory dataset operations; ensure PGA_AGGREGATE_TARGET is sized to accommodate peak ML workloads alongside OLTP.