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

A completely different take on the problem. I ran into a similar issue with a view that included some of the same objects, like OBJ$ . The old system would give good performance on a known problematic query, but moving up from 12c to 21c we had a regression in performance. After weeks of floundering around and testing, I finally discovered that on the old system, there had been a modification to one of these objects DDL, where it turned on something like cache or inmemory for the object. This change did not make it over to the new system. I seem to recall that there are some protections to keep from modifying those objects, but they had been bypassed by a fix control in 12c. When we reverted the change to be the same as the 21c object, the query performed identically in performance.   Sorry I do not remember more details, once the problem was identified I handed it back to the DBA team with some choices on how to resolve it, but I am not sure which one they picked. So my advice is to check all the underlying objects in the view to make sure they match on old and new system.

On Fri, Nov 22, 2024 at 12: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
>>
>

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