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