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

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

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