Re: Best course to understand why a bad plan is chosen by optimizer

On 9/16/19 3:14 PM, Mark W. Farnham wrote:
>
> Yes. Most especially when there is already a perfectly fine plan
> already in cache and most mysteriously when a parallel query manages
> to get yet better (but not matching plans) in children (which is of
> course a bug that then has to get reparsed again and again).
>
> Spending time generating bad plans in place of good plans happens very
> frequently.
>
> As for the RBO, the biggest problem with the CBO getting retrograde
> plans is that it was NOT designed with the RBO in mind.
>
> IF the design had been: 1) generate the RULE plan, 2) cost the RULE
> plan, 3) iterate trying to beat the RULE plan, then whence retrograde
> plans?
>
> But it was not.
>
> Of course that would have cast a future of deciding an heuristic
> initial try for access methods that did not exist for rule and adding
> them to the rule choice algorithm. Having helped to program a
> timesharing operating system, I would rate that as trivial.
>
> It was decided to push the customer base toward CBO only by NOT
> allowing new access methods in RULE. You may have noticed that the CBO
> is not risked for quite a few dictionary queries. (IF a  dictionary
> query goes off into the weeds, then entire instance of a database can
> get into a world of hurt for a variety of reasons, so I agree with not
> taking that risk.)
>
> So, to recap, the CBO never had the RBO in mind, and it is a silly
> waste to spin improving a plan that is very cheap on the first pass.
> Intervening to grind away and save a directive for an immediately
> cheap plan that needs to be as cheap as possible is the already
> implemented feature to answer the next question I’m guessing someone
> will ask.
>
> If the first plan tried is cheaper than some arbitrarily chosen
> “epsilon” (that might well be a parameter) then there is a choice for
> epsilon such that it is always wasteful to try to improve beyond it
> for the first execution of a query (because the next plan examination
> will take more than epsilon). It should have always existed, and the
> CBO should have been designed out of the box to cost the RULE plan.
> All of this was suggested by customers before the CBO was written. I
> know it was also suggested internally, but that might have not been
> before version zero of the CBO was written.
>
> That’s fine though. As well captured by Moans Nogood when he sees
> anything that will predictably require consulting to analyze to even
> see if there is a solution: Please do a lot of that. Here is how to
> secure our help….
>
>
Even worse, some customers were trying to emulate RBO using CBO and OPTIMIZER_INDEX_COST_ADJ. Essentially, the philosophy of RBO was "if there is an index that can be used, use it".  However, databases are rarely pure OLTP, so the parameter above was usually messing up reports. And yes, I ran into an optimizer burning CPU like crazy in version 9. I don't recollect which variety of version 9 it was, but after calling support, they gave me a parameter which was effectively determining the maximum number of passes that CBO was allowed to take. There is an AskTom discussion I still have bookmarked, from the time when Tom was an actual Tom.

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


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