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

If you try a simple query such as 'select count(distinct package_name) from all_arguments' with container_data set to 'ALL' you'll find the plan is very different from the plan you get for container_data = 'CURRENT_DICTIONARY' - this difference between the 12c and the 19c tests would be sufficient to explain why the timing is so different (and, of course, why so many hints that reference specific query block names are unresolved or unused - all the extra objects mean that you don't necessarily get the query blocks referenced in the hint, and changes in joins means some of the hints can't apply any more).

It is possible that the code changes to the views (especially if they bring in new tables or subqueries) are very likely to have the same effect.

Regards
Jonathan Lewis

On Fri, 22 Nov 2024 at 11:25, Satalabaha Oracle <satalabaha.oracle_at_gmail.com> wrote:

>
> 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
> CURRENT_DICTIONARY
>
> [2]:
>
> 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