Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing

Post Details

Added on Jun 3 2019
22 comments
15,711 views