My experience, findings and thoughts in my daily work with Oracle products

Management of the Oracle9i PGA

E-mail this post

Remember me (?)

All personal information that you provide here will be governed by the Privacy Policy of More...

If you think that your PGA can be configured properly just by increasing of the PGA_AGGREGATE_TARGET parameter, think twice - it is NOT!

See this article from Don Burleson: Undocumented secrets for super-sizing your PGA
It shows that there are two hidden parameters that can help for proper using of the PGA. Otherwise, Oracle will never use more than 200 MB for the whole PGA nor will it use more than 10 MB for single PGA task (sort, hash join, group-by, bitmap merge) regardless of your setting.
This can explains why in many cases V$PGA_TARGET_ADVICE shows that the size of the current PGA is enough but even that there are lot of performed disk sorts.
If this information is correct, it means that the following advices coming from Metalink Note 223730.1: Automatic PGA Memory Management in 9i
To determine the appropriate setting for PGA_AGGREGATE_TARGET parameter I recommend to follow the following steps:
Make a first estimate for PGA_AGGREGATE_TARGET based on the following rule:
- For OLTP systems
PGA_AGGREGATE_TARGET = ( * 80%) * 20%
- For DSS systems
PGA_AGGREGATE_TARGET = ( * 80%) * 50%
cannot be considered as fully correct ones.
There are lot of questions that are raising from the above article.


3 Responses to “Management of the Oracle9i PGA”

  1. Blogger Oded Maimon 
    Check tom kyte post about this article:
  2. Blogger Radoslav Rusinov 
    Yes, I mention it already in my last post
  3. Blogger Radoslav Rusinov 
    To see why I was wrong in doing of fastest conclusions about the mentioned article for the PGA hidden parameters, see the following two posts as well:

Leave a Reply

      Convert to boldConvert to italicConvert to link


About me

  • » I'm Radoslav Rusinov
  • » From Sofia, Bulgaria
  • » Employer TechnoLogica Ltd.
  • » I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
  • » The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
  • » My profile

RSS 2.0 Feed

Search This Blog with Google

Search This Blog with Free Find

powered by FreeFind

Search This Blog with Technorati

Previous Posts


Articles & Presentations

Discover Bulgaria

Oracle News & Blogs Aggregators

Oracle Resources

Remote DBA

Oracle User Groups

Oracle Blogs

Oracle Forums

Security Resources

Professional CV

Blog Statistics



               Page Rank Checker