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

Hi Timur ,

Thanks for providing the details. In your blog, I see a risk being highlighted of changing the parameter container_data at instance level. Can you share more information on this?

Thanks,
Satalabaha

On Sat, 23 Nov 2024 at 1:52 PM, Timur Akhmadeev <timur.akhmadeev_at_gmail.com> wrote:

> 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