To enable automatic PGA management, you have to set WORKAREA_SIZE_POLICY to AUTO and PGA_AGGREGATE_TARGET to the size of memory that can be allocated to PGA.

There is a guidelines suggested by Oracle on sizing the PGA_AGGREGATE_TARGET
1. For OLTP database, PGA_AGGREGATE_TARGET should be 10-15% of the total memory allocated Oracle.
2. For datawarehouse or DSS database, PGA_AGGREGATE_TARGET should be 40-70% of the totol memory allocated to Oracle.

On the other hand, you can query V$PGA_TARGET_ADVICE for the optimal value of V$PGA_TARGET_ADVICE. For example,

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 performance of the PGA settings can be monitored by V$PGASTAT. The most important column of this view is cache hit percentage.


References
Expert Oracle Database Architecture(Page 123) By Thomas Kyte
http://www.jaredstill.com/content/pga-target.html

These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google
  • Furl
  • Reddit
  • Spurl
  • StumbleUpon
  • Technorati