Hi, we are using version 12.1.0.2 of Oracle whereas optimizer_feature_enable is set as 11.2.0.4. It was running like that since years, We are seeing sudden change in plan for one of the sql and its now started running longer. And the interesting part is when i tried forcing the outline of the old plan to this query to see the behavior, i see the cost is coming lesser with the old plan(or good plan) outline, but surprisingly the optimizer somehow still rejecting that execution path and following the more costly execution path by its own.
I have not posted the exact query to reduce the complexity, i have posted the exact part which is mainly affected by this behavior. With index TAB1_IX5 the query is costed less even now, but its not following that path by default and i had to force hint for that which looks as below. The default path its following now is with index scan - TAB1_IX1 which is costed 60Million which is way higher than the 287K.
Then looking into the outline section for the original production plan, i found , some new hint embedded as below for the new sub-optimal plan, i am not sure but mostly this is affecting the optimizer decision to go for the more costliest path even a cheaper is available and its somehow rejecting the cheaper path. Below is the OPT_PARAM hints i found in the outline section which were not there before. So wanted to understand, if this fix control can cause such issues and apart from hinting or adding a profile whats the other better way to fix this issue as this must be impacting others too? or i need to go for 10553 trace to understand the real reason behind optimizer rejection of the less costliest path?
***************** Default plan which runs longer and having larger cost**************
Select * from TAB1 where TAB1.ETYP = 'XX'
and TAB1.DATE_CREATED >= to_date(:b1,'MM/DD/YYYY HH24:MI:SS')
AND TAB1.DATE_CREATED < to_date(:b2,'MM/DD/YYYY HH24:MI:SS')
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4126838465
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1116K| 166M| 60M (1)| 00:39:15 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAB1 | 1116K| 166M| 60M (1)| 00:39:15 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | TAB1_IX1 | 446M| | 2622K (1)| 00:01:43 | | |
-----------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TAB1@SEL$1
3 - SEL$1 / TAB1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "TAB1"@"SEL$1" ("TAB1"."ETYP"
"TAB1"."EID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_fix_control' '26664361:2')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(:B2,'MM/DD/YYYY HH24:MI:SS')>TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))
2 - filter("TAB1"."DATE_CREATED">=TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS') AND
"TAB1"."DATE_CREATED"<TO_DATE(:B2,'MM/DD/YYYY HH24:MI:SS'))
3 - access("TAB1"."ETYP"='XX')
************** Plan with forced index hint****************
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 782021929
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1116K| 166M| 287K (1)| 00:00:12 | | |
|* 1 | FILTER | | | | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TAB1 | 1116K| 166M| 287K (1)| 00:00:12 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | TAB1_IX5 | 4017K| | 36831 (1)| 00:00:02 | | |
------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / TAB1@SEL$1
3 - SEL$1 / TAB1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "TAB1"@"SEL$1" ("TAB1"."DATE_CREATED"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_fix_control' '26664361:2')
OPT_PARAM('_optimizer_gather_feedback' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
redicate Information (identified by operation id):
--------------------------------------------------
1 - filter(TO_DATE(:B2,'MM/DD/YYYY HH24:MI:SS')>TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS'))
2 - filter("TAB1"."ETYP"='XX')
3 - access("TAB1"."DATE_CREATED">=TO_DATE(:B1,'MM/DD/YYYY HH24:MI:SS') AND
"TAB1"."DATE_CREATED"<TO_DATE(:B2,'MM/DD/YYYY HH24:MI:SS'))