FW: rm RULE based optimizer != GOOD IDEA
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: "Oracle-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Apr 2005 11:53:59 -0400
Here is 2 cents worth.
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Monday, April 25, 2005 7:01 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: rm RULE based optimizer != GOOD IDEA
Several folks have suggested over the years that Oracle could end all
vestiges of this controversy by merely seeding the COST BASED permutation
search with whatever the rule based optimizer generates for a plan. Then the
CBO would only use a plan different from rule if it predicted a lower cost.
In theory then the plan delivered by the CBO would only execute worse than
the RBO plan due to bad stats or a bug, but you would know that that the
RULE plan was tried.
In the vast majority of cases then, you would get a plan at least as good as
rule. Without seeding the search you are left at the mercy of "permutations"
and the search construction algorithm as to whether the rule plan would get
tried. Even if the rule plan rarely wins the time saved in avoiding useless
debate (and redirected to load management and other worthwhile activities
such as eating pizza with appropriate libations) would improve the
performance of databases the world over immeasurably. Mogens might want to
comment on the pizza and beer versus consulting dollars tradeoff.
A long deferred project that I'm itching to do is to see if you can
materially affect the cost of the cost based optimizer by different syntax
structures. While I would also actually prefer to see the queries written as
naturally as possible, I'm curious if you can influence the order in which
the CBO tries alternatives, particularly if it is taking a lot of
permutations to get a good plan. I have not come across any documentation on
the subject. And if someone takes the lid off that can of worms and reports
techniques for pruning the search to a good plan, we'll enter a new screwy
age of isofunctional query text manipulation for tuning. Ugh. Suggestions
about "weak, strong, and mandatory" plan hints made in the early 1990's fell
on deaf ears. Weak would (as has been suggested, merely seed the the
search), strong is what we have, and mandatory would report an error if the
hinted plan couldn't be used. I'm thinking that one or more "weak" hints
could tell the CBO to try some things without locking you in to a solution.
I suppose you lose the last few permutations it would otherwise have tried
if it counts the weak hints in the permutations limit.
Another hint I'd love to see is the "good enough" hint that would stop
looking when the cost got below whatever you said was good enough.
Are folks tracking the fraction of cpu and elapsed time spent in parsing? I
know my impression of the world of Oracle databases is radically skewed by
the problems I'm asked to solve, and I've seen placed where the number of
permutations was set to very high values.
Also, regarding your references, run this simple test:
1) create a composite index and use all the columns with ANDs as per rule 8
2) look at the rule plan
3) stop using the last column in the composite index as part of the
predicate (in contradiction to rule 8)
4) look at the rule plan
Let me know if you find that the document is accurate in stating that all
the columns of a composite index must be used.
I'm not sure when the list of rule win orders got shortened by 3, either,
and to this day I've never seen a complete rendering of the tie breaking
rules that was accurate. (That doesn't mean there isn't one.)
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Gints Plivna
Sent: Monday, April 25, 2005 4:41 AM
To: robyn.sands@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: rm RULE based optimizer != GOOD IDEA
One of our developers used RULE hint widely in our latest project. As
a result we had to inspect almost all of them (taking time and $$$)
when moving to production. So from now I'm planning to allow RULE then
and only then if developer can explain _ALL_ RBO rules :)))
(http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/rbo.htm
#38864,
http://www.oreilly.de/catalog/orsqltunpr/chapter/)
As a result I hope that those who will be able to do that would
understand that it is a bad idea.
Gints
On 4/22/05, Robyn <robyn.sands@xxxxxxxxx> wrote:
> I for one will be happy to see the rule based optimizer go. Most of
> our developers and dbas add rule hints as a quick fix without taking
> the time to actually figure out the problem. Then, they announce
> that the query completed 'really fast' in dev and move it to
> production, where it may or may not run 'really fast'. Either way,
> there is no baseline to work from if we need to continue to tune it.
> Plus, the rule hint is likely to exist through the next 3 upgrades,
> and no one remembers that it exists, much less knows if it's still
> making things 'really fast'.
>=20
> As a result, we have lots of legacy queries that need to be
> reevaluated and there have been many times when REMOVING the rule hint
> in an old query sped things up considerably.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » FW: rm RULE based optimizer != GOOD IDEA
