Oracle PGA
PGA
1. Memory privately assigned Oracle server process / shadow process.
2. Available PGA is “work area”, ensure sufficient amount available.
3. If insufficient, db used temp tbs – resulting in slow perf due to I/o – one pass or multi pass.
4. Manual PGA parameters: sort_area_size, hash_area_size, bitmap_merge_size, create_bitmap_area_size
5. Auto PGA – by setting WORKAREA_SIZE_POLICY= AUTO | MANUAL. Pros – simple admin, dynamic allocation, freeing, efficient mem.
6. PGA_AGGREGATE_TARGET specify max PGA thru auto allocation.
e.g SQL>alter system set pga_aggregate_target=1024M;
7. V$PGA_TARGET_ADVICE and V$PGA_TARGET_ADVICE_HISTOGRAM
8. Estimation – for oltp PGA_AGGREGATE_TARGET = <Total physical memory> * 20%.
9. V$PGASTAT – info on max,current pga info.
- ‘aggregate PGA target parameter’ = max auto pga value set.
- ‘over allocation count’ = # of times pga agg targer is exceeded. If >0 , mean pga too small.
- ‘cache hit percentage’= efficiently of pga. should be near 99%.
Tuning PGA
1. Min PGA aggr value:
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;
2. v$pga_target_advice; = ESTD_OVERALLOC_COUNT must be zero
3. multipass, onepass or optimal can be monitored from ST04 PGA monitor- status – workarea executions.
