Forum Stats

  • 3,826,853 Users
  • 2,260,713 Discussions
  • 7,897,102 Comments

Discussions

Categories

Question on Index MIN/MAX optimization

User_OCZ1T
User_OCZ1T Member Posts: 1,923 Gold Badge
edited Jul 10, 2020 9:37AM in General Database Discussions

Hi we are using version 11.2.0.4 of Oracle Exadata. Below query is running for ~7-8 minutes. And the way this query is written here such that , table CNTRL_TAB is a just a control table so there is no join between the main table- TAB1 and this control table. And TAB1 is list partitioned on column DATE_KEY. And index TAB1_PK is composite index on (FCT_KEY,DATE_KEY).

And i saw the execution path , its going for a "INDEX STORAGE FAST FULL SCAN" path by default. but i see we are only picking up the MAX value of a column which is leading column of an unique index.  Now when i tried putting hint INDEX(TAB1 TAB1_PK), it went for "INDEX FULL SCAN (MIN/MAX)" and finished in seconds. So wanted to understand why by default optimizer is not following the MIN/MAX optimization? From the plan it shows the expected costing of "INDEX FULL SCAN (MIN/MAX)" coming as ~27million as compared to the cost of "INDEX STORAGE FAST FULL SCAN" in the default path which is ~7million for same ~8billion rows estimated in both of the cases. But in case of INDEX FULL SCAN (MIN/MAX) the number of actual rows coming as ~496.

Another way i tried tweaking the query, using exists operator then i see its following MIN/MAX optimization an finishing i seconds. Why its not following that path by default?

Also the surprising part is even by default its using the same index but to be exact its doing "INDEX STORAGE FAST FULL SCAN". So how-come if i hint the query using same index(means i have not supplied any additional input/influencer to the query) its opting "INDEX FULL SCAN (MIN/MAX)"?

Default execution path:-

SQL Text------------------------------SELECT MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1,CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULLGlobal Information------------------------------ Status              :  DONE (ALL ROWS)            Instance ID         :  3                          SQL ID              :  2y1x33t9vf9xx              SQL Execution ID    :  50331648                   Execution Started   :  07/09/2020 09:08:48        First Refresh Time  :  07/09/2020 09:08:52        Last Refresh Time   :  07/09/2020 09:16:38        Duration            :  470s                       Module/Action       :  SQL*Plus/-                 Program             :  sqlplus.exe                Fetch Calls         :  1                         Global Stats==============================================================================================================| Elapsed |   Cpu   |    IO    | Application | Cluster  |  Other   | Fetch | Buffer | Read | Read  |  Cell   || Time(s) | Time(s) | Waits(s) |  Waits(s)   | Waits(s) | Waits(s) | Calls |  Gets  | Reqs | Bytes | Offload |==============================================================================================================|     470 |     446 |       15 |        0.34 |     0.10 |       10 |     1 |    32M | 514K | 245GB |  27.54% |==============================================================================================================SQL Plan Monitoring Details (Plan Hash Value=3656453152)=================================================================================================================================================================================================================| Id |             Operation             |           Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  |  Cell   |  Mem  | Activity |           Activity Detail           ||    |                                   |                           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes | Offload | (Max) |   (%)    |             (# samples)             |=================================================================================================================================================================================================================|  0 | SELECT STATEMENT                  |                           |         |      |       467 |     +4 |     1 |        1 |      |       |         |       |          |                                     ||  1 |   SORT AGGREGATE                  |                           |       1 |      |       468 |     +3 |     1 |        1 |      |       |         |       |    40.21 | Cpu (189)                           ||  2 |    NESTED LOOPS                   |                           |      8G |   7M |       407 |     +4 |     1 |       4G |      |       |         |       |          |                                     ||  3 |     TABLE ACCESS BY INDEX ROWID   | CNTRL_TAB                 |       1 |    1 |         1 |     +4 |     1 |        1 |      |       |         |       |          |                                     ||  4 |      INDEX UNIQUE SCAN            | CONTROLTAB_PK             |       1 |      |       467 |     +4 |     1 |        1 |      |       |         |       |          |                                     ||  5 |     PARTITION LIST ALL            |                           |      8G |   7M |       407 |     +4 |     1 |       4G |      |       |         |       |          |                                     ||  6 |      INDEX STORAGE FAST FULL SCAN | TAB1_PK                   |      8G |   7M |       471 |     +1 |   579 |       4G | 510K | 244GB |  27.54% |   15M |    59.79 | Cpu (262)                           ||    |                                   |                           |         |      |           |        |       |          |      |       |         |       |          | reliable message (9)                ||    |                                   |                           |         |      |           |        |       |          |      |       |         |       |          | cell single block physical read (1) ||    |                                   |                           |         |      |           |        |       |          |      |       |         |       |          | cell smart index scan (9)           |=================================================================================================================================================================================================================Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)   4 - access("CNTRL_TAB"."CNTRL_ID"=111)

With hints:-

SQL Text------------------------------SELECT/*+ INDEX(TAB1 TAB1_PK) */ MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1, CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULLGlobal Information------------------------------ Status              :  DONE (ALL ROWS)            Instance ID         :  3                          SQL ID              :  5xrjk1zynqyt8              SQL Execution ID    :  50331648                   Execution Started   :  07/09/2020 09:19:39        First Refresh Time  :  07/09/2020 09:19:39        Last Refresh Time   :  07/09/2020 09:19:39        Duration            :  .002947s                   Module/Action       :  SQL*Plus/-                 Program             :  sqlplus.exe                Fetch Calls         :  1                         Global Stats======================================| Elapsed |   Cpu   | Fetch | Buffer || Time(s) | Time(s) | Calls |  Gets  |======================================|    0.00 |    0.00 |     1 |   1615 |======================================SQL Plan Monitoring Details (Plan Hash Value=2283604281)==========================================================================================================================================================| Id |            Operation            |           Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Activity | Activity Detail ||    |                                 |                           | (Estim) |      | Active(s) | Active |       | (Actual) |   (%)    |   (# samples)   |==========================================================================================================================================================|  0 | SELECT STATEMENT                |                           |         |      |         1 |     +0 |     1 |        1 |          |                 ||  1 |   SORT AGGREGATE                |                           |       1 |      |         1 |     +0 |     1 |        1 |          |                 ||  2 |    NESTED LOOPS                 |                           |      8G |  27M |         1 |     +0 |     1 |      496 |          |                 ||  3 |     TABLE ACCESS BY INDEX ROWID | CNTRL_TAB                 |       1 |    1 |         1 |     +0 |     1 |        1 |          |                 ||  4 |      INDEX UNIQUE SCAN          | CONTROLTAB_PK             |       1 |      |         1 |     +0 |     1 |        1 |          |                 ||  5 |     PARTITION LIST ALL          |                           |      8G |  27M |         1 |     +0 |     1 |      496 |          |                 ||  6 |      FIRST ROW                  |                           |      8G |  27M |         1 |     +0 |   579 |      496 |          |                 ||  7 |       INDEX FULL SCAN (MIN/MAX) | TAB1_PK                   |      8G |  27M |         1 |     +0 |   579 |      496 |          |                 |==========================================================================================================================================================Predicate Information (identified by operation id):---------------------------------------------------   3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)   4 - access("CNTRL_TAB"."CNTRL_ID"=111)

********************* Tweaking the query using Exists Operator*********

SELECT MAX (TAB1.FCT_KEY) AS MX_VAL          FROM TAB1          where exists (select 1 from  CNTRL_TAB                     WHERE     CNTRL_TAB.CNTRL_ID = 111                           AND CNTRL_TAB.STATUS IS NOT NULL)                           Execution Plan----------------------------------------------------------Plan hash value: 2799955177---------------------------------------------------------------------------------------------------------------------------| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |---------------------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |                           |     1 |     8 |  1160   (0)| 00:00:14 |       |       ||   1 |  SORT AGGREGATE               |                           |     1 |     8 |            |       |          |       ||*  2 |   FILTER                      |                           |       |       |            |       |          |       ||   3 |    PARTITION LIST ALL         |                           |     1 |     8 |  1159   (0)| 00:00:14 |   579 |     1 ||   4 |     FIRST ROW                 |                           |     1 |     8 |  1159   (0)| 00:00:14 |       |       ||   5 |      INDEX FULL SCAN (MIN/MAX)| TAB1_PK                   |     1 |     8 |  1159   (0)| 00:00:14 |   579 |     1 ||*  6 |    TABLE ACCESS BY INDEX ROWID| CNTRL_TAB                 |     1 |     6 |     1   (0)| 00:00:01 |       |       ||*  7 |     INDEX UNIQUE SCAN         | CONTROLTAB_PK             |     1 |       |     0   (0)| 00:00:01 |       |       |---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter( EXISTS (SELECT 0 FROM "USER1"."CNTRL_TAB" "CNTRL_TAB" WHERE              "CNTRL_TAB"."CNTRL_ID"=111 AND "CNTRL_TAB"."STATUS" IS NOT NULL))   6 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)   7 - access("CNTRL_TAB"."CNTRL_ID"=111)  
· Share on TwitterShare on Facebook

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    edited Jul 10, 2020 8:06AM Answer ✓

    It's a bug in the optimizer - easy to demonstrate - revolving around table joins breaking the index(min/max) costing.  From an SQL*Plus session Oracle 12.2.0.1:

    SQL> create table t1 as select * from all_objects where rownum <= 50000;

    Table created.

    SQL> create index t1_i1 on t1(object_name);

    Index created.

    SQL> set autotrace traceonly explain

    SQL> select max(object_name) from t1;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 1743745495

    --------------------------------------------------------------------

    | Id  | Operation                  | Name  | Rows  | Bytes | Cost  |

    --------------------------------------------------------------------

    |  0  | SELECT STATEMENT           |      |    1 |    39 |    3 |

    |  1  |  SORT AGGREGATE            |      |    1 |    39 |      |

    |  2  |   INDEX FULL SCAN (MIN/MAX)| T1_I1 |    1 |    39 |    3 |

    --------------------------------------------------------------------

    SQL> select max(object_name) from t1, dual where dummy is not null;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 805254539

    ----------------------------------------------------------------

    | Id  | Operation              | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------

    |  0  | SELECT STATEMENT       |      |    1 |    41 |    56 |

    |  1  |  SORT AGGREGATE        |      |    1 |    41 |      |

    |  2  |   NESTED LOOPS         |      | 50000 |  2001K|    56 |

    |* 3  |    TABLE ACCESS FULL   | DUAL  |    1 |    2 |    2 |

    |  4  |    INDEX FAST FULL SCAN| T1_I1 | 50000 |  1904K|    54 |

    ----------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      3 - filter("DUMMY" IS NOT NULL)

    SQL> select /*+ index(t1) */  max(object_name) from t1, dual where dummy is not null;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3144875158

    ----------------------------------------------------------------------

    | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------------

    |  0  | SELECT STATEMENT             |      |    1 |    41 |  351 |

    |  1  |  SORT AGGREGATE              |      |    1 |    41 |      |

    |  2  |   NESTED LOOPS               |      | 50000 |  2001K|  351 |

    |*  3 |    TABLE ACCESS FULL         | DUAL  |    1 |    2 |    2 |

    |  4  |    FIRST ROW                 |      | 50000 |  1904K|  349 |

    |  5  |     INDEX FULL SCAN (MIN/MAX)| T1_I1 | 50000 |  1904K|  349 |

    ----------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      3 - filter("DUMMY" IS NOT NULL)

    SQL> select max(object_name) from t1 where exists (select null from dual where dummy is not null);

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 4251389670

    ----------------------------------------------------------------------

    | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------------

    |  0  | SELECT STATEMENT             |      |    1 |    39 |    5 |

    |  1  |  SORT AGGREGATE              |      |    1 |    39 |      |

    |* 2  |   FILTER                     |      |      |      |      |

    |  3  |    FIRST ROW                 |      |    1 |    39 |    3 |

    |  4  |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |    1 |    39 |    3 |

    |* 5  |    TABLE ACCESS FULL         | DUAL  |    1 |    2 |    2 |

    ----------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      2 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"

                  IS NOT NULL))

      5 - filter("DUMMY" IS NOT NULL)

    A couple of points:  the index() hint is synonymous with the index_rs() hint (index range scan) which is probably why it blocked the index fast full scan (index_ffs() hint).

    The "where exists" is a plan with two query blocks and the existence test is not correlated so

    (a) the max(object_name) query block is optimised separately from the (select from dual) query block, and gets the right plan

    (b) the select from dual actually executes BEFORE the select from t1.

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook

Answers

  • Arsalan Dehghanisariyarghan
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy
    edited Jul 9, 2020 4:22PM 
    933257 wrote:Hi we are using version 11.2.0.4 of Oracle Exadata. Below query is running for ~7-8 minutes. And the way this query is written here such that , table CNTRL_TAB is a just a control table so there is no join between the main table- TAB1 and this control table. And TAB1 is list partitioned on column DATE_KEY. And index TAB1_PK is composite index on (FCT_KEY,DATE_KEY).And i saw the execution path , its going for a "INDEX STORAGE FAST FULL SCAN" path by default. but i see we are only picking up the MAX value of a column which is leading column of an unique index. Now when i tried putting hint INDEX(TAB1 TAB1_PK), it went for "INDEX FULL SCAN (MIN/MAX)" and finished in seconds. So wanted to understand why by default optimizer is not following the MIN/MAX optimization? From the plan it shows the expected costing of "INDEX FULL SCAN (MIN/MAX)" coming as ~27million as compared to the cost of "INDEX STORAGE FAST FULL SCAN" in the default path which is ~7million for same ~8billion rows estimated in both of the cases. But in case of INDEX FULL SCAN (MIN/MAX) the number of actual rows coming as ~496.Another way i tried tweaking the query, using exists operator then i see its following MIN/MAX optimization an finishing i seconds. Why its not following that path by default?Also the surprising part is even by default its using the same index but to be exact its doing "INDEX STORAGE FAST FULL SCAN". So how-come if i hint the query using same index(means i have not supplied any additional input/influencer to the query) its opting "INDEX FULL SCAN (MIN/MAX)"?Default execution path:-
    1. SQLText
    2. ------------------------------
    3. SELECTMAX(TAB1.FCT_KEY)ASMX_VALFROMTAB1,
    4. CNTRL_TABWHERECNTRL_TAB.CNTRL_ID=111ANDCNTRL_TAB.STATUSISNOTNULL
    6. GlobalInformation
    7. ------------------------------
    8. Status:DONE(ALLROWS)
    9. InstanceID:3
    10. SQLID:2y1x33t9vf9xx
    11. SQLExecutionID:50331648
    12. ExecutionStarted:07/09/202009:08:48
    13. FirstRefreshTime:07/09/202009:08:52
    14. LastRefreshTime:07/09/202009:16:38
    15. Duration:470s
    16. Module/Action:SQL*Plus/-
    17. Program:sqlplus.exe
    18. FetchCalls:1
    20. GlobalStats
    21. ==============================================================================================================
    22. |Elapsed|Cpu|IO|Application|Cluster|Other|Fetch|Buffer|Read|Read|Cell|
    23. |Time(s)|Time(s)|Waits(s)|Waits(s)|Waits(s)|Waits(s)|Calls|Gets|Reqs|Bytes|Offload|
    24. ==============================================================================================================
    25. |470|446|15|0.34|0.10|10|1|32M|514K|245GB|27.54%|
    26. ==============================================================================================================
    28. SQLPlanMonitoringDetails(PlanHashValue=3656453152)
    29. =================================================================================================================================================================================================================
    30. |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Read|Read|Cell|Mem|Activity|ActivityDetail|
    31. ||||(Estim)||Active(s)|Active||(Actual)|Reqs|Bytes|Offload|(Max)|(%)|(#samples)|
    32. =================================================================================================================================================================================================================
    33. |0|SELECTSTATEMENT||||467|+4|1|1|||||||
    34. |1|SORTAGGREGATE||1||468|+3|1|1|||||40.21|Cpu(189)|
    35. |2|NESTEDLOOPS||8G|7M|407|+4|1|4G|||||||
    36. |3|TABLEACCESSBYINDEXROWID|CNTRL_TAB|1|1|1|+4|1|1|||||||
    37. |4|INDEXUNIQUESCAN|CONTROLTAB_PK|1||467|+4|1|1|||||||
    38. |5|PARTITIONLISTALL||8G|7M|407|+4|1|4G|||||||
    39. |6|INDEXSTORAGEFASTFULLSCAN|TAB1_PK|8G|7M|471|+1|579|4G|510K|244GB|27.54%|15M|59.79|Cpu(262)|
    40. |||||||||||||||reliablemessage(9)|
    41. |||||||||||||||cellsingleblockphysicalread(1)|
    42. |||||||||||||||cellsmartindexscan(9)|
    43. =================================================================================================================================================================================================================
    45. PredicateInformation(identifiedbyoperationid):
    46. ---------------------------------------------------
    48. 3-filter("CNTRL_TAB"."STATUS"ISNOTNULL)
    49. 4-access("CNTRL_TAB"."CNTRL_ID"=111)
    SQL Text ------------------------------ SELECT MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1, CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULL  Global Information ------------------------------  Status : DONE (ALL ROWS)   Instance ID : 3   SQL ID : 2y1x33t9vf9xx   SQL Execution ID : 50331648   Execution Started : 07/09/2020 09:08:48   First Refresh Time : 07/09/2020 09:08:52   Last Refresh Time : 07/09/2020 09:16:38   Duration : 470s   Module/Action : SQL*Plus/-   Program : sqlplus.exe   Fetch Calls : 1   Global Stats ============================================================================================================== | Elapsed | Cpu | IO | Application | Cluster | Other | Fetch | Buffer | Read | Read | Cell | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload | ============================================================================================================== | 470 | 446 | 15 | 0.34 | 0.10 | 10 | 1 | 32M | 514K | 245GB | 27.54% | ==============================================================================================================  SQL Plan Monitoring Details (Plan Hash Value=3656453152) ================================================================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) | ================================================================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | 467 | +4 | 1 | 1 | | | | | | | | 1 | SORT AGGREGATE | | 1 | | 468 | +3 | 1 | 1 | | | | | 40.21 | Cpu (189) | | 2 | NESTED LOOPS | | 8G | 7M | 407 | +4 | 1 | 4G | | | | | | | | 3 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +4 | 1 | 1 | | | | | | | | 4 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 467 | +4 | 1 | 1 | | | | | | | | 5 | PARTITION LIST ALL | | 8G | 7M | 407 | +4 | 1 | 4G | | | | | | | | 6 | INDEX STORAGE FAST FULL SCAN | TAB1_PK | 8G | 7M | 471 | +1 | 579 | 4G | 510K | 244GB | 27.54% | 15M | 59.79 | Cpu (262) | | | | | | | | | | | | | | | | reliable message (9) | | | | | | | | | | | | | | | | cell single block physical read (1) | | | | | | | | | | | | | | | | cell smart index scan (9) | =================================================================================================================================================================================================================  Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)  4 - access("CNTRL_TAB"."CNTRL_ID"=111)
    With hints:-
    1. SQLText
    2. ------------------------------
    3. SELECT/*+INDEX(TAB1TAB1_PK)*/MAX(TAB1.FCT_KEY)ASMX_VALFROMTAB1,CNTRL_TABWHERECNTRL_TAB.CNTRL_ID=111ANDCNTRL_TAB.STATUSISNOTNULL
    5. GlobalInformation
    6. ------------------------------
    7. Status:DONE(ALLROWS)
    8. InstanceID:3
    9. SQLID:5xrjk1zynqyt8
    10. SQLExecutionID:50331648
    11. ExecutionStarted:07/09/202009:19:39
    12. FirstRefreshTime:07/09/202009:19:39
    13. LastRefreshTime:07/09/202009:19:39
    14. Duration:.002947s
    15. Module/Action:SQL*Plus/-
    16. Program:sqlplus.exe
    17. FetchCalls:1
    19. GlobalStats
    20. ======================================
    21. |Elapsed|Cpu|Fetch|Buffer|
    22. |Time(s)|Time(s)|Calls|Gets|
    23. ======================================
    24. |0.00|0.00|1|1615|
    25. ======================================
    27. SQLPlanMonitoringDetails(PlanHashValue=2283604281)
    28. ==========================================================================================================================================================
    29. |Id|Operation|Name|Rows|Cost|Time|Start|Execs|Rows|Activity|ActivityDetail|
    30. ||||(Estim)||Active(s)|Active||(Actual)|(%)|(#samples)|
    31. ==========================================================================================================================================================
    32. |0|SELECTSTATEMENT||||1|+0|1|1|||
    33. |1|SORTAGGREGATE||1||1|+0|1|1|||
    34. |2|NESTEDLOOPS||8G|27M|1|+0|1|496|||
    35. |3|TABLEACCESSBYINDEXROWID|CNTRL_TAB|1|1|1|+0|1|1|||
    36. |4|INDEXUNIQUESCAN|CONTROLTAB_PK|1||1|+0|1|1|||
    37. |5|PARTITIONLISTALL||8G|27M|1|+0|1|496|||
    38. |6|FIRSTROW||8G|27M|1|+0|579|496|||
    39. |7|INDEXFULLSCAN(MIN/MAX)|TAB1_PK|8G|27M|1|+0|579|496|||
    40. ==========================================================================================================================================================
    42. PredicateInformation(identifiedbyoperationid):
    43. ---------------------------------------------------
    45. 3-filter("CNTRL_TAB"."STATUS"ISNOTNULL)
    46. 4-access("CNTRL_TAB"."CNTRL_ID"=111)
    SQL Text ------------------------------ SELECT/*+ INDEX(TAB1 TAB1_PK) */ MAX (TAB1.FCT_KEY) AS MX_VAL FROM TAB1, CNTRL_TAB WHERE CNTRL_TAB.CNTRL_ID = 111 AND CNTRL_TAB.STATUS IS NOT NULL  Global Information ------------------------------  Status : DONE (ALL ROWS)   Instance ID : 3   SQL ID : 5xrjk1zynqyt8   SQL Execution ID : 50331648   Execution Started : 07/09/2020 09:19:39   First Refresh Time : 07/09/2020 09:19:39   Last Refresh Time : 07/09/2020 09:19:39   Duration : .002947s   Module/Action : SQL*Plus/-   Program : sqlplus.exe   Fetch Calls : 1   Global Stats ====================================== | Elapsed | Cpu | Fetch | Buffer | | Time(s) | Time(s) | Calls | Gets | ====================================== | 0.00 | 0.00 | 1 | 1615 | ======================================  SQL Plan Monitoring Details (Plan Hash Value=2283604281) ========================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | ========================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | | | 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | | | 2 | NESTED LOOPS | | 8G | 27M | 1 | +0 | 1 | 496 | | | | 3 | TABLE ACCESS BY INDEX ROWID | CNTRL_TAB | 1 | 1 | 1 | +0 | 1 | 1 | | | | 4 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 1 | +0 | 1 | 1 | | | | 5 | PARTITION LIST ALL | | 8G | 27M | 1 | +0 | 1 | 496 | | | | 6 | FIRST ROW | | 8G | 27M | 1 | +0 | 579 | 496 | | | | 7 | INDEX FULL SCAN (MIN/MAX) | TAB1_PK | 8G | 27M | 1 | +0 | 579 | 496 | | | ==========================================================================================================================================================  Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)  4 - access("CNTRL_TAB"."CNTRL_ID"=111)
    ********************* Tweaking the query using Exists Operator*********
    1. SELECTMAX(TAB1.FCT_KEY)ASMX_VAL
    2. FROMTAB1
    3. whereexists(select1fromCNTRL_TAB
    4. WHERECNTRL_TAB.CNTRL_ID=111
    5. ANDCNTRL_TAB.STATUSISNOTNULL)
    7. ExecutionPlan
    8. ----------------------------------------------------------
    9. Planhashvalue:2799955177
    11. ---------------------------------------------------------------------------------------------------------------------------
    12. |Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|Pstart|Pstop|
    13. ---------------------------------------------------------------------------------------------------------------------------
    14. |0|SELECTSTATEMENT||1|8|1160(0)|00:00:14|||
    15. |1|SORTAGGREGATE||1|8|||||
    16. |*2|FILTER||||||||
    17. |3|PARTITIONLISTALL||1|8|1159(0)|00:00:14|579|1|
    18. |4|FIRSTROW||1|8|1159(0)|00:00:14|||
    19. |5|INDEXFULLSCAN(MIN/MAX)|TAB1_PK|1|8|1159(0)|00:00:14|579|1|
    20. |*6|TABLEACCESSBYINDEXROWID|CNTRL_TAB|1|6|1(0)|00:00:01|||
    21. |*7|INDEXUNIQUESCAN|CONTROLTAB_PK|1||0(0)|00:00:01|||
    22. ---------------------------------------------------------------------------------------------------------------------------
    24. PredicateInformation(identifiedbyoperationid):
    25. ---------------------------------------------------
    27. 2-filter(EXISTS(SELECT0FROM"USER1"."CNTRL_TAB""CNTRL_TAB"WHERE
    28. "CNTRL_TAB"."CNTRL_ID"=111AND"CNTRL_TAB"."STATUS"ISNOTNULL))
    29. 6-filter("CNTRL_TAB"."STATUS"ISNOTNULL)
    30. 7-access("CNTRL_TAB"."CNTRL_ID"=111)
    SELECT MAX (TAB1.FCT_KEY) AS MX_VAL  FROM TAB1  where exists (select 1 from CNTRL_TAB  WHERE CNTRL_TAB.CNTRL_ID = 111  AND CNTRL_TAB.STATUS IS NOT NULL)   Execution Plan ---------------------------------------------------------- Plan hash value: 2799955177  --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 8 | 1160 (0)| 00:00:14 | | | | 1 | SORT AGGREGATE | | 1 | 8 | | | | | |* 2 | FILTER | | | | | | | | | 3 | PARTITION LIST ALL | | 1 | 8 | 1159 (0)| 00:00:14 | 579 | 1 | | 4 | FIRST ROW | | 1 | 8 | 1159 (0)| 00:00:14 | | | | 5 | INDEX FULL SCAN (MIN/MAX)| TAB1_PK | 1 | 8 | 1159 (0)| 00:00:14 | 579 | 1 | |* 6 | TABLE ACCESS BY INDEX ROWID| CNTRL_TAB | 1 | 6 | 1 (0)| 00:00:01 | | | |* 7 | INDEX UNIQUE SCAN | CONTROLTAB_PK | 1 | | 0 (0)| 00:00:01 | | | ---------------------------------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------   2 - filter( EXISTS (SELECT 0 FROM "USER1"."CNTRL_TAB" "CNTRL_TAB" WHERE  "CNTRL_TAB"."CNTRL_ID"=111 AND "CNTRL_TAB"."STATUS" IS NOT NULL))  6 - filter("CNTRL_TAB"."STATUS" IS NOT NULL)  7 - access("CNTRL_TAB"."CNTRL_ID"=111)

    933257,  I think, the cost-based optimizer is incorrectly calculating the cost of the index with statistics.  You can gather a 10053 trace file before and after adding a hint that shows the cost of the index. Also, you can check the following:

    1- Are the table and index stats up to date?

    2- Has anyone changed system settings parameters?

    3- Are there SQL profiles or plan baselines for this query?

    Cheers,

    Arsalan

    · Share on TwitterShare on Facebook
  • User_OCZ1T
    User_OCZ1T Member Posts: 1,923 Gold Badge
    edited Jul 10, 2020 5:48AM

    No Baseline/profile in place for this query. optimizer parameters are all default.  I am not able to convinced if its simply  because of because of stats. Also will try to regather stats but if i see the global stats i see the number of rows in table is ~8billion and same is being noted in global stats for the index i.e in dba_indexes in column num_distinct and num_rows as its a primary key.  And as i mentioned there are multiple oddities i see in the plan and just wanted to ensure if we are not hitting any special condition

    1) by simple modifying the query using Exists operator making it go for the MIN/MAX optimization by default.

    2)If i see the existing query path its estimating 8billion rows for the INDEX STORAGE FAST FULL SCAN but the cost is coming as 7Million whereas during forced index path the estimation is same 8billion but the cost becomes ~27million. Why?

    3) And also when i am using same index hints, why the optimizer changes the path to min/max optimization which its not doing by default with same index scan?

    · Share on TwitterShare on Facebook
  • Arsalan Dehghanisariyarghan
    Arsalan Dehghanisariyarghan Member Posts: 366 Bronze Trophy
    edited Jul 10, 2020 6:10AM 
    2)If i see the existing query path its estimating 8billion rows for the INDEX STORAGE FAST FULL SCAN but the cost is coming as 7Million whereas during forced index path the estimation is same 8billion but the cost becomes ~27million. Why?

    I think it's more like a bug. Bug because CBO has computed it wrongly that INDEX STORAGE FAST FULL SCAN is good so SQL query choosing the Index storage Fast Full Scan and causing the bad plan. You can check it when the stats are deleted on the table, whether Index Full (Min/Max) will be selected or not.

    We also need more analysis from 10053 For both good and bad plans.

    Regards

    Arsalan

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,975 Blue Diamond
    edited Jul 10, 2020 8:06AM Answer ✓

    It's a bug in the optimizer - easy to demonstrate - revolving around table joins breaking the index(min/max) costing.  From an SQL*Plus session Oracle 12.2.0.1:

    SQL> create table t1 as select * from all_objects where rownum <= 50000;

    Table created.

    SQL> create index t1_i1 on t1(object_name);

    Index created.

    SQL> set autotrace traceonly explain

    SQL> select max(object_name) from t1;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 1743745495

    --------------------------------------------------------------------

    | Id  | Operation                  | Name  | Rows  | Bytes | Cost  |

    --------------------------------------------------------------------

    |  0  | SELECT STATEMENT           |      |    1 |    39 |    3 |

    |  1  |  SORT AGGREGATE            |      |    1 |    39 |      |

    |  2  |   INDEX FULL SCAN (MIN/MAX)| T1_I1 |    1 |    39 |    3 |

    --------------------------------------------------------------------

    SQL> select max(object_name) from t1, dual where dummy is not null;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 805254539

    ----------------------------------------------------------------

    | Id  | Operation              | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------

    |  0  | SELECT STATEMENT       |      |    1 |    41 |    56 |

    |  1  |  SORT AGGREGATE        |      |    1 |    41 |      |

    |  2  |   NESTED LOOPS         |      | 50000 |  2001K|    56 |

    |* 3  |    TABLE ACCESS FULL   | DUAL  |    1 |    2 |    2 |

    |  4  |    INDEX FAST FULL SCAN| T1_I1 | 50000 |  1904K|    54 |

    ----------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      3 - filter("DUMMY" IS NOT NULL)

    SQL> select /*+ index(t1) */  max(object_name) from t1, dual where dummy is not null;

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 3144875158

    ----------------------------------------------------------------------

    | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------------

    |  0  | SELECT STATEMENT             |      |    1 |    41 |  351 |

    |  1  |  SORT AGGREGATE              |      |    1 |    41 |      |

    |  2  |   NESTED LOOPS               |      | 50000 |  2001K|  351 |

    |*  3 |    TABLE ACCESS FULL         | DUAL  |    1 |    2 |    2 |

    |  4  |    FIRST ROW                 |      | 50000 |  1904K|  349 |

    |  5  |     INDEX FULL SCAN (MIN/MAX)| T1_I1 | 50000 |  1904K|  349 |

    ----------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      3 - filter("DUMMY" IS NOT NULL)

    SQL> select max(object_name) from t1 where exists (select null from dual where dummy is not null);

    Execution Plan

    ----------------------------------------------------------

    Plan hash value: 4251389670

    ----------------------------------------------------------------------

    | Id  | Operation                    | Name  | Rows  | Bytes | Cost  |

    ----------------------------------------------------------------------

    |  0  | SELECT STATEMENT             |      |    1 |    39 |    5 |

    |  1  |  SORT AGGREGATE              |      |    1 |    39 |      |

    |* 2  |   FILTER                     |      |      |      |      |

    |  3  |    FIRST ROW                 |      |    1 |    39 |    3 |

    |  4  |     INDEX FULL SCAN (MIN/MAX)| T1_I1 |    1 |    39 |    3 |

    |* 5  |    TABLE ACCESS FULL         | DUAL  |    1 |    2 |    2 |

    ----------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

      2 - filter( EXISTS (SELECT 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"

                  IS NOT NULL))

      5 - filter("DUMMY" IS NOT NULL)

    A couple of points:  the index() hint is synonymous with the index_rs() hint (index range scan) which is probably why it blocked the index fast full scan (index_ffs() hint).

    The "where exists" is a plan with two query blocks and the existence test is not correlated so

    (a) the max(object_name) query block is optimised separately from the (select from dual) query block, and gets the right plan

    (b) the select from dual actually executes BEFORE the select from t1.

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • User_OCZ1T
    User_OCZ1T Member Posts: 1,923 Gold Badge
    edited Jul 10, 2020 9:37AM

    Thank you so much. You explained it so nicely with example, i got all my doubt cleared around the optimizer behavior along with buggy costing part.

    · Share on TwitterShare on Facebook