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

Hello Satalabaha,
your Oracle 19c execution plan uses statistics feedback (and the re-optimized child cursor 1).

You can check the additional applied hints with the following query: SQL> select hint_text from V$SQL_REOPTIMIZATION_HINTS where sql_id = '817wu40h1hnka';

Anyway if all provided hints are valid and applied can not be verified by the provided data.

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: www.soocs.de
Twitter: _at_OracleSK

> Satalabaha Oracle <dmarc-noreply_at_freelists.org> hat am 20.11.2024 13:49 CET geschrieben:
>
>
> 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