Re: Question on 19C Optimizer

Not answering your question, but I have 19.11.0.0 on hand, so I ran up a quick test of a 9 table join.
The fix control is off by default (which is a recent strategic decision from Oracle Corp. for fix-controls), but when enabled the fix control showed two extra sections of optimisation:

The first 4 headings below have been around for a long time, and took my sample query up to join order 95

Considering cardinality-based initial join order. Considering RBO ranking based initial join order. Considering join cardinality based initial join order. Considering join cardinality with NL index access based initial join order.

The two new headings added a further 9, then 11, join orders respectively

Considering join selectivity based initial join order. Considering table filter selectivity based initial join order.

Regards
Jonathan Lewis

On Sat, 24 Jul 2021 at 12:28, Lok P <loknath.73_at_gmail.com> wrote:

> Hello All, We don't have yet access to the database on which this
> issue is reproducible, but want to know here from experts if this below
> issue/bug has been encountered by anyone using 19C and thus has to be
> patched for safety.
>
> We got complaints in which one third party application which frequently
> executes some big complex queries using many tables(10+
> tables/views/subqueries etc) joins. And those are many times dynamically
> formed by that tool and submitted to the database. And those queries used
> to finish in a few seconds earlier(when DB version was 12.1.0.2 with OFE
> 11.2.0.4) but now in a lower environment which they try to test those
> queries before migration and is on 19C(19.9.0.0.0), it was observed by the
> team that those are running for hours and then have to be killed. And they
> are suspecting the below bug is impacting many of the queries for this
> application.
>
> Bug 30537403 - Suboptimal Join Order for SQL Statements with Large Number
> of Tables (Doc ID 30537403.8)
>
>
> This bug looks too generic and so we are a bit worried as we just can't go
> adding leading hints for all queries. But then considering so many
> application runs on the below 19.9 versions, wanted to know from experts
> here if this occurs only on certain situation? Or we really have to have
> patch for this before migrating to 19C?
>
> Regards
> Lok
>

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