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

I thought such a change may impact results of some queries run by apps. Clearly it won’t (for now).
If you have time please open SR and get some feedback from Oracle support.

Regards
Timur Akhmadeev

On Sun, 24 Nov 2024 at 02:59, Satalabaha Oracle <satalabaha.oracle_at_gmail.com> wrote:

> 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