Monitor PGA Aggregate Target with V$PGASTAT in Oracle

calendar Jun 18, 2026 / · 10 min read · Instance Tuning Oracle DBA PGA Memory Management Oracle Performance v$pgastat v$sql_workarea_histogram v$pga_target_advice v$sql_workarea  ·
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 (PGA), the private memory that server processes use for work areas. Oracle sizes those work areas for memory-intensive SQL operators such as sort, group-by, hash-join, bitmap merge and bitmap create. Where a per-process view such as V$PROCESS shows one process at a time, V$PGASTAT gives instance-wide numbers: the target you asked for, the bytes allocated now, the high-water mark, and the cache hit percentage that shows how well work areas run in memory.

A DBA reads this view to size memory. If the cache hit percentage is low, work areas are making extra passes over their input data, and queries pay a penalty they should not. If the over allocation count keeps rising, the target is too small for the workload. If allocated PGA never comes near the target, memory is reserved that another part of the instance could use. A few rows from one view show all three conditions.

This post covers the key statistic rows, how to read the cache hit percentage, where the work-area execution counts really live, 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

The next query is an addition to the original script. It reads the optimal, one-pass and multi-pass execution counts, which V$PGASTAT does not hold:

1SELECT low_optimal_size / 1024 AS low_kb,
2       (high_optimal_size + 1) / 1024 AS high_kb,
3       optimal_executions,
4       onepass_executions,
5       multipasses_executions
6FROM   v$sql_workarea_histogram
7WHERE  optimal_executions + onepass_executions + multipasses_executions > 0
8ORDER  BY low_kb;

Code Breakdown

The formatted query

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

  • aggregate PGA target parameter — the current value of the PGA_AGGREGATE_TARGET initialization parameter. If the parameter is not set, this value is 0 and automatic management of PGA memory is disabled.
  • total PGA inuse — how much PGA memory is currently consumed by work areas. The difference between this row and total PGA allocated hints at how much memory other PGA consumers, such as PL/SQL or Java, are using.
  • total PGA allocated — the current amount of PGA memory allocated by the instance. Oracle tries to keep this number below the target, but it can exceed it by a small percentage for a short time when the work-area load rises very quickly or when the target is small.
  • maximum PGA allocated — the most bytes of PGA memory allocated at one time since instance startup.
  • over allocation count — a cumulative count since startup. Over allocation can happen when the target is too small: Oracle cannot honor the target and has to allocate extra PGA memory.
  • cache hit percentage — a metric that reflects how the PGA memory component is performing, cumulative since startup. A value of 100% means every work area run since startup used an optimal amount of memory. When a work area cannot run at its optimal size, Oracle makes one or more extra passes over the input data, and the percentage falls in proportion to the size of the data and the number of extra passes. The metric is weighted by bytes processed, not by a plain count of operations.

The full-view query

The second query returns every row in V$PGASTAT with its raw value and unit. Two of those rows feed the cache hit percentage: bytes processed is the number of bytes handled by memory-intensive SQL operators, and extra bytes read/written is the number of bytes handled during extra passes. Oracle's tuning guide gives the formula as bytes processed times 100, divided by the sum of bytes processed and extra bytes read or written.

The histogram query

The count of work areas that ran in optimal, one-pass or multi-pass mode is not a V$PGASTAT row. Those counts are columns of V$SQL_WORKAREA_HISTOGRAM, which splits work areas into 33 groups by their optimal memory requirement, growing in powers of two, and accumulates counts since instance startup. The query above shows only the groups that have run at least once.

The parameter check

SHOW PARAMETER pga_aggregate_target reports the configured target. A nonzero value sets WORKAREA_SIZE_POLICY to AUTO, so SQL work areas are sized automatically. A value of 0 sets the policy to MANUAL, and work areas are sized by the *_AREA_SIZE parameters. When you do not set the parameter, Oracle uses 10 MB or 20% of the SGA size, whichever is greater. When automatic memory management is on (MEMORY_TARGET has a positive value) and PGA_AGGREGATE_TARGET is also positive, the PGA target acts as the minimum size of the instance PGA.

Key Points

  • V$PGASTAT is instance-wide. It reports instance-level statistics about PGA memory usage and the automatic PGA memory manager, while V$PROCESS carries per-process PGA columns.
  • Cache hit percentage is the headline health number. A high value means work areas run at their optimal size; a low value means extra passes over the input data.
  • over allocation count flags undersizing. Oracle's own advice for a nonzero count is to raise PGA_AGGREGATE_TARGET, using V$PGA_TARGET_ADVICE as the guide.
  • The target is a goal, not a ceiling. Oracle tries to limit PGA use to the target, but use can exceed it at times.
  • PGA_AGGREGATE_LIMIT is the hard limit. When the instance exceeds it, Oracle first ends the calls of the sessions that hold the most untunable PGA memory.
  • The optimal, one-pass and multi-pass counts are in another view. Read V$SQL_WORKAREA_HISTOGRAM for them.

Insights and Best Practices

Read the hit percentage together with the over allocation count

Neither number means much alone. A cache hit percentage at or near 100% with an over allocation count of zero means the target covers the workload. Oracle's tuning guide says a value of 100% is ideal but rarely seen except on pure OLTP systems, and gives a rule of thumb that the percentage should stay above 60%. At 60% the system is almost processing double the bytes it would need to process in an ideal case. Capture both numbers on a schedule so you can see the trend and not a single point.

Remember that the counters are cumulative

The cache hit percentage, the over allocation count and the maximum all accumulate from instance startup. A busy afternoon after a month of quiet nights is diluted by the history. To judge a specific period, record the extra bytes read/written and bytes processed rows before and after it and compare the change.

Size from the high-water mark, not the average

The maximum PGA allocated row records the most PGA the instance has held at one time since startup. Sizing the target to a typical figure leaves no room for large work areas that arrive together. Use the high-water mark, plus a margin you choose, as a floor when you review the target.

Use the advice view to predict the effect of a change

V$PGA_TARGET_ADVICE predicts how the cache hit percentage and the over allocation count would change if you changed PGA_AGGREGATE_TARGET. It simulates the past workload of the instance at several target values around the current one. Each row gives the estimated cache hit percentage, which is derived from BYTES_PROCESSED and ESTD_EXTRA_BYTES_RW, and an estimated over allocation count. A nonzero estimated over allocation count on a row means that target is not large enough, and Oracle says not to set the parameter to that value. Two limits apply: the view is empty if PGA_AGGREGATE_TARGET is not set, and it is not updated when STATISTICS_LEVEL is BASIC.

Know what the work-area modes mean

Oracle describes three sizes for a work area. At optimal size the work area holds the input data and its helper structures. At one-pass size an extra pass is made over part of the input, and response time rises. At multi-pass size several passes are needed and response time rises sharply. Oracle's tuning guide also notes that a work area can spill to a temporary segment, and that V$SQL_WORKAREA_ACTIVE can show which active work areas do so. A low cache hit percentage and heavy temporary space use can therefore point to the same memory shortage, though the tuning guide does not tie the two together in one sentence.

Consider the hard limit

The default for PGA_AGGREGATE_LIMIT depends on your settings. With MEMORY_TARGET set, it defaults to MEMORY_MAX_TARGET. Otherwise it defaults to 200% of PGA_AGGREGATE_TARGET, and in all cases it is at least 2 GB and at least 3 MB times the PROCESSES parameter. Raising the soft target without checking the limit can produce a target that sits close to a hard stop, so look at both parameters together.

When to Run This

  • Deciding whether PGA_AGGREGATE_TARGET is sized correctly for the real workload.
  • Investigating queries that are slow because sorts or hash joins make extra passes over their input.
  • Establishing a baseline of PGA usage before and after a memory change.
  • Confirming that automatic PGA memory management is active and the target is nonzero.
  • 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 while individual operations are still large. Check V$SQL_WORKAREA, which shows information about the work areas used by SQL cursors, to find the statements involved, and consider tuning those queries before adding memory.

If total PGA allocated never approaches the target, the instance may be given more PGA than it uses, and you can review whether that memory is better used elsewhere.

If raising PGA_AGGREGATE_TARGET does not improve the hit percentage, read V$PGA_TARGET_ADVICE. The estimates show whether a larger target would change the result at all.

If V$PGA_TARGET_ADVICE returns no rows, check that the parameter is nonzero and that STATISTICS_LEVEL is not BASIC.

If the parameter shows zero, automatic PGA management is off, WORKAREA_SIZE_POLICY is MANUAL and the *_AREA_SIZE parameters apply. Set a target to return to automatic sizing, which Oracle strongly recommends.

References

Posts in this series