My experience, findings and thoughts in my daily work with Oracle products
The _PGA_MAX_SIZE hidden parameter
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)
:From all information that I have read I can note several important points:
I will appreciate any comments about the above points, especially if I am wrong in some of my conclusions.
- 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.
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.
- » 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
Search This Blog with Google
Search This Blog with Free Find
Search This Blog with Technorati
Oracle News & Blogs Aggregators