RE: Hints
Date: Wed, 17 Aug 2011 09:06:29 -0400
Message-ID: <011501cc5cde$7b35f2d0$71a1d870$_at_rsiz.com>
This also relates to scope:
At the time Tim popularized these two parameters that shall not be named, the context was that 88.42% of e-business suite (EBS) plans were changed from sort of okay plans to catastrophically bad plans at some database release where the CBO was changed in a way that was probably good but which conflicted with the way 42% of EBS copy and paste based queries were constructed. Oracle's in house data texture did not trigger such a wide based plan degradation, and those plans that did and were important on Oracle's data texture probably got special attention and were fixed before production. (All percentages fictitious. Heavy surmise used about Oracle's in house systems.)
Tweaking the two parameters happened to be just about right to restore the vast majority of previous plans. So in the case that was popularized, the scope was accurate, but it was like taking two aspirin for your headache when there was a rock sitting on your head. So I think Tim beats himself up a little too much on this: We did not have the wherewithal to remove the rock any time soon, and the aspirin removed most of the short term pain. (I can't really remember what broad pattern was, but I think it had something to do with flopping to too many hash plans and hash plans not really being well executed back then.)
Likely your parameter change also worked similarly. The more controlled and effective code re-use is and code similarity is in an organization, the more likely it is that a global parameter change's effect will be broadly positive or negative.
The good news about that is, if you're able to do the further investigation to root cause that Greg (and most of us, certainly including Ian, who has a long record of following the evidence) advocates, you might find that investigation of a small number of queries will reveal a root cause that also fixes a wide range of sub-optimal query plans. It might point to a flaw in stats collection, "skew" in the data that is difficult to convey to the CBO, or even, gasp, a flaw in the CBO that can be fixed.
Playing around with global parameters (usually on a test system) where that is possible can be useful if viewed with detachment and an eye on the results. If that tips a broad set of plans in a good direction, that is a hard result to argue with as long as that is not the end of the story and there is a clear understanding that it was a band aid. Or maybe a tourniquet.
If a release change to the optimizer results in board based plan degradation, then a large scope temporary solution to put the plans mostly back to the way they were is probably a good idea. Especially if you need to take that patch, release, or upgrade for other reasons. The net positive change in the time and situation when Tim wrote his famous paper actually did fit the scope of the situation in the context of EBS at that date.
If data creep or statistics errors make a few queries go south in a way that a parameter change could fix, then it is very unlikely even a step toward a solution involves parameter changes.
Matching the scope of the solution to the scope of the problem is certainly an effective strategy that minimizes risk of degradation outside the scope of the current problem.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of MacGregor, Ian A.
Sent: Tuesday, August 16, 2011 6:56 PM
To: oracle-l_at_freelists.org
Subject: RE: Hints
I was not speaking about Tim's comments which were not harsh and quite reasonable, and also self-deprecating. Please don't jump to conclusions, but stick to the evidence. Seems I've heard that somewhere before. All I can say is that the astrophysicists are much happier now than before the change was made. For what its worth, none of the other 50 or so databases under my purview has had this parameter changed. I also thought I had included caveats about changing the parameter, though perhaps not all.
Where I disagree with is the notion that the parameter should never be changed. FWIW, doing as you suggest is more proper, and in the end would give better throughput than the change in the parameter. Indeed that was the path I had chosen, when I got the call suggesting changing it, and after singing: "NO!, NO!, NO, we don't do that no more", gave it a try and instantaneously the system was performing much better. The evidence is happy customers.
From: Greg Rahn [greg_at_structureddata.org] Sent: Tuesday, August 16, 2011 2:39 PM
To: MacGregor, Ian A.
Cc: oracle-l_at_freelists.org
Subject: Re: Hints
Cases like this warrant further investigation - as with any execution plan change. For whatever reason, a faster executing plan was found, take the time to understand why this is the case. Is it a stats related thing? Is it because the costing model assumes something that is not correct in this environment? Is it related to the current environment resource limitations?
Tim's comments/rants are completely valid in my opinion (perhaps harsh, but
fair) - way too many people just blindly apply something (like "best
practices")
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Aug 17 2011 - 08:06:29 CDT