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

One more thought. Do you have the same setting for optimizer_secure_view_merging in both systems. The view _current_edition_obj is access through a nested loop and "view pushed predicate" in 12c, but you have a hash join without predicate pushing in 19c. A change in that predicate might explain it; but possibly there's some other reason why predicate pushing hasn't taken place.

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