Management of the Oracle9i PGA

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

Published Thursday, August 18, 2005 by Radoslav Rusinov | E-mail this post

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.

Labels: PGA

3 Responses to “Management of the Oracle9i PGA”

Oded Maimon on 5:00 PM
Check tom kyte post about this article:
http://tkyte.blogspot.com/2005/08/getting-credible-information.html
Radoslav Rusinov on 9:29 AM
Yes, I mention it already in my last post
Radoslav Rusinov on 11:08 AM
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:
http://dba-blog.blogspot.com/2005/08/pgamaxsize-hidden-parameter.html

http://dba-blog.blogspot.com/2005/08/don-burlesons-article.html

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