The Don Burleson's article

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

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

13 Responses to “The Don Burleson's article”

Anonymous on 7:29 PM
I personally feel that we may have been splitting hairs on this one issue:

Lewis and Kyte contend that Burleson is wrong on the statement below:

- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto

Please remember that when using automatic PGA management:

sort_area_size IS ignored when you are using dedicated connections.

sort_area_size IS NOT ignored when using shared connections.

I'm not debating the rest of the points. I agree with Lewis after having tested his assertions.
Radoslav Rusinov on 7:36 PM
I didn't discuss the SORT_AREA_SIZE issues because it is clearly showed from Janathan Lewis that this is a wrong conclusion. But my research was mainly over the _PGA_MAX_SIZE parameter, which is one of the main points into the Don Burleson's article.

Rado
Anonymous on 7:47 PM
It is only a wrong conclusion when you are using shared memory.

It is a correct conclusion when you have dedicated connections.
Radoslav Rusinov on 7:51 PM
Okay, I agree
Mike on 10:44 PM
In your test did you look at HASH and SORT or Just SORT? From a historical perspective HASH area was always 2XSORT size. Hence the limit of _pga_max_size/2 applies to SORT but does it pertain to hash as well? Since Hash also uses the PGA area, this is the source of the 200, not a simple sort.
Anonymous on 12:00 AM
Hi Anonymous,

Sorry but I dont agree with you.

It think that it is normal to asume that someone reading a document realted to Oracle hidden parameters knows a little bit more about Oracle then just the basic stuff.

Here it's about the automatic PGA memory management and Oracle clearly states "This mechanism cannot be used for shared server connections". Once you know this there is no reason to speak, in relation with pga management, about shared connections.

Also it is wrong to say that the sort_area_size is not ignored when using shared connection. Correct would be: the PGA management is ignored when using shared connections.

Regards
Maurice
Radoslav Rusinov on 10:53 AM
Hi, Mike
No, I didn't take a look at SORT_AREA_SIZE and HASH_AREA_SIZE parameters. My research was mainly focused on the _PGA_MAX_SIZE and its impact upon the PGA region.

Rado
Peter K on 1:46 AM
Just for completeness, Jonathan Lewis has provided an addendum (Sept 7) to his article in response to the extensive "enhancements" that Don has made to his article. Also included is a 190 page Word document created by Mike Ault to support Don's assertions.
Radoslav Rusinov on 8:57 AM
Thanks, Peter
I have added it to my post: The _PGA_MAX_SIZE hidden parameter:
dba-blog.blogspot.com/2005/08/pgamaxsize-hidden-parameter.html as an additional reference for this hidden parameter.

Rado
Peter K on 10:22 PM
This comment has been removed by a blog administrator.
Peter K on 10:23 PM
This comment has been removed by a blog administrator.
Peter K on 10:25 PM
I made a mistake/typo in the previous two comments and had no way of editing so I deleted the comments and resubmitted it again here

Rado,
Another update. The discussion over this particular article has spilled over onto AskTom.com and there is a whole bunch of responses/comments including one from "Janet Burleson" and another from a Rampart Press author taking Tom to task. It's a good read and would be funny if it was so sad that all this was a result of sloppy research and publishing by a so-called oracle "expert".

Here's the link, http://tinyurl.com/cfm9c
Radoslav Rusinov on 11:22 AM
Thanks Peter,
I am stayinh in touch with all discussions: the threads in the Dizwell forum, at Tom Kyte's Blog and the mentioned thread on asktom.oracle.com (yesterday I saw the link for it on the Dizwell forum, I think).
I can start the whole new blog dedicated only to this article and followed responses :)

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