Tuesday, August 23, 2005
A few thoughts on PGA memory management
I'm guessing that many of you that read this, also read Tom Kyte and Jonathan Lewis. The reverse is unlikely to be true for good reasons. Those of you that don't need to do some background reading now.
1. Tom's blog and comments
2. Jonathan's article
3. Don's revised article (registration required - but its free and its an ok site, just avoid the spam).
I'm actually quite glad of this disagreement because for me it serves to illustrate a few things.
The importance of wide experience, or at least knowledge of your own limited experience. Were I a betting man, I'd wager that the various articles around the internet - and indeed on Metalink - that state that XXXX_AREA_SIZE is ignored when PGA_AGGREGATE_TARGET is non-zero and WORKAREA_SIZE_POLICY is set to AUTO come from 2 things. A cursory reading of the docs, the implication of the word dedicated is easy to miss, and from generalized personal experience. By the latter I mean that we tend to extrapolate from our own experience as if it were universally true (or at least representative).
The importance of test cases and demos, complete with explanation. Jonathan's article is an exemplar here. You could run Jonathan's test case on your own system - no shared server sessions - get different results and conclude differently. Your conclusions wouldn't be universally true, and the wisdom of setting a parameter without reading the official docs and searching the suppliers website seems to say the least lacking, but it would be a valid test for you. The genius of course of Jonathan's test is he explains under what circumstances he would get different results.
Knowing what UNDOCUMENTED may mean, in practice it probably only means that Oracle Support would undo all of your careful changes in order to troubleshoot a problematic system, it could mean that your tinkering could render the system unsupported. Messing with undocumented parameters definitely has a place for the curious. production systems aren't that place. I can't of course resist pointing out that the major undocumented parameters discussed in the 'undocumented secrets' article are in fact documented.
It does help to set out the technical basis for ones assessment and conclusions. I seem to recall being required to do this at 'A' and 'O' level science. Don's original article was somewhat helpful here - it stated what its assumptions were. Unfortunately for the author a clear majority were incorrect and some assumptions did go unstated, but stating ones assumptions is good practice.
It doesn't help to name call and use emotive language. Don's original article contains the sentence For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management. for which, rightly in my view, he was pulled up. Equally responses to Tom's blog include lines such as Tom,
Good to see people like you are no longer ignoring people like Don Burleson who most of the time post half truths and totally wrong "technical Information". You are doing a great service in pointing out such issues and I know this is not the first time you are doing it. It's almost impossible to imagine the same poster replacing 'people like Don Burleson' with 'Oracle Support' in the above sentence. Yet the metalink article I posted above would provide the same degree of justification when read on its own. It is by no means the only example either.
All technical sites should include at least a feedback link - or ability to comment.
8 Comments
1. Tom's blog and comments
2. Jonathan's article
3. Don's revised article (registration required - but its free and its an ok site, just avoid the spam).
I'm actually quite glad of this disagreement because for me it serves to illustrate a few things.
The importance of wide experience, or at least knowledge of your own limited experience. Were I a betting man, I'd wager that the various articles around the internet - and indeed on Metalink - that state that XXXX_AREA_SIZE is ignored when PGA_AGGREGATE_TARGET is non-zero and WORKAREA_SIZE_POLICY is set to AUTO come from 2 things. A cursory reading of the docs, the implication of the word dedicated is easy to miss, and from generalized personal experience. By the latter I mean that we tend to extrapolate from our own experience as if it were universally true (or at least representative).
The importance of test cases and demos, complete with explanation. Jonathan's article is an exemplar here. You could run Jonathan's test case on your own system - no shared server sessions - get different results and conclude differently. Your conclusions wouldn't be universally true, and the wisdom of setting a parameter without reading the official docs and searching the suppliers website seems to say the least lacking, but it would be a valid test for you. The genius of course of Jonathan's test is he explains under what circumstances he would get different results.
Knowing what UNDOCUMENTED may mean, in practice it probably only means that Oracle Support would undo all of your careful changes in order to troubleshoot a problematic system, it could mean that your tinkering could render the system unsupported. Messing with undocumented parameters definitely has a place for the curious. production systems aren't that place. I can't of course resist pointing out that the major undocumented parameters discussed in the 'undocumented secrets' article are in fact documented.
It does help to set out the technical basis for ones assessment and conclusions. I seem to recall being required to do this at 'A' and 'O' level science. Don's original article was somewhat helpful here - it stated what its assumptions were. Unfortunately for the author a clear majority were incorrect and some assumptions did go unstated, but stating ones assumptions is good practice.
It doesn't help to name call and use emotive language. Don's original article contains the sentence For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management. for which, rightly in my view, he was pulled up. Equally responses to Tom's blog include lines such as Tom,
Good to see people like you are no longer ignoring people like Don Burleson who most of the time post half truths and totally wrong "technical Information". You are doing a great service in pointing out such issues and I know this is not the first time you are doing it. It's almost impossible to imagine the same poster replacing 'people like Don Burleson' with 'Oracle Support' in the above sentence. Yet the metalink article I posted above would provide the same degree of justification when read on its own. It is by no means the only example either.
All technical sites should include at least a feedback link - or ability to comment.
8 Comments:
I believe "ability to comment" is true, "feedback links" - they do not always work. Big old bit-buckets they are.
Doug
No, I don't think that Metalink and Don's site are in anyway comparable - well Ok Don *might* win on site design and speed. As for balance, please note that I'm only commenting on the XXX_AREA_SIZE is ignored bit of the article. I believe I said that the rest of the article was poor. I stand by that :) That said - and I'm repeating a point I made on Tom's blog here - the ignoring of shared_server seems to be pretty prevalent in metalink articles on PGA_AGGREGATE_TARGET (or 200M_PRIVATE_POOL as Don seems to think it should be called - there'll be a hit ratio as well). Its clearly well documented - it appears to me badly documented (not in the docs but in the support knowledge base - can't tell which is worse)
Tom
yeah - comments are bad, feedback is ignored. Not quite sure what we do. I think a wiki would work well.
Niall
having to verify yourself to your own blog. hmmm maybe image words was a little much.
No, I don't think that Metalink and Don's site are in anyway comparable - well Ok Don *might* win on site design and speed. As for balance, please note that I'm only commenting on the XXX_AREA_SIZE is ignored bit of the article. I believe I said that the rest of the article was poor. I stand by that :) That said - and I'm repeating a point I made on Tom's blog here - the ignoring of shared_server seems to be pretty prevalent in metalink articles on PGA_AGGREGATE_TARGET (or 200M_PRIVATE_POOL as Don seems to think it should be called - there'll be a hit ratio as well). Its clearly well documented - it appears to me badly documented (not in the docs but in the support knowledge base - can't tell which is worse)
Tom
yeah - comments are bad, feedback is ignored. Not quite sure what we do. I think a wiki would work well.
Niall
having to verify yourself to your own blog. hmmm maybe image words was a little much.
Quite frankly, folks: I don't get this one. It sounds to me like much ado about nothing.
Fact: Metalink and a number of other equally credible sources do appear to imply that sort_area_size is irrelevant when using pga_aggregate_target and workarea_size_policy=auto.
Fact: in the article, there are clear links to both JL and TKs words to the same effect. Ie, set both those parameters and forget about setting individual XXX_area_sizes.
Fact: the article goes into a bit of "star-gazing" with hidden parameters. But then again, we all kow that's part and parcel of the author's attitude to "tuning".
So what are we saying now? That sort_area_size is STILL relevant after all? Or what exactly? See what I mean? Hidden parameters notwithstanding, if we have to exaustively test and prove EVERY SINGLE little bit of Oracle parameter we change, ignoring for a moment the effects of varying workloads and their effect on anything we do, then what's the point of claiming the darn thing is easier to manage now? Because it certainly wouldn't be. That would quickly lead to "paralysis-by-testing" and other questionable end results.
Where is the middle ground? Does anyone know?
Fact: Metalink and a number of other equally credible sources do appear to imply that sort_area_size is irrelevant when using pga_aggregate_target and workarea_size_policy=auto.
Fact: in the article, there are clear links to both JL and TKs words to the same effect. Ie, set both those parameters and forget about setting individual XXX_area_sizes.
Fact: the article goes into a bit of "star-gazing" with hidden parameters. But then again, we all kow that's part and parcel of the author's attitude to "tuning".
So what are we saying now? That sort_area_size is STILL relevant after all? Or what exactly? See what I mean? Hidden parameters notwithstanding, if we have to exaustively test and prove EVERY SINGLE little bit of Oracle parameter we change, ignoring for a moment the effects of varying workloads and their effect on anything we do, then what's the point of claiming the darn thing is easier to manage now? Because it certainly wouldn't be. That would quickly lead to "paralysis-by-testing" and other questionable end results.
Where is the middle ground? Does anyone know?
Well, I created a nice comment with a link in it, previewed it, clicked on the link, and had no way back to the comment. Is there some key-combo that means "back" when there is no back button or link???
Now that that is out of my system...
You can rate the article in question. Nothing stopping you from stuffing the ratings.
"Big ol' bit buckets" are everywhere. Companies routinely make uncomfortable or unprofitable processes (like customer service in a mass market) hard. (Then again, what would you do if the president of the company replied?)
Metalink quality seems to cycle over time, and varies by section, certainly. High praise to anyone who sets a good example for them!
I think there is also some amount of "insulation" many people want after going through years of flamage, whether said flamage is deserved or not. I think Don knows he is provocative and purposefully makes it difficult to get a direct response. But I'm sure he or someone he employs reads everything to his info@... address.
His website may be flashy, but spelling, grammar, bad links and too many typos really detract. I found half-a-dozen things wrong on Mladen's book page, sent some to Mladen, and they were fixed, eventually.
Now that that is out of my system...
You can rate the article in question. Nothing stopping you from stuffing the ratings.
"Big ol' bit buckets" are everywhere. Companies routinely make uncomfortable or unprofitable processes (like customer service in a mass market) hard. (Then again, what would you do if the president of the company replied?)
Metalink quality seems to cycle over time, and varies by section, certainly. High praise to anyone who sets a good example for them!
I think there is also some amount of "insulation" many people want after going through years of flamage, whether said flamage is deserved or not. I think Don knows he is provocative and purposefully makes it difficult to get a direct response. But I'm sure he or someone he employs reads everything to his info@... address.
His website may be flashy, but spelling, grammar, bad links and too many typos really detract. I found half-a-dozen things wrong on Mladen's book page, sent some to Mladen, and they were fixed, eventually.
The comments on a blog should be in a forum style (threaded) and there should be the option to get mails when some one post a comment or once a day/week ...
Oded.
Oded.
It sounds to me like much ado about nothing.
Nuno - to me, I am surprised this came down to "sort_area_size". Sort area size was one of MANY factual errors in there.
For example, the bullet point right under sort_area_size was "pga_aggregate_target=500m" -- that shows the DBA doesn't understand pga management. No, it shows the person writing the bullet points doesn't *get it*.
That was one of many "wrong things"
And the new 'test case' added by Mike Ault? Lets see - the problem was that you had some sorts taking 64m (or less) to disk. Solution proposed: set pga-aggregate-target to 1.8 TERABYTES (with a T) and the undocumented _pga_max_size to 300m or so. Why? To get a 64m sort.
My solution: set pga_aggregate_target more in the reasonable range of 1.2gig (IF you have the ram, not sure if anyone has 1.8 TERABYTES yet) and sort areas of 64m would be possible right there and then. No need for undocumented stuff, no need to get magical and mystical. It was a seemingly straight forward solution that was made into something, well, I don't understand why they did that for the problem they were facing.
ps: yes, sort_area_size does count in 9i with shared server. To say to people:
For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management.
* sort_area_size=1048576 <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto.
* pga_aggregate_target = 500m <-- The maximum default allowed value is 200 megabytes, this limits sorts to 25 megabytes (5% of 500m).
is just wrong - the "does not understand" -- wrong, sort_area_size, could be worded better, the 500m comment - totally outrageous (mine is set lower then 500m, do I have it "wrong"?)
Nuno - to me, I am surprised this came down to "sort_area_size". Sort area size was one of MANY factual errors in there.
For example, the bullet point right under sort_area_size was "pga_aggregate_target=500m" -- that shows the DBA doesn't understand pga management. No, it shows the person writing the bullet points doesn't *get it*.
That was one of many "wrong things"
And the new 'test case' added by Mike Ault? Lets see - the problem was that you had some sorts taking 64m (or less) to disk. Solution proposed: set pga-aggregate-target to 1.8 TERABYTES (with a T) and the undocumented _pga_max_size to 300m or so. Why? To get a 64m sort.
My solution: set pga_aggregate_target more in the reasonable range of 1.2gig (IF you have the ram, not sure if anyone has 1.8 TERABYTES yet) and sort areas of 64m would be possible right there and then. No need for undocumented stuff, no need to get magical and mystical. It was a seemingly straight forward solution that was made into something, well, I don't understand why they did that for the problem they were facing.
ps: yes, sort_area_size does count in 9i with shared server. To say to people:
For example, the following set of parameters indicates that the Oracle DBA does not understand PGA management.
* sort_area_size=1048576 <-- sort_area_size is ignored when pga_aggregate_target is set and when workarea_size_policy =auto.
* pga_aggregate_target = 500m <-- The maximum default allowed value is 200 megabytes, this limits sorts to 25 megabytes (5% of 500m).
is just wrong - the "does not understand" -- wrong, sort_area_size, could be worded better, the 500m comment - totally outrageous (mine is set lower then 500m, do I have it "wrong"?)
For me, I commented on the SORT_AREA_SIZE thing because I thought that that was where Tom and Jonathan's criticism of the article was weak - not incorrect, just could read as a vendetta given the other articles around the place with the same omission. I did however end my original comment with
The rest of the article is very poor though so perhaps I'm just over generous.
I'd hate for anyone to think that I thought that it was a reputable article that enhanced either its publishers or its author's reputation. For the record when an Englishman describes something as very poor, don't read it as mild criticism.
The rest of the article is very poor though so perhaps I'm just over generous.
I'd hate for anyone to think that I thought that it was a reputable article that enhanced either its publishers or its author's reputation. For the record when an Englishman describes something as very poor, don't read it as mild criticism.
"ps: yes, sort_area_size does count in 9i with shared server."
Ding,ding! Yup, got it. The key of course being the expression "with shared server". A trap for the unwary. Which I totally missed from all the various discussions I read. I completely didn't get that the point being made was precisely because of the specific case of shared server. Thanks for clearing this up, much appreciated.
Now I can see where you folks are coming from: the way the original was written, it could be construed as blaming a dba for setting some values that are indeed correctly set. More information should have been provided, like for example the rest of the parameters: that would have made it clear if it was indeed a shared server or not.
Ah well, back to finding the duplicates in that 150 million row table, with only a 100M PGA_AGGREGATE_TARGET...
;)
Post a Comment
Ding,ding! Yup, got it. The key of course being the expression "with shared server". A trap for the unwary. Which I totally missed from all the various discussions I read. I completely didn't get that the point being made was precisely because of the specific case of shared server. Thanks for clearing this up, much appreciated.
Now I can see where you folks are coming from: the way the original was written, it could be construed as blaming a dba for setting some values that are indeed correctly set. More information should have been provided, like for example the rest of the parameters: that would have made it clear if it was indeed a shared server or not.
Ah well, back to finding the duplicates in that 150 million row table, with only a 100M PGA_AGGREGATE_TARGET...
;)
