My experience, findings and thoughts in my daily work with Oracle products
The Don Burleson's article
Published Sunday, August 21, 2005 by Radoslav Rusinov | E-mail this post
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 126.96.36.199 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 = 4gAgain, 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).
* _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.
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 = 6gI 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.
* _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.
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 InformationAdded 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.
- » 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