Forum Stats

  • 3,839,356 Users
  • 2,262,486 Discussions
  • 7,900,947 Comments

Discussions

How to enforce index hint to a SQL !

2

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 6, 2019 4:46AM

    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

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,259 Bronze Trophy
    edited Jun 6, 2019 5:54AM

    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

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 6, 2019 5:59AM

    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

  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 6, 2019 5:12PM

    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_DTTMFROMFNM.FNM_VSBL_MSG MSG,FNM.BCS_INV_RESEND_TRK TRKWHEREMSG.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) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT*FROMTABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND (TRK.RESEND_DT IS NULL ORTRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)FROM    FNM.BCS_INV_RESEND_TRK TRK1WHERE    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 !

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 7, 2019 9:37AM
    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 ?

    Sometimes a test which uses literals that match the end-user's inputs will be sufficient - but you have to be aware that plans may different between bind variable versions and literal value versions - so doing a test that is as close as possible to the user's activity is desirable. In this case the approach you've taken is adequate, but there may be some cases where you have to wrap the SQL inside a PL/SQL block and use PL/SQL variables to pass in the values as SQL*Plus variables are all considered to be character types, so there's a risk of implicit conversion doing the wrong thing and changing plans (though you could edit the SQL to use to_number(), to_date() etc. on the SQL*Plus variables - but even that may mean Oracle doesn't have exactly the same information for the arithmetic.

    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 ?

    You already know how to start - do what you did for the original posting: execute the hinted query with row source statistics enabled and pull the plan from memory after it has executed, reporting the execution statistics.  At present you haven't even told use whether or not Oracle used the index you hinted, so how can we tell you what to do next.  (Include the 'alias' formatting option so we get a little more information about what Oracle is doing with the query.

    Regards

    Jonathan Lewis

  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 11, 2019 1:37PM

    Jon,  Sorry for the delayed response.

    Here's query execution stats from cursor cache.  The optimizer is picking up the index hint, but  it is doing a "index full scan" (instead of range scan).  Still not returning results after 30 min.

    Next, I enforced index range scan hint "INDEX_RS_ASC (MSG XIE2SNI_VSBL_MSG)*/ ", now optimizer doesn't even pick this index.

    How do I check for data distribution or see if collecting histograms is of any use in this scenario ?  Apparently,  the best run-time we got so far is through a Tuning advisor profile which is using a different index..

    SQL> alter session set statistics_level='ALL';Session altered.SQL> variable BINDINVOICETO VARCHAR2(32);SQL> variable BINDSEARCHBY VARCHAR2(128);SQL> variable BINDINVOICELIST VARCHAR2(32);SQL> variable BINDSEARCHBY VARCHAR2(128);SQL> variable BINDSEIBELIDLIST VARCHAR2(32);SQL>SQL> begin  2  3  :BINDINVOICETO := '197639';  4  :BINDSEARCHBY := 'INVOICENUMBER';  5  :BINDINVOICELIST := '34609590';  6  :BINDSEARCHBY := 'INVOICENUMBER';  7  :BINDSEIBELIDLIST := '';  8  9  end; 10 11  /PL/SQL procedure successfully completed.SQL> select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/  2  MSG.MSG_ID,  3  MSG.VSBL_MSG_ID,  4  MSG.SRCH_4_FLD_VAL,  5  MSG.SRCH_3_FLD_VAL,  6  MSG.SRCH_5_FLD_VAL,  7  MSG.MSG_TRSM_DTTM,  8  MSG.DISP_4_FLD_VAL,  9  MSG.DISP_3_FLD_VAL, 10  MSG.DISP_1_FLD_VAL, 11  MSG.DISP_2_FLD_VAL,MSG.SRCH_1_FLD_VAL, 12   13  TRK.RESEND_DT, 14  MSG.CRE_DTTM 15  FROM 16  FNM.FNM_VSBL_MSG MSG, 17  FNM.BCS_INV_RESEND_TRK TRK 18  WHERE 19  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 20  * 21  FROM 22  TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND MSG.SRCH_3_FLD_VAL IN (SELECT 23  * 24  FROMTABLE(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 25   26  TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT) 27  FROM 28      FNM.BCS_INV_RESEND_TRK TRK1 29  WHERE 30      TRK1.INV_NUM = TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESC;

    Enter value for sql_id: 7pc3shgs6gy29old   3: where s.sql_id='&sql_id'new   3: where s.sql_id='7pc3shgs6gy29'EXECUTION_PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------0 SQL_ID  7pc3shgs6gy29, child number 00 -------------------------------------0 select * from (SELECT /*+ INDEX(MSG XIE2FNM_VSBL_MSG)*/ MSG.MSG_ID,0 MSG.VSBL_MSG_ID, MSG.SRCH_4_FLD_VAL, MSG.SRCH_3_FLD_VAL,0 MSG.SRCH_5_FLD_VAL, MSG.MSG_TRSM_DTTM, MSG.DISP_4_FLD_VAL,0 MSG.DISP_3_FLD_VAL, MSG.DISP_1_FLD_VAL, MSG.DISP_2_FLD_VAL,0 MSG.SRCH_1_FLD_VAL, TRK.RESEND_DT, MSG.CRE_DTTM FROM FNM.FNM_VSBL_MSG0 MSG, FNM.BCS_INV_RESEND_TRK TRK WHERE MSG.MSG_TYP_CD =0 '210_CUSTOMER_INVOICE' AND MSG.MSG_CAPTR_STG_CD = 'PRE_BCS' AND0 MSG.SRCH_4_FLD_VAL = :BindInvoiceTo AND ((:BindSearchBy =0 'INVOICENUMBER' AND MSG.MSG_ID IN (SELECT * FROM0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindInvoiceList) AS0 TABLE_OF_VARCHAR)))) OR (:BindSearchBy = 'SIEBELORDERID' AND0 MSG.SRCH_3_FLD_VAL IN (SELECT * FROM0 TABLE(CAST(FNM_GN_IN_STRING_LIST(:BindSeibelIDList) AS0 TABLE_OF_VARCHAR))))) AND MSG.MSG_ID = TRK.INV_NUM(+) AND0 (TRK.RESEND_DT IS NULL OR TRK.RESEND_DT = (SELECT MAX(TRK1.RESEND_DT)0 FROM     FNM.BCS_INV_RESEND_TRK TRK1 WHERE     TRK1.INV_NUM =0 TRK.INV_NUM))) QRSLT  ORDER BY CRE_DTTM DESC00 Plan hash value: 158745648600 ---------------------------------------------------------------------------------------------------------0 | Id  | Operation                           | Name                  | E-Rows |  OMem |  1Mem | Used-Mem |0 ---------------------------------------------------------------------------------------------------------0 |   0 | SELECT STATEMENT                    |                       |        |       |       |          |0 |   1 |  SORT ORDER BY                      |                       |      1 | 73728 | 73728 |          |0 |*  2 |   FILTER                            |                       |        |       |       |          |0 |   3 |    NESTED LOOPS OUTER               |                       |      1 |       |       |          |0 |*  4 |     TABLE ACCESS BY INDEX ROWID     | FNM_VSBL_MSG          |      1 |       |       |          |0 |*  5 |      INDEX FULL SCAN                | XIE2FNM_VSBL_MSG      |   4975K|       |       |          |0 |*  6 |     INDEX RANGE SCAN                | XPKBCS_INV_RESEND_TRK |      1 |       |       |          |0 |*  7 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |       |       |          |0 |*  8 |    COLLECTION ITERATOR PICKLER FETCH| FNM_GN_IN_STRING_LIST |      1 |       |       |          |0 |   9 |    SORT AGGREGATE                   |                       |      1 |       |       |          |0 |  10 |     FIRST ROW                       |                       |      1 |       |       |          |0 |* 11 |      INDEX RANGE SCAN (MIN/MAX)     | XPKBCS_INV_RESEND_TRK |      1 |       |       |          |0 ---------------------------------------------------------------------------------------------------------00 Predicate Information (identified by operation id):0 ---------------------------------------------------00    2 - filter((((:BINDSEARCHBY='INVOICENUMBER' AND  IS NOT NULL) OR0               (:BINDSEARCHBY='SIEBELORDERID' AND  IS NOT NULL)) AND ("TRK"."RESEND_DT" IS NULL OR0               "TRK"."RESEND_DT"=)))0    4 - filter(("MSG"."SRCH_4_FLD_VAL"=:BINDINVOICETO AND "MSG"."MSG_CAPTR_STG_CD"='PRE_BCS'))0    5 - access("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')0        filter("MSG"."MSG_TYP_CD"='210_CUSTOMER_INVOICE')0    6 - access("MSG"."MSG_ID"="TRK"."INV_NUM")0    7 - filter(VALUE(KOKBF$)=:B1)0    8 - filter(VALUE(KOKBF$)=:B1)0   11 - access("TRK1"."INV_NUM"=:B1)00 Note0 -----0    - Warning: basic plan statistics not available. These are only collected when:0        * hint 'gather_plan_statistics' is used for the statement or0        * parameter 'statistics_level' is set to 'ALL', at session or system level059 rows selected.
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 11, 2019 1:59PM

    The first step would be to get my name right.

    Then take a close look at the current plan which does the INDEX FULL SCAN, and compare it with the "good" plan that you started this thread with. Ask yourself this question - where is Oracle coming FROM when it can make good use of that index, and is it coming from the same place when you hint it now that you've got the bind variables in the code.

    Regards

    Jonathan Lewis

  • DBA112
    DBA112 Member Posts: 517 Bronze Badge
    edited Jun 11, 2019 2:39PM

    Jonathan,  Sorry about the shorter name I used earlier.   Thank you for all the inputs, I will continue the analysis.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 12, 2019 2:23AM

    The problem is the query - I don't think the optimizer can do anything efficient with it to make it efficient; you may have to rewrite it as a UNION ALL.

    In your original Oracle could detect that one of your IN subqueries could be discarded because you had 'INVOICE' = 'SIEBELORDERID' which is alway false.

    With bind variables Oracle has to check at run time which subquery needs to be run - which means it has to create a single plan with two branches (hence the "union all" requirement) so that it can  pick the right one.  This means with the current code you HAVE to drive from the FNM_VSBL_MSG table and run one of two possible filter subqueries.

    I may be able to find time to give you a sample solution later on today.

    Regards

    Jonathan Lewis

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,010 Blue Diamond
    edited Jun 12, 2019 4:16AM

    I'm stuck on a train for a couple more hours, so I've prepared a model (which I'll probably blog about) as a demo - here's something that looks like a simplified version of your query (the fact that I don't use collections is irrelevant):

    select

            t1.v1

    from

            t1

    where

            (

                :v1 = 'INVOICE'

            and t1.id in (select id from t2 where n1 = 0)

            )

    or      (

                :v1 = 'ORDERID'

            and t1.id in (select id from t3 where n1 = 0)

            )

    ;

    ---------------------------------------------------------------------------

    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time    |

    ---------------------------------------------------------------------------

    |   0 | SELECT STATEMENT   |      |    10 |   150 |     25  (4)| 00:00:01 |

    |*  1 |  FILTER            |      |       |       |            |          |

    |   2 |   TABLE ACCESS FULL| T1   | 10000 |   146K|     25  (4)| 00:00:01 |

    |*  3 |   TABLE ACCESS FULL| T2   |     1 |     8 |     25  (4)| 00:00:01 |

    |*  4 |   TABLE ACCESS FULL| T3   |     1 |     8 |     25  (4)| 00:00:01 |

    ---------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      1 - filter(:V1='INVOICE' AND  EXISTS (SELECT 0 FROM "T2" "T2" WHERE

                  "ID"=:B1 AND "N1"=0) OR :V1='ORDERID' AND  EXISTS (SELECT 0 FROM "T3"

                  "T3" WHERE "ID"=:B2 AND "N1"=0))

      3 - filter("ID"=:B1 AND "N1"=0)

      4 - filter("ID"=:B1 AND "N1"=0)

    Oracle HAS to use the two IN subqueries as (existence ) filter predicates. But your programmer (and my sample) "know" that the IDs in t2 are about INVOICES and the ids in t3 are about ORDERIDs, and I only ever want to run one of these subqueries. If I make the subquery a UNION ALL (which isn't going to have an overlap) the optimizer can unnest and drive from the suqbuery back into t1 - in your case using the index you want used (even if you have to hint it).

    select

            t1.v1

    from

            t1

    where

            t1.id in (

                    select /*+ unnest */ id

                    from    t2

                    where  n1 = 0

                    and    :v1 = 'INVOICE'

                    union all

                    select /*+ unnest */ id

                    from    t3

                    where  n1 = 0

                    and    :v1 = 'ORDERED'

            )

    ;

    -----------------------------------------------------------------------------------

    | Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

    -----------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT       |          |    54 |  1512 |     74  (3)| 00:00:01 |

    |*  1 |  HASH JOIN             |          |    54 |  1512 |     74  (3)| 00:00:01 |

    |   2 |   VIEW                 | VW_NSO_1 |    54 |   702 |     49  (3)| 00:00:01 |

    |   3 |    HASH UNIQUE         |          |    54 |   432 |     49  (3)| 00:00:01 |

    |   4 |     UNION-ALL          |          |       |       |            |          |

    |*  5 |      FILTER            |          |       |       |            |          |

    |*  6 |       TABLE ACCESS FULL|  T2      |    27 |   216 |     25  (4)| 00:00:01 |

    |*  7 |      FILTER            |          |       |       |            |          |

    |*  8 |       TABLE ACCESS FULL|  T3      |    27 |   216 |     25  (4)| 00:00:01 |

    |   9 |   TABLE ACCESS FULL    |  T1      | 10000 |   146K|     25  (4)| 00:00:01 |

    -----------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      1 - access("T1"."ID"="ID")

      5 - filter(:V1='INVOICE')

      6 - filter("N1"=0)

      7 - filter(:V1='ORDERED')

      8 - filter("N1"=0)

    Note that in my case the union all HAS unnested and is the build table in a hash join - in your case it would (probably) drive a nested loop rather than a hash join, using the desired index into the main table.

    Regards

    Jonathan Lewis