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

I had the same issue, it just doesn’t work https://x.com/tmmdv/status/1523968999993843713 <https://x.com/tmmdv/status/1523968999993843713?s=46>

Regards
Timur Akhmadeev

On Sat, 23 Nov 2024 at 04:10, Satalabaha Oracle <dmarc-noreply_at_freelists.org> wrote:

> Sayan,
>
> My observation so far is that when I add the hint "/*+
> opt_param('container_data' 'current_dictionary') */ to the SQL, the
> v$sql_optimizer_env parameter container_data is set to current_dictionary.
> However, if I use any profiles or add other outlines to the SQL, which
> improves the runtime to 10 seconds, the "container_data" parameter is
> automatically set to "ALL". This happens even if I explicitly include the
> `opt_param('container_data' 'current_dictionary')` hint along with other
> outline hints.
>
> On Fri, Nov 22, 2024 at 10:49 PM Sayan Malakshinov <
> dmarc-noreply_at_freelists.org> wrote:
>
>> Satalabaha,
>>
>> Just try to remove all your hints and profile and add the only /*+
>> opt_param('container_data' 'current_dictionary') */
>>
>>
>> --
>> Kind regards,
>> Sayan
>>
>>
>> On Fri, Nov 22, 2024 at 5:15 PM Jonathan Lewis <
>> dmarc-noreply_at_freelists.org> wrote:
>>
>>>
>>> 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
>>>>
>>>
>>
>> --
>> Best regards,
>> Sayan Malakshinov
>> Oracle performance tuning engineer
>> Oracle ACE
>> http://orasql.org
>>
>
>
> --
> Thanks,
> Satalabaha
>

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