Forum Stats

  • 3,770,515 Users
  • 2,253,129 Discussions
  • 7,875,493 Comments

Discussions

How to enforce index hint to a SQL !

DBA112
DBA112 Member Posts: 517 Bronze Badge
edited Jun 14, 2019 10:23AM in General Database Discussions

Dear DBA Friends,

DB version - 11.2.0.4.   Below are 2 identical SQLs with their execution plans.  The only difference between 2 SQLs is I am using an index hint in the first SQL.

With hint, query completes in < 1 sec. Without the hint,  Optimizer chooses a different index resulting in query time out .. (after 5 mins or so..).

The timing out SQL is coming from app, I cannot pass the hint directly.  So, I created a profile on the good SQL and copied it to the bad SQL, using technique from below link:

(https://oraclespin.com/2012/05/13/example-of-using-sql-profile-to-use-switch-to-a-different-execution-plan/ )

Now, when the bad SQL is executed from the app, I can see it picked up the profile I created but not the underlying index from the profile. It still uses bad index and timing out..

How can I enforce the good index  - "XIE2FNM_VSBL_MSG" to the SQL ?  Kindly help...

GOOD SQL

select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/    MSG.MSG_ID,     MSG.VSBL_MSG_ID,     MSG.SRCH_4_FLD_VAL,     MSG.SRCH_3_FLD_VAL,     MSG.SRCH_5_FLD_VAL,     MSG.MSG_TRSM_DTTM,     MSG.DISP_4_FLD_VAL,     MSG.DISP_3_FLD_VAL,     MSG.DISP_1_FLD_VAL,     MSG.DISP_2_FLD_VAL,     MSG.SRCH_1_FLD_VAL,     TRK.RESEND_DT,     MSG.CRE_DTTM FROM     FNM.FNM_VSBL_MSG MSG,     FNM.BCS_INV_RESEND_TRK TRK WHERE     MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = '123456'   AND (('INVOICENUMBER' = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT     * FROM     TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))) OR ('INVOICENUMBER' = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT     * FROM     TABLE(CAST(FNM_GN_IN_STRING_LIST('') AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT     MAX(TRK1.RESEND_DT) FROM     FNM.BCS_INV_RESEND_TRK TRK1 WHERE     TRK1.INV_NUM = TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESC;Plan hash value: 1944127456---------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                               | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |---------------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                        |                       |      1 |        |      2 |00:00:00.08 |      12 |      7 |       |       |          ||   1 |  SORT ORDER BY                          |                       |      1 |      1 |      2 |00:00:00.08 |      12 |      7 |  2048 |  2048 | 2048  (0)||*  2 |   FILTER                                |                       |      1 |        |      2 |00:00:00.08 |      12 |      7 |       |       |          ||   3 |    NESTED LOOPS OUTER                   |                       |      1 |      1 |      2 |00:00:00.08 |      10 |      7 |       |       |          ||   4 |     NESTED LOOPS                        |                       |      1 |      1 |      2 |00:00:00.06 |       6 |      5 |       |       |          ||   5 |      VIEW                               | VW_NSO_1              |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          ||   6 |       HASH UNIQUE                       |                       |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |  1697K|  1697K|  487K (0)||   7 |        COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |      1 |      1 |00:00:00.01 |       0 |      0 |       |       |          ||*  8 |      TABLE ACCESS BY INDEX ROWID        | FNM_VSBL_MSG          |      1 |      1 |      2 |00:00:00.06 |       6 |      5 |       |       |          ||*  9 |       INDEX RANGE SCAN                  | XIE2FNM_VSBL_MSG      |      1 |      4 |      4 |00:00:00.04 |       4 |      3 |       |       |          ||* 10 |     INDEX RANGE SCAN                    | XPKBCS_INV_RESEND_TRK |      2 |      1 |      2 |00:00:00.01 |       4 |      2 |       |       |          ||  11 |    SORT AGGREGATE                       |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          ||  12 |     FIRST ROW                           |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          ||* 13 |      INDEX RANGE SCAN (MIN/MAX)         | XPKBCS_INV_RESEND_TRK |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |---------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=))   8 - filter(("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))   9 - access("MSG"."MSG_ID"="COLUMN_VALUE" AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')       filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  10 - access("MSG"."MSG_ID"="TRK"."INV_NUM")  13 - access("TRK1"."INV_NUM"=:B1)Note-----   - cardinality feedback used for this statement54 rows selected.

BAD SQL

select * from (SELECT    MSG.MSG_ID,     MSG.VSBL_MSG_ID,     MSG.SRCH_4_FLD_VAL,     MSG.SRCH_3_FLD_VAL,     MSG.SRCH_5_FLD_VAL,     MSG.MSG_TRSM_DTTM,     MSG.DISP_4_FLD_VAL,     MSG.DISP_3_FLD_VAL,     MSG.DISP_1_FLD_VAL,     MSG.DISP_2_FLD_VAL,     MSG.SRCH_1_FLD_VAL,     TRK.RESEND_DT,     MSG.CRE_DTTM FROM     FNM.FNM_VSBL_MSG MSG,     FNM.BCS_INV_RESEND_TRK TRK WHERE     MSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND MSG.SRCH_4_FLD_VAL = '123456'   AND (('INVOICENUMBER' = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT     * FROM     TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))) OR ('INVOICENUMBER' = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT     * FROM     TABLE(CAST(FNM_GN_IN_STRING_LIST('') AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT     MAX(TRK1.RESEND_DT) FROM     FNM.BCS_INV_RESEND_TRK TRK1 WHERE     TRK1.INV_NUM = TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESC;Plan hash value: 3354198206--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                             | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                      |                       |      1 |        |      2 |00:08:49.14 |     108K|  66422 |   1215 |       |       |          |         ||   1 |  SORT ORDER BY                        |                       |      1 |      1 |      2 |00:08:49.14 |     108K|  66422 |   1215 |  2048 |  2048 | 2048  (0)|         ||*  2 |   FILTER                              |                       |      1 |        |      2 |00:08:49.14 |     108K|  66422 |   1215 |       |       |          |         ||*  3 |    HASH JOIN SEMI                     |                       |      1 |      1 |      2 |00:08:49.14 |     108K|  66422 |   1215 |    13M|  2360K| 3658K (1)|   11264 ||   4 |     NESTED LOOPS OUTER                |                       |      1 |      1 |    101K|00:08:48.48 |     108K|  66107 |      0 |       |       |          |         ||   5 |      TABLE ACCESS BY INDEX ROWID      | FNM_VSBL_MSG          |      1 |      1 |    101K|00:08:45.67 |   66038 |  66037 |      0 |       |       |          |         ||*  6 |       INDEX RANGE SCAN                | XIE11FNM_VSBL_MSG     |      1 |      1 |    101K|00:00:01.17 |     671 |    670 |      0 |       |       |          |         ||*  7 |      INDEX RANGE SCAN                 | XPKBCS_INV_RESEND_TRK |    101K|      1 |      7 |00:00:02.33 |   42087 |     70 |      0 |       |       |          |         ||   8 |     VIEW                              | VW_NSO_1              |      1 |   8168 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         ||   9 |      COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |   8168 |      1 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         ||  10 |    SORT AGGREGATE                     |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         ||  11 |     FIRST ROW                         |                       |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         ||* 12 |      INDEX RANGE SCAN (MIN/MAX)       | XPKBCS_INV_RESEND_TRK |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |         |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=))   3 - access("MSG"."MSG_ID"="COLUMN_VALUE")   6 - access("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS')   7 - access("MSG"."MSG_ID"="TRK"."INV_NUM")  12 - access("TRK1"."INV_NUM"=:B1)48 rows selected.

Thanks..

AndrewSayerDBA112Mohamed Houri
«13

Answers

  • KayK
    KayK Member Posts: 1,685 Bronze Crown
    edited Jun 4, 2019 1:46AM

    Hi DBA,

    maybe the optimizer can't realize that the list of msg_id coming from inlist-select will be short, don't know how selective this part really is.

    Without the hint, the optimizer choose index XIE11FNM_VSBL_MSG using 3 columns with single literals. Not a bad idea at all.

    What happens if combine these two indexes ?

    Try an index like this

    new_FNM_VSBL_MSG ( MSG_TYP_CD, MSG_CAPTR_STG_CD, SRCH_4_FLD_VAL, MSG_ID )

    This will get you the columns from XIE11 plus the msg_id which you need to restrict  the result set.

    regards

    Kay

    DBA112
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,792 Gold Crown
    edited Jun 4, 2019 9:17AM

    The output from your "Good Plan" reports "Cardinality feedback used" - this may explain why the optimizer CHOSE to use the index you think you had ordered: the optimizer had acquired information that the collection consisted of just one row. The "Bad Plan" shows the default (for 8KB blocks) 8168 rows which is why it doesn't use the collection as the build table in the hash join.

    Execute the code to produce the good  plan again but use the 'outline' format option to generate the full set of hints for the plan - there's likely to be at least 15 or them - and show them to us. You will need to create a profile holding most of them for Oracle to reproduce the plan you want.

    Alternatively include the 'alias' format option in the call to dbms_xplan so that we can work out the fully qualified aliases that Oracle gives to the various query blocks and tables and we may be able to work out a "legal" minimum set.  This will include an UNNEST for the "in subquery", a NO_UNNEST, NO_PUSH_SUBQ for the "= max" subquery, a LEADING, 2 USE_NL, and an INDEX() hint for the main query block.

    Another possibility - which may work and would be a better strategic option - is to take action on the statistics:

    • option 1:  create a column group (extended stats) on the pair (MSG_TYP_CD, MSG_CAPTR_STG_CD) which may have a fairly small number of distinct values and some reasonable degree of correlation.  If that is the case then this may result in Oracle estimating a larger number of rows, and higher cost, of the bad driving range scan and therefore changing indexes.
    • option 2: check what histograms you have on the "%_CD" columns that appear in the two indexes - perhaps you need to create histograms to let the optimizer know that you're selecting on very popular values from skewed distributions.  Again a change in estimated rows may be sufficient to modify the plans.

    A final thought - does your production query run with bind variables or with the literals you've supplied ?  You may be asking us to solve a problem that isn't the same as the one that needs to be solved.

    Regards

    Jonathan Lewis

    [Edited to correct several typos]

    AndrewSayerDBA112DBA112
  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited Jun 4, 2019 10:16AM
    Now, when the bad SQL is executed from the app, I can see it picked up the profile I created but not the underlying index from the profile. It still uses bad index and timing out..

    How did you determine this?

    It's not usual for a profile to be matched but only partially applied - that would normally, but not always, indicate that the profile hints were incomplete/wrong somehow.

    Cardinality feedback seems to be adjusting is the estimates in your collection, FNM_GN_IN_STRING_LIST , which default to 8168.

    If you had control of the SQL I would suggest adding a CARDINALITY hint to adjust the number of rows lower - depending on how many elements the application can put into the collection and assuming that a static lower estimated cardinality delivered a plan which worked for all circumstances.

    Or, you'd need to create a function as a wrapper around your type and then use extensible optimizer to associate statistics with that function - that means changing the SQL which means you could just add a hardcoded cardinality hint..

    Otherwise you could potentially use a SQL profile to adjust the cardinality estimate using OPT_ESTIMATE (undocumented) which would be similar to using a profile to force the index... in which case back to my first question.

    DBA112
  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 4, 2019 1:36PM

    Jon,

    Thank you.  Below  is the 'outline' portion of the good execution plan.  

    This time I created profile with full set of hints from the outline, also attaching the syntax I used to create the profile.  Still, no luck enforcing the cheaper index.

    I created extended stats, re-gathered stats after creating ext. stats,  but SQL from the application is still going for the bad index.

    I see we have "frequency" histograms on the _CD col's.  (NUM_DISTINCT for MSG_TYP_CD  and  MSG_CAPTR_STG_CD  are  20 and 5 respectively, and NUM_BUCKETS 17 and 5).

    The business folks are choosing the Invoice # (literal values) from drop down and submitting in the UI.  This means query is using bind variables, correct ?   (Also, I can see bind variables in the SQL execution..)

    For some Invoice #'s, SQL seems to run fine.. But for the one in question.. it's timing out.

    I truly admire your approach in going after the actual root cause and try to understand Optimizer's behavior, rather than applying band-aid hints.

    In this case, if feasible, I would like to understand what I am dong incorrectly with the profile ?

    select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/     MSG.MSG_ID,    MSG.VSBL_MSG_ID,     MSG.SRCH_4_FLD_VAL,     MSG.SRCH_3_FLD_VAL, MSG.SRCH_5_FLD_VAL,     MSG.MSG_TRSM_DTTM,     MSG.DISP_4_FLD_VAL,MSG.DISP_3_FLD_VAL,     MSG.DISP_1_FLD_VAL,     MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL,     TRK.RESEND_DT,     MSG.CRE_DTTM FROMFNM.FNM_VSBL_MSG MSG,     FNM.BCS_INV_RESEND_TRK TRK WHEREMSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD ='PRE_BCS' AND MSG.SRCH_4_FLD_VAL = '123456'   AND (('INVOICENUMBER' ='INVOICENUMBER' AND MSG.MSG_ID IN (SELECT     * FROMTABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR)))) OR('INVOICENUMBER' = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT * FROM     TABLE(CAST(FNM_GN_IN_STRING_LIST('') ASTABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND(TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECTMAX(TRK1.RESEND_DT) FROM     FNM.BCS_INV_RESEND_TRK TRK1 WHERETRK1.INV_NUM =Plan hash value: 1944127456-----------------------------------------------------------------------------------------------------------------| Id  | Operation                               | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                        |                       |       |       |    46 (100)|          ||   1 |  SORT ORDER BY                          |                       |     1 |  2158 |    46   (5)| 00:00:01 ||*  2 |   FILTER                                |                       |       |       |            |          ||   3 |    NESTED LOOPS OUTER                   |                       |     1 |  2158 |    45   (3)| 00:00:01 ||   4 |     NESTED LOOPS                        |                       |     1 |  2141 |    44   (3)| 00:00:01 ||   5 |      VIEW                               | VW_NSO_1              |     1 |  2002 |    36   (0)| 00:00:01 ||   6 |       HASH UNIQUE                       |                       |     1 |     2 |            |          ||   7 |        COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |     1 |     2 |    36   (0)| 00:00:01 ||*  8 |      TABLE ACCESS BY INDEX ROWID        | FNM_VSBL_MSG          |     1 |   139 |     7   (0)| 00:00:01 ||*  9 |       INDEX RANGE SCAN                  | XIE2FNM_VSBL_MSG      |     4 |       |     3   (0)| 00:00:01 ||* 10 |     INDEX RANGE SCAN                    | XPKBCS_INV_RESEND_TRK |     1 |    17 |     1   (0)| 00:00:01 ||  11 |    SORT AGGREGATE                       |                       |     1 |    17 |            |          ||  12 |     FIRST ROW                           |                       |     1 |    17 |     2   (0)| 00:00:01 ||* 13 |      INDEX RANGE SCAN (MIN/MAX)         | XPKBCS_INV_RESEND_TRK |     1 |    17 |     2   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------------------------Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')      DB_VERSION('11.2.0.4')      ALL_ROWS      FORCE_XML_QUERY_REWRITE      XML_DML_RWT_STMT      XMLINDEX_REWRITE      XMLINDEX_REWRITE_IN_SELECT      NO_COST_XML_QUERY_REWRITE      OUTLINE_LEAF(@SEL$7)      OUTLINE_LEAF(@SEL$A8541665)      OUTLINE_LEAF(@SEL$B7274CD5)      UNNEST(@SEL$07BDC5B4 UNNEST_SEMIJ_VIEW)      OUTLINE(@SEL$07BDC5B4)      MERGE(@SEL$4)      OUTLINE(@SEL$F5BB74E1)      MERGE(@SEL$2)      OUTLINE(@SEL$3)      OUTLINE(@SEL$4)      OUTLINE(@SEL$1)      OUTLINE(@SEL$2)      NO_ACCESS(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5)      INDEX_RS_ASC(@SEL$B7274CD5 "MSG"@SEL$2 ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM"              "FNM_VSBL_MSG"."MSG_TYP_CD"))      INDEX(@SEL$B7274CD5 "TRK"@SEL$2 ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))      LEADING(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5 "MSG"@SEL$2 "TRK"@SEL$2)      USE_NL(@SEL$B7274CD5 "MSG"@SEL$2)      USE_NL(@SEL$B7274CD5 "TRK"@SEL$2)      SEMI_TO_INNER(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5)      FULL(@SEL$A8541665 "KOKBF$0"@SEL$4)      USE_HASH_AGGREGATION(@SEL$A8541665)      INDEX(@SEL$7 "TRK1"@SEL$7 ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))      END_OUTLINE_DATA  */Predicate Information (identified by operation id):---------------------------------------------------   2 - filter(("TRK"."RESEND_DT" IS NULL OR "TRK"."RESEND_DT"=))   8 - filter(("MSG"."SRCH_4_FLD_VAL"='123456' AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))   9 - access("MSG"."MSG_ID"="COLUMN_VALUE" AND "MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')       filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')  10 - access("MSG"."MSG_ID"="TRK"."INV_NUM")  13 - access("TRK1"."INV_NUM"=:B1)Note-----   - cardinality feedback used for this statement94 rows selected.

    Profile creation -

    DECLARESQL_FTEXT CLOB;BEGINSELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = '5w8mvk838bas2';DBMS_SQLTUNE.IMPORT_SQL_PROFILE(  SQL_TEXT => SQL_FTEXT,  PROFILE => SQLPROF_ATTR(      'IGNORE_OPTIM_EMBEDDED_HINTS',      'OPTIMIZER_FEATURES_ENABLE(''11.2.0.4'')',      'DB_VERSION(''11.2.0.4'')',      'ALL_ROWS',      'FORCE_XML_QUERY_REWRITE',      'XML_DML_RWT_STMT',      'XMLINDEX_REWRITE',      'XMLINDEX_REWRITE_IN_SELECT',      'NO_COST_XML_QUERY_REWRITE',      'OUTLINE_LEAF(@SEL$7)',      'OUTLINE_LEAF(@SEL$A8541665)',      'OUTLINE_LEAF(@SEL$B7274CD5)',      'UNNEST(@SEL$07BDC5B4 UNNEST_SEMIJ_VIEW)',      'OUTLINE(@SEL$07BDC5B4)',      'MERGE(@SEL$4)',      'OUTLINE(@SEL$F5BB74E1)',      'MERGE(@SEL$2)',      'OUTLINE(@SEL$3)',      'OUTLINE(@SEL$4)',      'OUTLINE(@SEL$1)',      'OUTLINE(@SEL$2)',      'NO_ACCESS(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5)',      'INDEX_RS_ASC(@SEL$B7274CD5 "MSG"@SEL$2 ("FNM_VSBL_MSG"."MSG_ID" "FNM_VSBL_MSG"."CRE_DTTM" "FNM_VSBL_MSG"."MSG_TYP_CD"))',      'INDEX(@SEL$B7274CD5 "TRK"@SEL$2 ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))',      'LEADING(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5 "MSG"@SEL$2 "TRK"@SEL$2)',      'USE_NL(@SEL$B7274CD5 "MSG"@SEL$2)',      'USE_NL(@SEL$B7274CD5 "TRK"@SEL$2)',      'SEMI_TO_INNER(@SEL$B7274CD5 "VW_NSO_1"@SEL$B7274CD5)',      'FULL(@SEL$A8541665 "KOKBF$0"@SEL$4)',      'USE_HASH_AGGREGATION(@SEL$A8541665)',      'INDEX(@SEL$7 "TRK1"@SEL$7 ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))'),       NAME => 'coe_2fcyrt9373kyr_1944127456',  REPLACE => TRUE,  FORCE_MATCH => TRUE);END;/
  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 4, 2019 1:31PM

    Dom,

    Thank you for the inputs.  I may have created profile incorrectly the first time as I did not provide all hints while creating profile.

    Second time, I did create with all hints. (attached the syntax...). I will try the hints you suggested, but regardless, trying to find the flawless way of creating the profile.. or not sure if this profile approach even works in all situations..

  • Dom Brooks
    Dom Brooks Member Posts: 5,552 Silver Crown
    edited Jun 5, 2019 4:46AM

    So, you are getting the text of sql id 5w8mvk838bas2  but creating a sql profile with the naming pattern of sql id 2fcyrt9373kyr?

    Seems weird from a naming perspective.

    If you want to apply the profile to sql 2fcyrt9373kyr then you need to get the text of that sql id.

    Reason is that the text is used to get a hash - the signature you see in dba_sql_profiles - hash exposed via DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE.

    If it's not working then you need to have a close look at that signature in DBA_SQL_PROFILES vs the EXACT_MATCHING_SIGNATURE/FORCE_MATCHING_SIGNATURE of the sql in V$SQL.

    From what you've posted above, your SQL is using literals so every statement with a different values will be a different sql id.

    So, you'd need to use FORCE - as you have done - so check the FORCE_MATCHING_SIGNATURE.

    Note that FORCE will not work with a mixture of binds and literals... but the literal you see in the execution plan predicates is related to subquery optimisation not to value supplied in the SQL statements (at least not one I could see in the actual SQL statements you've posted so far).

    The other challenge is that using a TABLE operator and a TYPE suggests that you can have a variable length of inputs.

    In which case, FORCE matching is no help unless you profile every unique combination of lengths.

    If this is the case, you have a fundamental issue which sticking plasters will struggle to cope with.

    Ideally, you want a single sql statement (and sql id) for every combination of values (provided that can perform acceptably for all combinations of course) - that has to involve bind variables to give you any degree of control and from a variable length list perspective - it's not really going to work brilliantly unless you use a different approach like a global temporary table or bind in a single collection of values.

    All that aside, as an initial experiment- rather than final solution - you could try setting a profile with just this hint:

    opt_estimate(@SEL$A8541665 table "KOKBF$0"@SEL$4 rows=1 )

    But from what you've said already, I suspect the issue is to do with what I've mentioned above.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,792 Gold Crown
    edited Jun 5, 2019 6:38AM
    The business folks are choosing the Invoice # (literal values) from drop down and submitting in the UI.  This means query is using bind variables, correct ?   (Also, I can see bind variables in the SQL execution..)

    There's no way to determine from the UI behaviour whether or not the generated query will be using bind variables. If you've managed to see that "the" query run by the users is picking up the profile then you should be able to show use the sql_fulltext of the query.  The example you've shown has two components that look as if the invoice number is appearing as a literal (and also suggesting that the predicate could change to an IN list.

    MSG.SRCH_4_FLD_VAL = '123456'

    TABLE(CAST(FNM_GN_IN_STRING_LIST('123456') AS TABLE_OF_VARCHAR))

    The ":B1" bind  variable we can see in the execution plan looks like it's a correlating value for the "select max()" subquery passing in the value from a column - but it's possible it's a real, declared bind variable with a name that happens to match a name that Oracle tends to use internally.

    So -

    Can you pull a query, with sql_id, and full text from memory, with it's execution plan, with outline and notes so that we can see a query that you think is using the profile but failing to obey it.  (People have reported that problem before occasionally, I'd have to do a search to find out why it was happening.)

    You could also query dba_sql_profiles for the profile_name you've created with suitable SQL*Plus format commands to show the sql text that Oracle thinks it belongs to, and if you have access to the data dictionary object sqlobj$data you can run the following query for the signature that showed up in dba_sql_profiles to see what the hints that arrived look like.

    set pagesize 60

    set linesize 132

    set trimspool on

    column hint format a70 wrap word

    column signature format 999,999,999,999,999,999,999

    break on signature skip 1 on opt_type skip 1 on plan_id skip 1

    spool sql_profile_baseline_11g

    select

            prf.signature,

            decode(

                    obj_type,

                    1,'Profile',

                    2,'Baseline',

                    3,'Patch',

                    'Other'

            )       opt_type,

            prf.plan_id,

            extractvalue(value(tab),'.')    hint

    from

            (

            select

                    /*+ no_eliminate_oby */

                    *

            from

                    sqlobj$data

            where

                    comp_data is not null

            order by

                    signature, obj_type, plan_id

            )       prf,

            table(

                    xmlsequence(

                            extract(xmltype(prf.comp_data),'/outline_data/hint')

                    )

            )       tab

    ;

    UPDATE:  I wrote the above for 11g, but I think it should still work for 12c.

    Regards

    Jonathan Lewis

  • Donatello Settembrino
    Donatello Settembrino Member Posts: 121 Blue Ribbon
    edited Jun 5, 2019 7:28AM

    Hello,

    I'd like to understand why the optimizer use index XIE11FNM_VSBL_MSG instead of the XIE2FNM_VSBL_MSG which would avoid reading 101K rows on the table FNM_VSBL_MSG.

    It seems clear to me from the runtime statistics of the two execution plans that the selectivity of the index XIE2FNM_VSBL_MSG in the conditions/filters of your query is clearly more advantageous, then I wonder, why accessing in RANGE SCAN the optimizer decides to use XIE11FNM_VSBL_MSG?


    When the optimizer has to choose how to access the table FNM_VSBL_MSG it finds the cost of the index XIE11FNM_VSBL_MSG more convenient than that of the index XIE2FNM_VSBL_MSG and probably the optimizer does not choose XIE2FNM_VSBL_MSG because for some statistical value of this index the cost increased (eg clustering factor).
    Have you tried to see what the statistics of the two indexes are? have been calculated on all objects (table FNM_VSBL_MSG and indexes)?

    Regards,

    DS

  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 5, 2019 3:42PM

    I want to apply profile to bad SQL ID - "5w8mvk838bas2"  generated from good SQL - "2fcyrt9373kyr".

    2fc* is the SQL ID of the SQL from my SQL PLUS run with the index hint.  Then using the outline section of the execution plan, I am trying to copy hints to bad sql id using the link in my first thread..

    Query is using bind variables. I was with the business user this morning while she submitted different invoice numbers, every time the SQL is running with same SQL ID - 5w8*.

    Here's an example I pulled from AWR report that shows Query has bind variables:

    (Note* - Currently, the profile I copied from good sql is dropped and I applied a profile recommended by Tuning Advisor.. which is using a different index.. XIE1*.. at-least this way query is running long but not timing out...)

    SQL_ID 5w8mvk838bas2--------------------SELECT * FROM (SELECT      MSG.MSG_ID,      MSG.VSBL_MSG_ID,MSG.SRCH_4_FLD_VAL,      MSG.SRCH_3_FLD_VAL,      MSG.SRCH_5_FLD_VAL,   MSG.MSG_TRSM_DTTM,      MSG.DISP_4_FLD_VAL,      MSG.DISP_3_FLD_VAL,     MSG.DISP_1_FLD_VAL,      MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL,      TRK.RESEND_DT,      MSG.CRE_DTTM  FROMFNM.FNM_VSBL_MSG MSG,      FNM.BCS_INV_RESEND_TRK TRK  WHEREMSG.MSG_TYP_CD = '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD ='PRE_BCS' AND MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy ='INVOICENUMBER' AND MSG.MSG_ID IN (SELECT      *  FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) ASTABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' ANDMSG.SRCH_3_FLD_VAL IN (SELECT      *  FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) ASTABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND(TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECTMAX(TRK1.RESEND_DT)  FROM      FNM.BCS_INV_RESEND_TRK TRK1  WHERETRK1.INV_NUM = TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESCPlan hash value: 1071232985-------------------------------------------------------------------------------------------------------------| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                    |                       |       |       |     4 (100)|          ||   1 |  SORT ORDER BY                      |                       |     1 |   156 |     4  (25)| 00:00:01 ||   2 |   FILTER                            |                       |       |       |            |          ||   3 |    NESTED LOOPS OUTER               |                       |     1 |   156 |     3   (0)| 00:00:01 ||   4 |     TABLE ACCESS BY INDEX ROWID     | FNM_VSBL_MSG          |     1 |   139 |     2   (0)| 00:00:01 ||   5 |      INDEX RANGE SCAN               | XIE11FNM_VSBL_MSG     |     1 |       |     1   (0)| 00:00:01 ||   6 |     INDEX RANGE SCAN                | XPKBCS_INV_RESEND_TRK |     1 |    17 |     1   (0)| 00:00:01 ||   7 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |     1 |     2 |     3   (0)| 00:00:01 ||   8 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |     1 |     2 |     3   (0)| 00:00:01 ||   9 |    SORT AGGREGATE                   |                       |     1 |    17 |            |          ||  10 |     FIRST ROW                       |                       |     1 |    17 |     2   (0)| 00:00:01 ||  11 |      INDEX RANGE SCAN (MIN/MAX)     | XPKBCS_INV_RESEND_TRK |     1 |    17 |     2   (0)| 00:00:01 |-------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):-------------------------------------------------------------   1 - SEL$F5BB74E1   4 - SEL$F5BB74E1 / [email protected]$2   5 - SEL$F5BB74E1 / [email protected]$2   6 - SEL$F5BB74E1 / [email protected]$2   7 - SEL$07BDC5B4 / [email protected]$4   8 - SEL$ABDE6DFF / [email protected]$6   9 - SEL$7  11 - SEL$7        / [email protected]$7Outline Data-------------  /*+      BEGIN_OUTLINE_DATA      IGNORE_OPTIM_EMBEDDED_HINTS      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')      DB_VERSION('11.2.0.4')      ALL_ROWS      FORCE_XML_QUERY_REWRITE      XML_DML_RWT_STMT      XMLINDEX_REWRITE      XMLINDEX_REWRITE_IN_SELECT      NO_COST_XML_QUERY_REWRITE      OUTLINE_LEAF(@SEL$07BDC5B4)      MERGE(@SEL$4)      OUTLINE_LEAF(@SEL$ABDE6DFF)      MERGE(@SEL$6)      OUTLINE_LEAF(@SEL$7)      OUTLINE_LEAF(@SEL$F5BB74E1)      MERGE(@SEL$2)      OUTLINE(@SEL$3)      OUTLINE(@SEL$4)      OUTLINE(@SEL$5)      OUTLINE(@SEL$6)      OUTLINE(@SEL$1)      OUTLINE(@SEL$2)      INDEX_RS_ASC(@SEL$F5BB74E1 "MSG"@SEL$2 ("FNM_VSBL_MSG"."SRCH_4_FLD_VAL"              "FNM_VSBL_MSG"."MSG_TYP_CD" "FNM_VSBL_MSG"."MSG_CAPTR_STG_CD"))      INDEX(@SEL$F5BB74E1 "TRK"@SEL$2 ("BCS_INV_RESEND_TRK"."INV_NUM"              "BCS_INV_RESEND_TRK"."RESEND_DT"))      LEADING(@SEL$F5BB74E1 "MSG"@SEL$2 "TRK"@SEL$2)      USE_NL(@SEL$F5BB74E1 "TRK"@SEL$2)      INDEX(@SEL$7 "TRK1"@SEL$7 ("BCS_INV_RESEND_TRK"."INV_NUM" "BCS_INV_RESEND_TRK"."RESEND_DT"))      FULL(@SEL$ABDE6DFF "KOKBF$1"@SEL$6)      FULL(@SEL$07BDC5B4 "KOKBF$0"@SEL$4)      END_OUTLINE_DATA  */Peeked Binds (identified by position):--------------------------------------   1 - :BINDINVOICETO (VARCHAR2(30), CSID=873): '123456'

    Enter value for SQLID: 5w8mvk838bas2old  31: where sql_id='&sql_id'new  31: where sql_id='5w8mvk838bas2'                                                                             elapsd                                                                  rows         time                 elapsed in                plan_hash   snap                    exec  processed        delta                     per SQL ID SQL_ID             value     id SNAP_BEG          delta      delta          sec     CPU_EX    execution Profile--- ------------- ---------- ------ --------------- ------- ---------- ------------ ---------- ------------ --------------------  1 5w8mvk838bas2 1071232985  10096 29-05-19 14:00        4          0       242.02  1.3345235        60.51 null  1 5w8mvk838bas2 3990772682  10116 30-05-19 10:00        2          0       120.42     .74443        60.21 null  1 5w8mvk838bas2 1071232985  10117 30-05-19 11:00        1          0       120.21   2.405869       120.21 null  1 5w8mvk838bas2 3990772682  10117 30-05-19 11:00        0          0          .00 null       null         null  1 5w8mvk838bas2 1071232985  10118 30-05-19 12:00        4          2       121.47    .713648        30.37 null  1 5w8mvk838bas2 3990772682  10118 30-05-19 12:00        0          0          .00 null       null         null  1 5w8mvk838bas2 1071232985  10119 30-05-19 13:00        3          4         1.94 .011043667          .65 null  1 5w8mvk838bas2 1071232985  10120 30-05-19 14:00        8          0       120.73 .391422625        15.09 null  1 5w8mvk838bas2 1071232985  10121 30-05-19 15:00        5          0       242.15   .5827904        48.43 null  1 5w8mvk838bas2 1071232985  10141 31-05-19 11:01        2          0       120.98  1.4070945        60.49 null  1 5w8mvk838bas2 1071232985  10142 31-05-19 12:00        4          0       242.48   1.477149        60.62 null  1 5w8mvk838bas2 3990772682  10142 31-05-19 12:00        2          0       120.63  1.2266175        60.32 coe_5w8mvk838bas2_39  1 5w8mvk838bas2 4200011164  10144 31-05-19 14:00       15          6       315.43   .8964386        21.03 SYS_SQLPROF_016b0f62  1 5w8mvk838bas2 3990772682  10145 31-05-19 15:00        2          0       240.73  2.3402655       120.37 null  1 5w8mvk838bas2 3462797742  10146 31-05-19 16:00        2          0       120.20  1.7872955        60.10 PROFILE_5w8mvk838bas  1 5w8mvk838bas2 1071232985  10149 31-05-19 19:00        4          0        85.26    .349116        21.31 null  1 5w8mvk838bas2 1071232985  10214 03-06-19 12:00        2          0        67.82    .651554        33.91 coe_2fcyrt9373kyr_19  1 5w8mvk838bas2 1071232985  10215 03-06-19 13:00        5          0       121.48   .6201476        24.30 coe_2fcyrt9373kyr_19  1 5w8mvk838bas2 1071232985  10236 04-06-19 10:00        3          0       120.81 .570824333        40.27 null  1 5w8mvk838bas2 1071232985  10237 04-06-19 11:00        3          0       241.92 1.42221667        80.64 coe_2fcyrt9373kyr_19  1 5w8mvk838bas2 4200011164  10242 04-06-19 16:00        2          2       105.11  2.1878195        52.56 SYS_SQLPROF_016b23ae  1 5w8mvk838bas2 4200011164  10260 05-06-19 10:00        3          1       122.01 1.08735633        40.67 SYS_SQLPROF_016b23ae
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,792 Gold Crown
    edited Jun 6, 2019 4:23AM

    The text that is actually executed may explain why the profile has to be ignored.

    If you supply an actual value when testing code that runs with literals you may produce a plan that is dependent on the optimizer "KNOWING" that it is doing a comparison with something that is not null. When the query runs with bind variables the optimizer has to produce a plan which caters for the possibility that the bind variable may hold NULL - this means you may end up with a faked profile that dictates a plan that would give the wrong results if an incoming bind variable were NULL.  If that happens you can be in a position where Oracle reports "I applied the profile - but then I ignored it because it would produce an illegal plan", unfortunately there's no code to produce the second half of the message.

    For testing purposes (from SQL*Plus, for example) you have to declare variables and assign values to them, then use the variables in the SQL, e.g.

    SQL> variable BindInvoiceTo varchar2(10)

    SQL> exec :BindInvoiceTo := '12345'

    select ...  where MSG.SRCH_4_FLD_VAL = :BindInvoiceTo

    Regards

    Jonathan Lewis

    Regards

    Jonathan Lewis

    Mohamed HouriDBA112