RE: Hints
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <ian@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 17 Aug 2011 09:06:29 -0400
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of MacGregor, Ian A.
Sent: Tuesday, August 16, 2011 6:56 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]
Sent: Tuesday, August 16, 2011 2:39 PM
To: MacGregor, Ian A.
Cc: oracle-l@xxxxxxxxxxxxx
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")
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
