FW: rm RULE based optimizer != GOOD IDEA

Here is 2 cents worth.

-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Monday, April 25, 2005 7:01 AM
To: oracle-l_at_freelists.org
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Gints Plivna Sent: Monday, April 25, 2005 4:41 AM
To: robyn.sands_at_gmail.com; oracle-l_at_freelists.org 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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l