Re: Question on 19C Optimizer

Thank you.

 Few things , while looking in MOS regarding this bug I got below doc( docid- 2773715.1) which lists all the Things/bugs related to SQL performance needs to be considered before moving into 19c. And this bug is also in that list. Many bugs with respect to automatic stats collection also listed. However I do see for many bugs(including this one) there seems to be no one off backport patch or workaround available other than moving to 19.11. And many of those are fixed only on 19.11 version(including this bug). So does that mean if we plan to migrate to 19C it should be at least 19.11 or above? Any lower version , we have to suffer from these bugs which seems to have no workaround?

https://support.oracle.com/MMOS/faces/ui/km/kmDocumentDisplay.jspx?kmDocId=2773715.1&idx=1&totalResultsDisplayed=2

On Sat, 24 Jul 2021, 7:27 pm Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

> 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