Monitor PGA Aggregate Target with V$PGASTAT in Oracle

calendar Jun 18, 2026 / · 6 min read · Oracle DBA V$PGASTAT PGA Memory Management Oracle Performance Oracle Administration Dynamic Performance Views Instance Tuning  ·
Share on: linkedin copy

Monitor PGA Aggregate Target with V$PGASTAT in Oracle

Purpose

Is PGA_AGGREGATE_TARGET set high enough for the work the instance actually does? V$PGASTAT answers that question. It is the instance-level statistics view for the Program Global Area — the private memory Oracle hands to each server process for sorting, hashing, and bitmap operations. Where a per-session PGA view tells you what one connection is using, V$PGASTAT rolls every process into one set of instance-wide numbers: the target you asked for, the bytes actually allocated, the high-water mark, and the cache hit percentage that says whether work-area operations are running in memory or spilling to temp.

A DBA reads this view to size memory. If the cache hit percentage sits low and processes are spilling sorts to the temporary tablespace, the target is too small for the workload and queries are paying a disk penalty they should not. If allocated PGA never approaches the target, memory is reserved that could be given to the buffer cache instead. One query against V$PGASTAT shows both conditions.

This post covers the key statistic rows the view returns, how to read the cache hit percentage, and how V$PGASTAT relates to the PGA_AGGREGATE_TARGET parameter that drives automatic PGA memory management.

Code

 1-- The headline PGA statistics, formatted for reading
 2SELECT name,
 3       CASE
 4         WHEN unit = 'bytes'
 5           THEN ROUND(value / 1024 / 1024, 1) || ' MB'
 6         ELSE TO_CHAR(value)
 7       END AS value
 8FROM   v$pgastat
 9WHERE  name IN ('aggregate PGA target parameter',
10                'total PGA inuse',
11                'total PGA allocated',
12                'maximum PGA allocated',
13                'over allocation count',
14                'cache hit percentage');
15
16-- The full view, every statistic row
17SELECT name, value, unit
18FROM   v$pgastat
19ORDER  BY name;
20
21-- The parameter that drives it
22SHOW PARAMETER pga_aggregate_target

Code Breakdown

The formatted query

The first query pulls the statistics a DBA looks at most and converts the byte values into megabytes so they are readable at a glance.

  • aggregate PGA target parameter — the current value of PGA_AGGREGATE_TARGET, the soft limit Oracle aims to keep total PGA under.
  • total PGA inuse — bytes currently held by active work areas and session memory right now.
  • total PGA allocated — bytes Oracle has allocated, including memory not actively in use but not yet released back.
  • maximum PGA allocated — the high-water mark since instance startup, the most PGA the instance has ever held at once.
  • over allocation count — how many times Oracle had to allocate beyond the target because the work could not be done within it. A rising count is the clearest signal the target is too small.
  • cache hit percentage — the share of work-area operations that ran fully in memory rather than spilling one or more passes to disk.

The full-view query

The second query returns every row in V$PGASTAT with its raw value and unit. Beyond the headline rows it includes the count of optimal, one-pass, and multi-pass work-area executions, which break the cache hit percentage down into detail: optimal executions ran entirely in memory, one-pass spilled once, multi-pass spilled repeatedly and are the most expensive.

The parameter check

SHOW PARAMETER pga_aggregate_target reports the configured target. A non-zero value enables automatic PGA memory management: Oracle distributes that budget across active work areas automatically, growing and shrinking each one based on demand. On instances that use the broader MEMORY_TARGET for automatic memory management, PGA_AGGREGATE_TARGET acts as a minimum rather than a fixed size.

Key Points

  • V$PGASTAT is instance-wide. It aggregates every server process, unlike a per-session PGA view that shows one connection at a time.
  • Cache hit percentage is the headline health number. A high value means work areas run in memory; a low value means sorts and hashes are spilling to the temporary tablespace.
  • over allocation count flags undersizing. Any sustained increase means the workload needs more PGA than the target allows, and Oracle is exceeding it to cope.
  • The target is a soft limit. Oracle treats PGA_AGGREGATE_TARGET as a goal, not a hard ceiling — it can and will exceed it temporarily under pressure.
  • PGA_AGGREGATE_LIMIT is the hard cap. On 12c and later a separate parameter sets the real upper bound at which Oracle terminates the largest offending calls.

Insights and Best Practices

Read the hit percentage against the over-allocation count together

Neither number means much alone. A cache hit percentage in the high 90s with an over-allocation count of zero means the target comfortably covers the workload. A hit percentage that drops during peak hours alongside a climbing over-allocation count is the textbook signal to raise PGA_AGGREGATE_TARGET. Capture both on a schedule so you can see the trend rather than a single point.

Size from the high-water mark, not the average

The maximum PGA allocated row records the worst case the instance has actually handled. Sizing the target only to the average leaves no headroom for the concurrent large sorts that arrive together at month-end. Use the high-water mark, plus a margin, as the realistic floor for the target.

Use the advice view to predict the effect of a change

Oracle ships a companion view, V$PGA_TARGET_ADVICE, that models the cache hit percentage you would get at several candidate target sizes based on recorded workload. Reading it before you change the parameter turns sizing from guesswork into a prediction: you can see whether doubling the target would actually lift the hit percentage or merely reserve idle memory.

Remember the temporary tablespace is the other half

When work areas spill, they spill to the temporary tablespace. A low cache hit percentage and heavy temp usage are two views of the same shortage. If you cannot raise the PGA target — because total host memory is fixed — make sure the temporary tablespace is on fast storage so the spills cost as little as possible.

When to Use This

  • Deciding whether PGA_AGGREGATE_TARGET is sized correctly for the real workload.
  • Investigating queries that are slow because sorts or hash joins spill to temp.
  • Establishing a baseline of PGA usage before and after a memory change.
  • Confirming automatic PGA memory management is active and the target is non-zero.
  • Feeding a capacity-planning report with instance-level memory high-water marks.

Troubleshooting Common Issues

If the cache hit percentage is low but the over-allocation count is zero, the target may be adequate for total memory yet individual operations are still large — check V$SQL_WORKAREA for the specific statements spilling and consider tuning those queries before adding memory. If total PGA allocated never approaches the target, the instance is over-provisioned for PGA and that memory could move to the buffer cache. If raising PGA_AGGREGATE_TARGET does not improve the hit percentage, consult V$PGA_TARGET_ADVICE — the workload may be dominated by operations too large to fit at any reasonable target, which points to query tuning rather than more memory. If the parameter shows zero, automatic PGA management is off and the instance is using the older manual *_AREA_SIZE parameters; set a target to enable automatic management. Finally, confirm you have SELECT on the fixed view through SELECT_CATALOG_ROLE if the query returns no rows for a non-DBA user.

References

Posts in this series