Re: How to switch the RULE hint in outlines hints in 12.2 on standard edition.

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_at_rsiz.com
> <mailto:mwf_at_rsiz.com>> 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
>>
>>

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


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