Determine if PGA_AGGREGATE_TARGET is set big enough

Since Oracle9i, the dynamic initialisation parameter PGA_AGGREGATE_TARGET replaces parameters like sort_area_size and bitmap_merge_area_size. PGA_AGGREGATE_TARGET is set for all server-processes and not for each. If PGA_AGGREGATE_TARGET is set to 200mb, all server processes share these 200mb of memory. The minimum value of PGA_AGGREGATE_TARGET is 10mb.

To determine if PGA_AGGREGATE_TARGET is set high enough, the following query can be executed:

select name,value from v$sysstat where name like 'workarea executions%';

NAME VALUE
---------------------------------------------------------------- ----------
workarea executions - optimal 31570
workarea executions - onepass 0
workarea executions - multipass 0

In the above example all operations (all executions) have been execute in memory (optimal situation). When the operation was too big to be executed in memory, a part of the transaction was written to disk (onepass). If several swaps were needed, the Multipass counter will be increased.

In the example below, the PGA_AGGREGATE_TARGET is set too low:

NAME VALUE
---------------------------------------- ----------
workarea executions - optimal 510
workarea executions - onepass 1
workarea executions - multipass 4

The view v$pgastat give additional information about the use of the PGA:

select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
aggregate PGA target parameter 524288000 bytes
aggregate PGA auto target 451537920 bytes
global memory bound 26214400 bytes
total PGA inuse 22575104 bytes
total PGA allocated 55227392 bytes
maximum PGA allocated 194516992 bytes
total freeable PGA memory 10682368 bytes
PGA memory freed back to OS 882114560 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 1022976 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 0 bytes
over allocation count 0
bytes processed 2193918976 bytes
extra bytes read/written 0 bytes
cache hit percentage 100 percent

16 rows selected.

The second line (aggregate PGA auto target) should be just a little less then the target parameter (1ste line). The setting ‘over allocation count’ indicates the number of times Oracle has to allocate additional memory as Oracle cannot honor the PGA_AGGREGATE_TARGET setting. If optimal, this parameter should be close to zero. The line ‘cache hit percentage’ corresponds with the number of bytes executed in optimal buffers compared to the total number of bytes (see v$sysstat). Ideally the cache hit percentage should be close to 100%.


In case of the PGA_AGGREGATE_TARGET is set too small, the view V$PGA_TARGET_ADVICE can be used to determine the minimum setting for PGA_ALLOCATIE_TARGET considering the current workload:

select round(pga_target_for_estimate/1024/1024) as target_size_MB,
bytes_processed,estd_extra_bytes_rw as est_rw_extra_bytes,
estd_pga_cache_hit_percentage as est_hit_pct,
estd_overalloc_count as est_overalloc
from v$pga_target_advice;

TARGET_SIZE_MB BYTES_PROCESSED EST_RW_EXTRA_BYTES EST_HIT_PCT EST_OVERALLOC
-------------- --------------- ------------------ ----------- -------------
63 2197386240 0 100 5
125 2197386240 0 100 0
250 2197386240 0 100 0
375 2197386240 0 100 0
500 2197386240 0 100 0
600 2197386240 0 100 0
700 2197386240 0 100 0
800 2197386240 0 100 0
900 2197386240 0 100 0
1000 2197386240 0 100 0
1500 2197386240 0 100 0
2000 2197386240 0 100 0
3000 2197386240 0 100 0
4000 2197386240 0 100 0

When PGA_AGGREGATE_TARGET is set to 63mb, the estimate hit percentage is 5. This means 5% of transactions are executes as onepass or multipass. In the above situation, PGA_AGGREGATE_TARGET can be set to 125mb. In this situation there would be no over allocation and the hit ration would be 100%