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