Basics of PGA_AGGREGATE_TARGET
PGA_AGGREGATE_TARGET is the user workspace for Oracle users. By querying V$PGA_TARGET_ADVICE you can see what Oracle recommends on how to size your PGA_AGGREGATE_TARGET value. To query this view use the following query.
SELECT round (PGA_TARGET_FOR_ESTIMATE/1024/1024) target_mb,
ESTD_PGA_CACHE_HIT_PERCENTAGE cache_hit_perc,
ESTD_OVERALLOC_COUNT
FROM v$pga_target_advice;
The output of this query will be similar to the following:
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
24 15 143
48 32 28
95 42 4
143 63 0
From the above results we should set the PGA_AGGREGATE_TARGET parameter to a value where we avoid any over allocation, so lowest PGA_AGGREGATE_TARGET value we can set is 143 (where ESTD_OVERALLOC_COUNT is 0).
