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

Hi Jonathan,

Thanks for the details.

I reviewed the hint report below [1], but I was unable to determine why certain hints were not utilized.

[1]:

https://gist.github.com/Satalabaha/4ae20275a218aaebc29df50484cf43f6

Concerning your second point, v$sql_optimizer_env revealed an issue [2]. Although we have applied patch 31142749 to our binaries and set container_data to CURRENT_DICTIONARY before restarting the instance, v$sql_optimizer_env still selects 'ALL' for container_data. Not sure if this is playing some role here.

SQL> sho parameter container_data;

NAME                                 TYPE                        VALUE
------------------------------------ ---------------------------
------------------------------
container_data                       string

https://gist.github.com/Satalabaha/649e9447d12440c2dcc6df22ea6d34cd

Regarding #3,all the 3 views all_types. all_arguements and all_procedures have undergone code changes.

Regarding #4, optimizer_secure_view_merging is set to true for both 12c and 19c.

On Thu, Nov 21, 2024 at 9:20 PM Jonathan Lewis <dmarc-noreply_at_freelists.org> wrote:

>
> 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
>>
>

-- 
Thanks,
Satalabaha

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