Forum Stats

  • 3,872,915 Users
  • 2,266,490 Discussions
  • 7,911,382 Comments

Discussions

Query got slow just after db minor upgrade 19c

User_8URBJ
User_8URBJ Member Posts: 3 Green Ribbon
edited Sep 28, 2022 1:40PM in General Database Discussions

One of the query got slow after minor upgrade from 19.10 to 19.14. noticed that execution plan got changed. having full table scan and not using index. although, index are in valid state. we rebuild index, drop & re-create index but that didn't help. we have 19c Standard edition without tuning pack. can someone help me please ?

Note:- i have created backup before upgrade. so if anything required, i can bring them back.

SELECT /*+ FIRST_ROWS */ c.id FROM schema.table1 c WHERE ((contains(c.description, '12345' ) > 0 OR contains(c.key_label, '12345' ) > 0 OR EXISTS (SELECT 1 FROM schema.table2 m WHERE c.id = m.case_id AND m.deleted_flag = 0 AND contains(m.case_comment, '12345' ) > 0)) AND c.case_type = 'issue' );

Answers

  • asahide
    asahide Expert Technical Engineer Member Posts: 1,492 Gold Trophy
  • JohnWatson2
    JohnWatson2 Member Posts: 4,511 Silver Crown

    What plan do you get if you add the use_concat hint?

  • JohnWatson2
    JohnWatson2 Member Posts: 4,511 Silver Crown

    Please can you show the explain plan using dbms_xplan.display( format=>'typical'), so that one can see the predicate section and the hint report?

    User_8URBJ
  • JohnWatson2
    JohnWatson2 Member Posts: 4,511 Silver Crown
    edited Sep 22, 2022 10:33AM

    You could look into why the hint was unused. Alternatively, you could code the concat by hand:

    SELECT c.id
    FROM  edqconfig9.dn_case c
    WHERE ( Contains(c.description, '5108029235') > 0 AND c.case_type = 'issue' )
    union 
    SELECT c.id
    FROM  edqconfig9.dn_case c
    WHERE ( Contains(c.key_label, '5108029235') > 0 AND c.case_type = 'issue' )
    union
    SELECT c.id
    FROM  edqconfig9.dn_case c
    WHERE ( EXISTS (SELECT 1
                FROM  edqconfig9.dn_casecomment m
                WHERE c.id = m.case_id
                   AND m.deleted_flag = 0
                   AND Contains(m.case_comment, '5108029235') > 0) AND c.case_type = 'issue' )
    

    UNION ALL would be more efficient, but I can't be bothered to write that for you.

    By the way, would it be possible for you to give a bit of feedback that would let people know if you find their advice helpful? So far you have not said "thank you" to me or to your other respondent.

    User_8URBJ
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,116 Blue Diamond

    You're using 19c so you should be using or-expansion, not concatenation - hinted with or_expand().

    If you look at the hint report you can see that the optimizer has recognized the hint but not used it (U for unused, reporting use_concat for query block sel$1 (the main query).

    It's possible that in the upgrade from 19.10 to 19.14 someone noticed that the absence of a NOT NULL constraint somewhere might introduce wrong results in some cases and therefore bypassed the expansion hint.

    Alternatively the optimizer won't do a concat unless hinted in your version, and your hint is the wrong form for the use_concat() for what you want to do - and that would make it legal but unusable.

    Finally there may have been some changes in the costing and cardinality calculations that make the full scan with filter appear to be cheaper than OR-expansion.


    John Watson made a comment about non showing you the UNION ALL version of the query - if c.id is the primary key (or a unique non-null key) for the table then using a UNION instead of a UNION ALL is not going to make any difference to the result - and that's presumably why he didn't bother showing you the more complex query that would allow LEGAL duplicates to appear while ensuring that "ACCIDENTAL" duplicates were not generated.


    Regards

    Jonathan Lewis