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

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