Skip to Main Content

Oracle Database Discussions

How to enforce index hint to a SQL !

DBA112Jun 3 2019 — edited Jun 14 2019

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

Comments
Post Details
Added on Jun 3 2019
22 comments
7,429 views