Forum Stats

  • 3,839,008 Users
  • 2,262,438 Discussions
  • 7,900,834 Comments

Discussions

SQL patch (or profile force_match) on a system query produced by a refresh mview

Hi,

I migrate databases 12c to 19c. One database have hundred thousands objects. A dbms_mview.refresh produce this query that is very slow on this database.

sql_id 091fb1shwqyn8

select o.owner#, o.name, o.namespace, o.obj#, d.d_timestamp,

 nvl(d.property,0), o.type#, o.subname, d.d_attrs

from

 dependency$ d, obj$ o  where d.p_obj#=:1 and  (d.p_timestamp=nvl(:2,

 d.p_timestamp) or d.property=2) and  o.owner#=nvl(:3,o.owner#) and

 d.d_obj#=o.obj# order by o.obj#

With the sql_id I created a sql patch (or sql profile force_match) to inject the hint opt_param('_optimizer_push_pred_cost_based','false').

I execute the select and the plan note says that the sql patch (or profile) is used and the select is fast.

When I run the refresh view, the sql (sql_id 091fb1shwqyn8) is run implicitly by the refresh view, but the sql patch is not used !

Does sql patch is supposed to works in this kind of execution (system query execute implicitly by a refresh view) ?

Thanks a lot.

Best Answer

  • stephane_parenteau
    stephane_parenteau Member Posts: 32 Blue Ribbon
    Answer ✓

    Hi,

    Since my first post, I moved forward with the configuration of the CBO 19c (reading, testing, calibrating, etc). The application that I'm migrating is a mission critical app with about 30000 thousands SQL.

    During these works many of SQL have poor performance in the application and these SQL have similar pattern (explain plan "union-all" instead of "concatenation").

    I found that in the "or-expansions algorithm" some new optimizations have been introduced since 12.2+.

    So the main cause for these SQL (including the system query of my first post) is the new "or-expansions algorithm 12.2+" and the solution is to set the parameter _optimizer_cbqt_or_expansion=OFF.

    Here is some articles about this change.

    Bug 27541468 - Poor Query Performance With CBQT OR Exapansion (Doc ID 27541468.8)

    After Upgrade to 12.2 OR Expansion is Unexpectedly Bypassed for Some SQL Queries Which Can Result in Sub-optimal Execution Plans (Doc ID 2279072.1)

    Bug 26019148 - cbqt ore does not apply to correlated scalar subquery with oe (Doc ID 26019148.8)

    https://stackoverflow.com/questions/55075173/orcale-behavior-with-left-outer-join-with-or-condition

    https://blog.pythian.com/debugging-cbqt-expansion-10053-trace/

    And FYI, during my works with CBO 19c, I found also many of others SQL have poor performance. In this case the problem comes from the system stats MREADTIM, MBRC, SREADTIM.

    That is to say in 19c+ the "cpu cost model algorithm" have been changed. The solutions is to adjust these system stats or configure the database parameter _fix_control 27643128.

    Here is some articles about this change.

    Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1).

    https://blog.dbi-services.com/a-change-in-full-table-scan-costs-in-19c/.

    Thanks.

Answers

  • G R McHugh-Oracle
    G R McHugh-Oracle Member Posts: 9 Employee

    My proposed action here would be to create a SR ticket to report the issue because it is a system query.

    I tried to look up the system query in MOS but could not find any matches, so good chance the situation is unique to your environment since you have "100 thousand objects".

    I feel confident that TS will be able to find a solution to your issue not just to your benefit you but perhaps other customers as well.

    Thank you for this, 🙂

  • stephane_parenteau
    stephane_parenteau Member Posts: 32 Blue Ribbon
    Answer ✓

    Hi,

    Since my first post, I moved forward with the configuration of the CBO 19c (reading, testing, calibrating, etc). The application that I'm migrating is a mission critical app with about 30000 thousands SQL.

    During these works many of SQL have poor performance in the application and these SQL have similar pattern (explain plan "union-all" instead of "concatenation").

    I found that in the "or-expansions algorithm" some new optimizations have been introduced since 12.2+.

    So the main cause for these SQL (including the system query of my first post) is the new "or-expansions algorithm 12.2+" and the solution is to set the parameter _optimizer_cbqt_or_expansion=OFF.

    Here is some articles about this change.

    Bug 27541468 - Poor Query Performance With CBQT OR Exapansion (Doc ID 27541468.8)

    After Upgrade to 12.2 OR Expansion is Unexpectedly Bypassed for Some SQL Queries Which Can Result in Sub-optimal Execution Plans (Doc ID 2279072.1)

    Bug 26019148 - cbqt ore does not apply to correlated scalar subquery with oe (Doc ID 26019148.8)

    https://stackoverflow.com/questions/55075173/orcale-behavior-with-left-outer-join-with-or-condition

    https://blog.pythian.com/debugging-cbqt-expansion-10053-trace/

    And FYI, during my works with CBO 19c, I found also many of others SQL have poor performance. In this case the problem comes from the system stats MREADTIM, MBRC, SREADTIM.

    That is to say in 19c+ the "cpu cost model algorithm" have been changed. The solutions is to adjust these system stats or configure the database parameter _fix_control 27643128.

    Here is some articles about this change.

    Optimizer Chooses Expensive Index Full Scan over Index Fast Full Scan or Full Table Scan from 12.1 (Doc ID 2382922.1).

    https://blog.dbi-services.com/a-change-in-full-table-scan-costs-in-19c/.

    Thanks.