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@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Mark W. Farnham <mwf@xxxxxxxx>
Sent: 15 September 2019 17:20
To: 'Mladen Gogala'; krishsingh.111@xxxxxxxxx; '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@xxxxxxxxx]
Sent: Sunday, September 15, 2019 11:20 AM
To: Mark W. Farnham; krishsingh.111@xxxxxxxxx; '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
--
//www.freelists.org/webpage/oracle-l
