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

Hi Sayan,

Thanks for the quick response.

We changed this parameter to "container_data" at the database level based on some notes we found. I added the hint you suggested, and the query runtime decreased to 44 seconds. However, this is still far from the performance we had with 12c.

SQL> select banner from v$version;

BANNER

https://gist.github.com/Satalabaha/28afc543cb43662c3934ebb836d9c627

On Wed, Nov 20, 2024 at 6:23 PM Sayan Malakshinov <xt.and.r_at_gmail.com> wrote:

> Hi,
> Just add this hint:
> opt_param('container_data' 'current_directory')
>
> --
> Kind regards,
> Sayan
>
> On Wed, Nov 20, 2024, 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
>>
>

-- 
Thanks,
Satalabaha

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