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

« Home

Previous Post »Previous Post »Previous Post »Previous Post »Previous Post »Previous Post »Previous Post »Previous Post »Previous Post »Previous Post » 

The _PGA_MAX_SIZE post is updated

Published Tuesday, August 23, 2005 by Radoslav Rusinov.


I have updated my post about the _PGA_MAX_SIZE hidden parameter with additional information that pop-up in the past few days.
I will appreciate any comments and corrections about my points if you think that they are wrong or you just have more valuable information that I can add to this post.

Labels:


Permanent Link  @ 0 Comments on This Post  @ Comment Here

The Don Burleson's article

Published Sunday, August 21, 2005 by Radoslav Rusinov.

Added from Rado (23 August): Don updated his article again yesterday (see the link below). Now it is more detailed and there are lot of quoted documents.
But there is again some questionable information:
No RAM sort may use more than 5% of pga_aggegate_target or _pga_max_size, whichever is smaller. This means that no task may use more than 200 megabytes for sorting or hash joins.
Again, for serial operations for one "RAM sort" is used one workarea. We know that the _PGA_MAX_SIZE is per process but one process can have many workareas. The max size of one workarea is calculated from the formula min(5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE/2), not min(5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE).
I have checked that by tests. I have changed the _PGA_MAX_SIZE on my 9.2.0.5 database and after every change, the _SMM_MAX_SIZE has been recalculated every time to be equal to min(5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE/2).
* pga_aggregate_target = 4g
* _pga_max_size = 300m
* _smm_px_max_size = 333m
With these hidden parameters set we see a 4x large size increase for parallel queries and sorts:
* A RAM sort or hash join may now have up to the full 200 megabytes (5% of pga_aggegate_target) a 400% increase.
Again, the same mistake, here the single workarea will have size no more than min(5% of PGA_AGGREGATE_TARGET, _PGA_MAX_SIZE/2), which in this case is min(200 MB, 150 MB) = 150 MB, the calculated value for _SMM_MAX_SIZE will be 153600 (in KB).

Don Burleson have published one revised version of his article on his site: Undocumented secrets for super-sizing your PGA

In the revised article you can see that:
* pga_aggregate_target = 6g
* _pga_max_size = 300m
* _smm_px_max_size = 333m

With these hidden parameters set we see a 5x large size increase for parallel queries and sorts:

* A RAM sort or hash join may now have up to 300 megabytes (5% of pga_aggegate_target) a 50% increase.
I did not perform a tests but according to the all information that I have read about the subject, the single RAM sort will use no more than 150 MB for these particular settings because it is taken as min(5% of PGA_AGGREGATE_TARGET,_PGA_MAX_SIZE/2), which in this case is 150 MB.
At general, the article is more unclear than its original because it do not explain in detail the mentioned hidden parameters and their meanings and correct use.

I have seen from the Doug's blog that Jonathan Lewis has published a detailed article on his site about the recently discussed article for the PGA hidden parameters:
Undocumented secrets – or untested fairytales?

Jonathan Lewis disproves many of the assertions coming from Don Burleson's article with lot of tests, as:
- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto
- The maximum default allowed value is 200 megabytes (for PGA_AGGREGATE_TARGET)

Jonathan Lewis explains in more details the _SMM_MAX_SIZE and _SMM_PX_MAX_SIZE hidden parameters.

There is a post on the Tom Kyte's blog about the same article too: Getting Credible Information

Added from Rado (24 August): There is a new blog post from Niall Litchfield on his blog: A few thoughts on PGA memory management

Both of them concludes in that everyone must take responsibility for playing around with hidden parameters (especially when they are applied to a production system) and that everyone must knows what exactly is he/she doing.

According to results coming from this presentation (and mentioned discussions from my previous The _PGA_MAX_SIZE hidden parameter post) it can be seen that good results for big sort tasks can be achieved by setting of the _PGA_MAX_SIZE and related hidden parameters but only with a lot of testing and guidance of the Oracle Support team.

In conclusion, do NOT believe to everything that you have read on Internet, be suspicious especially when it presents some new and revolutionary information. If you are suspicious - TEST IT, if you are not suspicious - TEST IT, if you are just curious - TEST IT.
If you decide to apply it on your production system - TEST IT, TEST IT, TEST IT.
Do NOT set hidden parameters without testing and permission of Oracle Support.

Labels:


Permanent Link  @ 13 Comments on This Post  @ Comment Here

The _PGA_MAX_SIZE hidden parameter

Published Thursday, August 18, 2005 by Radoslav Rusinov.

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:
  • 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:


Permanent Link  @ 5 Comments on This Post  @ Comment Here

Management of the Oracle9i PGA

Published by Radoslav Rusinov.

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:


Permanent Link  @ 3 Comments on This Post  @ Comment Here

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

Search This Blog with Google

Search This Blog with Free Find


powered by FreeFind

Search This Blog with Technorati

Oracle Resources

Oracle Blogs

Blog Statistics

              

              

               Page Rank Checker