Home -> Mailing Lists -> Oracle-L -> RE: Best course to understand why a bad plan is chosen by optimizer

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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Mon, 16 Sep 2019 15:14:30 -0400
Message-ID: <06be01d56cc2$f77123e0$e6536ba0$_at_rsiz.com> 








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….



From: Andy Sayer [mailto:andysayer_at_gmail.com] 
Sent: Monday, September 16, 2019 1:51 PM
To: mwf_at_rsiz.com


Cc: Chris Taylor; Mladen Gogala; ORACLE-L; jonathan_at_jlcomp.demon.co.uk; krishsingh.111_at_gmail.com
Subject: Re: Best course to understand why a bad plan is chosen by optimizer

 



Mark,

 



Have you hit a case where the total elapsed time of multiple executions of a statement has been significantly effected because the optimizer decided to keep going? I haven’t since version 11.2 at least. I’ve seen cases where parse time was huge but they have always been actual bugs rather than just the optimizer optimizing till it can’t optimize any more. 

 



I’m sure your concerns were valid back when everything was designed with the RBO in mind but I don’t think they’re very well placed now.

 



Thanks,



Andrew

 

 



On Mon, 16 Sep 2019 at 16:52, Mark W. Farnham <mwf_at_rsiz.com> wrote:



nods. what is still missing is if cost < epsilon after even one plan (or at
any point), stop planning. The 8.1 psuedo fix was a complicated hidden
concession to the better strategy that pretended to address it.



The algorithm below is compatible and not a horrible idea, but it is not the
absolute bail-out for cheap queries.



Picture a gps guidance system doing laps in a circular driveway at the
destination trying to get just a little bit closer when you are already
there.



But a simple bail-out was somehow unsatisfying to someone somewhere in the
decision loop.



-----Original Message-----




From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Jonathan Lewis


Sent: Sunday, September 15, 2019 1:35 PM
To: 'Mladen Gogala'; krishsingh.111_at_gmail.com; 'Chris Taylor'; mwf_at_rsiz.com
Cc: 'ORACLE-L'


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



Mark,



I think there's been a limiter for a long time (since 8.1 at least) which
says somthing like:



stop optimizing when  best cost so far < 0.3 * join orders tested so far *
number of "non-single-row" tables.



("single-row" tables are tables that are guaranteed by the combination of
constraints and predicates to return no more than one row, "non-single-row"
is all the rest.)






Regards


Jonathan Lewis





From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
behalf of Mark W. Farnham <mwf_at_rsiz.com>
Sent: 15 September 2019 17:20


To: 'Mladen Gogala'; krishsingh.111_at_gmail.com; 'Chris Taylor'
Cc: 'ORACLE-L'


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



Clarifying because the context of adaptation has been removed: any other
parameter combinations [regarding plan feedback and adaptation].



Ironically feedback adaptation is part of the attempt to
"make_things_go_faster!"



Graham Wood and I separately proposed (before the release of 7, if memory
serves) "don't keep optimizing if the cost of further optimization exceeds
the cost of the query." This unfortunately got translated into a knob to
limit permutations attempted instead of "the cost is below X, stop trying to
improve it" and hilarity ensued.



That, and "cost the rule based plan, then try to beat it" as a strategy to
avoid retrograde plans moving from RULE to COST would have saved a whole lot
of grief* since 1989. Unfortunately a lot of code (probably nearly all of
which was later discarded) costing the row sources NOT in the order of what
RULE would have done was already designed so the "that's not compatible with
how we do cost" was the reply instead of "holy cow, that is a great idea." I
believe Graham and I both independently suggested that as well.



Together those two bits would have been the parameter  setting
"make_things_go_slower=FALSE."



*grief: also known, as per Moans Nogood, the wealth generation mechanism for
a lot of consultants and consulting companies.



From: Mladen Gogala [mailto:gogala.mladen_at_gmail.com]
Sent: Sunday, September 15, 2019 11:20 AM
To: Mark W. Farnham; krishsingh.111_at_gmail.com; 'Chris Taylor'
Cc: 'ORACLE-L'


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




On 9/15/19 10:55 AM, Mark W. Farnham wrote:
What we *should* have is a session and system level single parameter:
no_adapt=TRUE|FALSE that either completely disables or allows following any
other parameter combinations.




What we should have is the parameter proposed by Jonathan Lewis when Oracle
9i was the current version: "make_things_go_faster". Unfortunately, we still
don't have that desperately needed parameter.






--




Mladen Gogala



Database Consultant



Tel: (347) 321-1217


--



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






--



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








--



http://www.freelists.org/webpage/oracle-l
Received on Mon Sep 16 2019 - 21:14:30 CEST












Original text of this message