Well the philosophy of the rule based optimizer could be reduced to a
single sentence: "if there is an index path, use it". That is about it.
I have seen many attempts to emulate RBO using either AUX_STATS$ or
OPTIMIZER_INDEX_COST_ADJ, the latter typically set to something like 25
or 50, if the developer is "conservative". In all such cases there were
problems with batch jobs and reports. Above settings tend to produce a
ton of index scans and NL joins, which are not always the best idea.
My latest philosophy is to give optimizer all the information it needs
to do its job: statistics and histograms, even to set
OPTIMIZER_DYNAMIC_SAMPLING to 11. There was a really nasty bug with that
in early versions of 12.1 which has caused latch contention when
OPTIMIZER_DYNAMIC_SAMPLING was set to 11. I haven't seen that in 12.2
and the CBO, with occasional quirks, does its job remarkably well.
On 6/26/19 5:35 PM, Neil Chandler wrote:
--
17 or 26 heuristic rules?
The manuals only ever listed 15 RBO Heuristics. Anyone care to expand the list beyond that?
*Access paths and their ranking*
Path 1 Single Row by Rowid
Path 2 Single Row by Cluster Join
Path 3 Single Row by Hash Cluster key with Unique or Primary Key
Path 4 Single Row by Unique or Primary Key
Path 5 Clustered Join
Path 6 Hash Cluster Key
Path 7 Indexed Cluster Key
Path 8 Composite Index
Path 9 Single-Column Index
Path 10 Bounded Range Search on Indexed Columns
Path 11 Unbounded Range Search on Indexed Columns
Path 12 Sort-Merge Join
Path 13 MAX or MIN of Indexed Column
Path 14 ORDER BY on Indexed Column
Path 15 Full Table Scan
Neil.
sent from my phone
On 26 Jun 2019, at 17:28, Mark W. Farnham <mwf@xxxxxxxx <mailto:mwf@xxxxxxxx>> wrote:
NODS. AND it should be noted that the fact that some of Oracle’s own recursive queries still reporting as using rule does NOT matter regarding the desupport note. Oracle is not the customer and they can do whatever they need to in order to make the RDBMS engine run fast.
The “supported” way to simulate RULE is to write the plan with hints (very often a set of no_merge with clauses is sufficient to model RULE’s choices, which are static and not dependent on cardinality). And once you’ve done that, an outline is not actually needed.
If you cannot simulate the RULE plan with just no_merge hints (and possibly some use_index hints), then I recommend you consult JL and Chris Antognini sites and books and sqlmaria for how to beat your query into submission.
IF you think RULE can be good for your query, that is a declaration that stats don’t matter for that query. You also have no “strait jacket” going this route and if there seems to be an effective hash plan here or there, you and hint that (whereas if RULE still functioned you would be stuck with those (17 or 26, argue amongst yourselves) heuristic rules and capabilities.
mwf
