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!

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

fac586

Richard Legge wrote:

Hi all, this mornings question.. please let it be a nice simple answer...

Im using APEX 4.2, and have switch to theme 15. simple blue.

Ive got a menu list on the left hand side. so it needs to sit in region 2. its designated as a sidebar region.   I'm trying to use the sidebar template so that I get a menu header and a border... However region 2 on this theme has a blue background... and also has a header and footer area / image.

So my sidebar region looks odd inside it.

From my research, I should be able to override the region template to remove this header / footer / background colour and associated attributes, leaving me with a nice sidebar menu... Ive don lots of research, and think I should be referencing the region class in the definition of my menu??? but dont seem to be able to get it right..

Please could somebody help

Somebody would be able to help more quickly and effectively if they had an example on apex.oracle.com to refer to...

However, if you're using 4.2 and not supporting legacy (IE6/7) browsers I would recommend not using theme 15, nor any of the other legacy quirks mode themes (marked with a "*"). You'll find it much easier to work with—and get support for—the more modern standards-based themes.

Richard Legge

Have I missed something then, as Ive only got two standard themes.. 23 (uniframe) and 26 (productivity Apps).. It doesn't give me much to work with... are there others available?

Thanks

Richard

fac586

Richard Legge wrote:

Have I missed something then, as Ive only got two standard themes.. 23 (uniframe) and 26 (productivity Apps).. It doesn't give me much to work with... are there others available?

You must have some of the "Standard" themes already installed in the app.

When you go through the Create Theme wizard, on the Identify Theme page, select All Themes from the Theme Type select list, and use any of them except those marked with a "*": 8, 10, 11, 13 (which is not marked but should be), 14, 15, 16, 18, 19, and 20.

All of the other themes (regardless of their 4.2 labelling as "Standard" or "Legacy") contain a DOCTYPE declaration that will trigger standards mode in browsers.

Richard Legge

ahh.  Thanks. Yes, forgot that if Id already imported them, they dont show up on the list.. many thanks

1 - 4

Post Details

Added on Jun 3 2019
22 comments
19,422 views