The _PGA_MAX_SIZE hidden parameter

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

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

Check the following links for more details regarding to my previous post (Management of the Oracle9i PGA):

Discussion from Don Burleson's Oracle forum: Question for Don, Regarding _pga_max_size
Article from Ora!Ora!Oracle mail magazine: Sort
Metalink discussion thread: high pga_aggregate_target but low "cache hit percentage"
Metalink discussion thread: PGA memory usage exceeding PGA_AGGREGATE_TARGET
The Metalink discussions above are started from Laurent Schneider, an OCM DBA in Oracle9i and Oracle ACE member
Very good technical presentation from a Bulgarian Oracle professional Christo Kutrovsky: Working with Automatic PGA
Article from the Oracle employees Benoi^t Dageville and Mohamed Zait: SQL Memory Management in Oracle9i
OTN Forum discussion - again Laurent Schneider and Christo Kutrovsky: Any experience with the init parameter _pga_max_size?

From all information that I have read I can note several important points:

The _PGA_MAX_SIZE does not controls the total used PGA memory for the whole system.
The _PGA_MAX_SIXE controls the max used PGA memory per process, which by default is 200 MB.
A work area is a large extent on the memory available in the Program Global Area (PGA).
Each time a cursor is executed, a new runtime area is created for that cursor in the PGA memory region of the server process executing that cursor.
One process can have many work areas.
The size that can be used from a single work area is limited to a max of 100 MB (_PGA_MAX_SIZE/2) by default for serial operations. This is in case that your PGA_AGGREGATE_TARGET > = 2 GB. Otherwise it will use no more than of (5% of PGA_AGGREGATE_TARGET), for values for PGA_AGGREGATE_TARGET <= 2GB. It is coming from that the min(5% of PGA_AGGREGATE_TARGET, 100MB) is taken.
The size for a single work area is limited to 30% of the PGA_AGGREGATE_TARGET/DOP for parallel operations, where DOP is Degree of Parallelism.
If you have set, for example the PGA_AGGREGATE_TARGET to 1 GB then Oracle cannot give more than 50 MB to a single work area (for serial operations).
If you want to use more than 100 MB per single work area then may be the _PGA_MAX_SIZE can help you to achieve better results for your sort tasks and to let you to perform the biggest ones only inside the memory.
The _PGA_MAX_SIZE is measured in Bytes.
Another hidden parameters that you must be aware of, are the _SMM_MAX_SIZE and _SMM_PX_MAX_SIZE.
_SMM_MAX_SIZE limits the maximum work area size for serial operations. _SMM_MAX_SIZE is calculated from the result of min(5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE/2).

When you are executing a parallel query it is still using only one work area, all slaves are working under that work area.

_SMM_PX_MAX_SIZE limits the maximum size of used memory from all slave processes. The used size by each slave process inside that work area is limited from the value of _SMM_MAX_SIZE parameter. So, the value of _SMM_MAX_SIZE is used not only for serial but for parallel operations as well.

_SMM_PX_MAX_SIZE is used only when you are running query with a degree with a parallelism more than 6. For example, if you have DOP = 6 then every slave process can takes no more than _SMM_MAX_SIZE. When DOP > 6 then the _SMM_PX_MAX_SIZE is taking a place.

The values for _SMM_MAX_SIZE and _SMM_PX_MAX_SIZE are measured in KBytes.

After changing of the _PGA_MAX_SIZE parameter and restarting of the database, values for both _SMM_MAX_SIZE and _SMM_PX_MAX_SIZE are recalculated automatically. This means that you don't need to change them manually. But you must know what they are doing.

For 32-bit systems the preferrable value for _SMM_MAX_SIZE is 500 MB because the limitations. On 64-bit system you will be able to allocate around 4GB of memory for single sort task. Be aware of these values when you are changing the _PGA_MAX_SIZE because it will affect the _SMM_MAX_SIZE as well.

Because the _PGA_MAX_SIZE is used per process you must set the PGA_AGGREGATE_TARGET to appropriate value as well. This is the parameter, which indeed controls the total amount of the PGA memory.

If you want to avoid any ORA-04030 errors configure the values of these parameters appropriate and with attention depending of your current system environment.

Do NOT set these hidden parameters on production systems without permission of Oracle Support. You must be absolutely sure that this is the only way that can solve your problem before to go for it. Usually you must open a TAR and to request for permission.

If you want to use them, you must ask Oracle Support for needed patches that must be applied.

When you are quering the V$PGASTAT view, be aware of that how the row "Cache Hit Percentage" is calculated: PGA Cache Hit Ratio = total bytes processed / (total bytes processed + total extra bytes read/written). With this formual it is almost impossible to get a result much smaller then 50%.

Usually, this parameter can be helpful only on large databases. There are Data Warehouse databases that have reduced the average query execution time by 50% after changing of the _PGA_MAX_SIZE.

Remember, use this solution only if you don't have another workaround to solve your problems.

If you decide to go for it, perform a lot of tests of your test environments before to put it on you production database.

Remember that all of the above info is the result from my research inside all available information about this hidden parameter, I did not test many of these points. In this post I just summarize all of that I know about this subject.

Do NOT accept the information, which is posted here as your main source, your main source must be the results of your tests and the Oracle Support Team approval and confirmation that you have done everything that is needed to use this parameter in your production environment.

I will appreciate any comments about the above points, especially if I am wrong in some of my conclusions.

Another useful information about this subject:
Oracle9i New Feature: Automated SQL Execution Memory Management
Oracle9i Monitoring Automated SQL Execution Memory Management
Automatic PGA Memory Managament in 9i

I think that few things, which are coming from the article that insists my posts must be reconsidered or at least discussed:

the maximum default allowed PGA value for the whole system is 200 MB. Yes, if the PGA_AGGREGATE_TARGET = 200 MB.

no task can use more than 10 MB for sorting or hash joins. Yes, if the PGA_AGGREGATE_TARGET = 200 MB.

the total workarea size cannot exceed 200 MB because the default value of the _PGA_MAX_SIZE. Yes, per process.

Added on 08 September: Jonathan Lewis has written new article with more info, details and results of testing accroding to Don Burleson's article: The Snark research mechanism ?
Use this article if you want to learn more about the _PGA_MAX_SIZE parameter.

I am correcting myself in that the advices from the Metalink Note 223730.1 (quotes from previous post) are not correct.

Labels: PGA

5 Responses to “The _PGA_MAX_SIZE hidden parameter”

Anonymous on 7:59 PM
Hi,

Nice document ;-)

_PGA_MAX_SIZE is my favorit parameter for large DWH DBs.

I tested this parameter a year ago and changed it on prod (after checking with O Support) from 200m to 1000m. After that the average execution time of our queries was reduces by 50%.

About the _SMM_(PX)_MAX_SIZE parameters it is important to be aware that the unit is Kbytes. Also if you change _PGA_MAX_SIZE and restart your DB they are recalculated, that means that it is often not necessary to modify the _SMM... parameters.

_SMM_MAX_SIZE limits not only serial processes but also processes related to a parallel query. This means that each process of a parallel query is also limited to a workarea size defined by _SMM_MAX_SIZE.

And one last thing: when you have a look at V$PGASTAT remember the way "cache hit percentage" is calculated.
PGA Cache Hit Ratio = total bytes processed / (total bytes processed + total extra bytes read/written)

With this formual it is almost impossible to get a result much smaller then 50%. From my point of view it would be more correct to calculate:

(total bytes processed - total extra bytes read/written) / total bytes processed

Regards
Maurice Mueller
http://forums.oracle.com/forums/thread.jspa?threadID=253828&start=0&tstart=0
Radoslav Rusinov on 9:11 PM
Hi Maurice,
Thank you for the valuable info. I will think what is better, to update my post or to put the new information on separate one. It is very good that someone who have real experience with these hidden parameters can share his experience.
This year I was on the Craig's course inside your company's building in Zurich. The world is so small indeed :)
Radoslav Rusinov on 9:21 PM
Hi Maurice,
I open your company page with publications with intention to start another post with a link to one of yours technical presentations:
"CBO: A Configuration Roadmap" from Christian Antognini (from the past Hotsos symposium) but I have found that it is removed. Is it removed intentionally?
Anonymous on 3:18 PM
Hi,

Are you looking for the following document ?
http://www.trivadis.ch/Images/CBOConfigurationRoadmap_tcm16-12738.pdf

From my point of view it would be the best to update your post so that all PGA/workarea sizing information are located at the same place.

Regards
Maurice
Radoslav Rusinov on 3:50 PM
Hi, Maurice
10x for the link!
Yes, I will update the post.
Now I have seen that Don has updated his article again and I am doing some tests to clarify something.
I will put the Trivadis presentation on different post in the next days

Rado

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