1 2 Previous Next 22 Replies Latest reply on Jun 14, 2019 2:23 PM by DBA112

    How to enforce index hint to a SQL !

    DBA112

      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 statement
      
      
      
      
      54 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..

        • 1. Re: How to enforce index hint to a SQL !
          KayK

          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

          • 2. Re: How to enforce index hint to a SQL !
            Jonathan Lewis

            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]

            1 person found this helpful
            • 3. Re: How to enforce index hint to a SQL !
              Dom Brooks

              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.

              • 4. Re: How to enforce index hint to a SQL !
                DBA112

                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 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 =
                
                
                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 statement
                
                
                
                
                94 rows selected.
                

                 

                Profile creation -

                 

                DECLARE
                SQL_FTEXT CLOB;
                BEGIN
                SELECT 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;
                /
                
                • 5. Re: How to enforce index hint to a SQL !
                  DBA112

                  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..

                  • 6. Re: How to enforce index hint to a SQL !
                    Dom Brooks

                    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.

                    • 7. Re: How to enforce index hint to a SQL !
                      Jonathan Lewis

                      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

                      • 8. Re: How to enforce index hint to a SQL !
                        Donatello Settembrino

                        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

                        • 9. Re: How to enforce index hint to a SQL !
                          DBA112

                          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  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 = :BindInvoiceTo AND ((:BindSearchBy =
                          'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT      *  FROM
                          TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS
                          TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND
                          MSG.SRCH_3_FLD_VAL IN (SELECT      *  FROM
                          TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) 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: 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 / MSG@SEL$2
                             5 - SEL$F5BB74E1 / MSG@SEL$2
                             6 - SEL$F5BB74E1 / TRK@SEL$2
                             7 - SEL$07BDC5B4 / KOKBF$0@SEL$4
                             8 - SEL$ABDE6DFF / KOKBF$1@SEL$6
                             9 - SEL$7
                            11 - SEL$7        / TRK1@SEL$7
                          
                          
                          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$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: 5w8mvk838bas2
                          old  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
                          
                          
                          
                          
                          
                          • 10. Re: How to enforce index hint to a SQL !
                            Jonathan Lewis

                            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

                            • 11. Re: How to enforce index hint to a SQL !
                              Jonathan Lewis

                              Compare the plan you generated at the start of this thread with the plan from the live system.

                               

                              Note particularly that the operation "COLLECTION ITERATOR PICKLER FETCH" appears twice in the live plan, but only once in your plan.

                               

                              This is because you've generated a plan with a predicate: "... OR ('INVOICENUMBER' = 'SIEBELORDERID' AND ... ) and the optimizer at PARSE time can see that that bit of the where clause will always be false - so it's been able to drop the subquery referencing the second collection construct.

                               

                              In production the optimizer has to produce a plan that caters for the possibility that the bind variable will hold the value 'SIEBELORDERID' at runtime, and that means your profile is illegal.

                               

                              Regards

                              Jonathan Lewis

                               

                              • 12. Re: How to enforce index hint to a SQL !
                                Mohamed Houri

                                Jonathan

                                 

                                This is exactly what I've got at one client site where the execution plan Note was saying that the SQL Profile has been used but the desired execution plan has not been generated. I spent a couple of hours searching the root cause for that
                                until I spotted out, in the peeked bind variables of the application query execution plan, that one of the bind variables sent from the front-end application was null. The original poster can check whether this is the case for him or not using the peeked bind variable of the bad plan.

                                 

                                This is one of the important difference between a SQL Profile and a SPM. Oracle will say (via the Note) that the SQL Profile has been used whenever the signature of the Profile matches that of the SQL query regardless of the difference that might exist between the plan forced by the Profile

                                and the one that would have normally been produced by applying the set of hints contained into the Profile.

                                 

                                Whereas SPM will never say that the SQL Plan baseline has been used if the plan_id stored into the baseline doesn’t match the phv2 of the CBO execution plan

                                 

                                Best regards

                                Mohamed Houri

                                • 13. Re: How to enforce index hint to a SQL !
                                  Jonathan Lewis

                                  Here's an old blog note of mine that demonstrates the problem of testing a query with bind variables by substituting for a particular set of actual values: https://jonathanlewis.wordpress.com/2010/10/31/conditional-sql-3/  The principle is the same even though it doesn't go into the topic of SQL Profiles.

                                   

                                   

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: How to enforce index hint to a SQL !
                                    DBA112

                                    Jon,

                                     

                                    Thank you for teaching me a new point.  This is getting intriguing and would like to clarify a couple of things:

                                     

                                    1. This means I should "never" supply actual literal values  while testing the code,  when I know the code from application uses bind variables ? because the plan I produce by using literals in the SQL is no good (illegal for the optimizer..) for the SQL coming from the application, correct ?

                                     

                                    So, for this you are suggesting  the right way to test is to declare the bind variables first and run the SQL with binds..

                                     

                                    For instance,  is this the right way to test ?  With this, I am not getting the <1 sec response time anymore even though I am using the Index hint I  "thought" would help.....

                                     

                                     

                                    
                                    variable BINDINVOICETO VARCHAR2(32);
                                    variable BINDSEARCHBY VARCHAR2(128);
                                    variable BINDINVOICELIST VARCHAR2(32);
                                    variable BINDSEARCHBY VARCHAR2(128);
                                    variable BINDSEIBELIDLIST VARCHAR2(32);
                                    
                                    
                                    begin
                                    
                                    
                                    :BINDINVOICETO := '123456';
                                    :BINDSEARCHBY := 'INVOICENUMBER';
                                    :BINDINVOICELIST := '456789';
                                    :BINDSEARCHBY := 'INVOICENUMBER';
                                    :BINDSEIBELIDLIST := '';
                                    
                                    
                                    end;
                                    
                                    
                                    /
                                    
                                    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 = :BindInvoiceTo AND ((:BindSearchBy = 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT
                                    *
                                    FROM
                                    TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT
                                    *
                                    FROM
                                    TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) 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;
                                    
                                    

                                     

                                     

                                    2.    Now that I am testing it the right way (?)   (by declaring the bind variables before SQL execution)  and the index hint is not helping... any suggestions on how else I improve the run-time of this query ?

                                     

                                    Ideally, if the Index hint is still helping when I am testing it the right way, then copying the profile (with outline hints) makes sense ?

                                     

                                    Thank you very much for the guidance and your technical acumen !

                                    1 2 Previous Next