Re: Very slow Oracle dictionary query after upgrade to 19.19 from 12.1.0.2

A couple of generic points, which may be relevant.

The definitions of the dictionary views may have changed between versions, so the internal query block numbers for the values internal components of the views may have changed, which means some of the (current) derived query block names may no longer come into existence and some of the hints to specify index or tables may now be pointing to the wrong query block.

Setting optimizer_features_enable does not guarantee that the exact behaviour of the specificed optimizer will re-appear (for example, bug fixes will not be reversed, but there are other details that may "misbehave").

You have a few occurrences of bind variables of the form :sys_b_nnn in the 19c which do not appear in the 12c plans - which suggests that you have cursor_shared set to force for 19c. This means the optimizer may have to generate a different plan so that it can be sure of getting the right result for all possible values (including NULL) of the bind variables - and that may force a change in the execution plan.

You have many examples of merge() in the hints - but they are "primitive" versions which don't specify which query block should be the target of the merge - which means Oracle could obey the hint but still do the wrong thing.

How similar in size and pattern are the data sets - where the hints are ambiguous you may find Oracle doing the wrong thing because it looks like the right thing according to the object stats.

Regards
Jonathan Lewis

On Wed, 20 Nov 2024 at 12:50, Satalabaha Oracle <dmarc-noreply_at_freelists.org> wrote:

> Hello Experts,
>
> I need your advice on troubleshooting a very slow Oracle dictionary query
> after upgrading from 12.1.0.2 to 19.19. The query execution plan and SQL
> monitoring details for both versions are provided in the links below. It
> seems the query is being called by `OracleDatabaseMetaData.java` in the
> application. This query uses a manual SQL profile and runs in a few
> milliseconds in 12.1, but takes over 2 minutes in 19c. I'm unsure why the
> 12.1 SQL profile isn't being utilized in 19c. In the gists below, I've
> executed the SQL using the outline for the 12c query. Any guidance on this
> issue would be greatly appreciated. Thanks!
>
>
> 19c:
>
> https://gist.github.com/Satalabaha/eae70f3753dd1bf0401d2d49e3910170
>
> 12c:
>
> https://gist.github.com/Satalabaha/7a9ecabe423603844e249d1cdf6f2ff7
>
> --
> Thanks,
> Satalabaha
>

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