14 Replies Latest reply on Jul 3, 2015 4:33 PM by user12057600

    oracle not choosing the best exec plan

    user12057600

      Hi,

       

      my db version is 11.2.0.3  64bit std edition running on Linux..

       

      One of my table txn_header size 19GB has the following indexes.

       

      TXN_HEADER_IDX11 on TXN_HEADER(TH_UPDATE_TIMESTAMP);

      TXN_HEADER_IDX17 on TXN_HEADER(TH_UPDATE_TIMESTAMP DESC ,TH_TXN_CODE);

      TXN_HEADER_IDX14 on TXN_HEADER(TH_MERCHANT_ID);

       

      11:08:44 SYS@dr AS SYSDBA> SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE;

       

          COUNT(*)

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

           2119828

       

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

      | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT   |                  |       |       |     4 (100)|          |

      |   1 |  SORT AGGREGATE    |                  |     1 |     8 |            |          |

      |*  2 |   FILTER           |                  |       |       |            |          |

      |*  3 |    INDEX RANGE SCAN| TXN_HEADER_IDX17 |  1083K|  8462K|     4   (0)| 00:00:01 |

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

       

       

      M8000013 has the least number of records.

       

      11:09:55 SYS@dr AS SYSDBA> SELECT COUNT(*) FROM TXN_HEADER WHERE TH_MERCHANT_ID='M8000013';

       

       

          COUNT(*)

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

                47


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

      | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT  |                  |       |       |    24 (100)|          |

      |   1 |  SORT AGGREGATE   |                  |     1 |     6 |            |          |

      |*  2 |   INDEX RANGE SCAN| TXN_HEADER_IDX14 |  2533 | 15198 |    24   (0)| 00:00:01 |

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

       

       

       

      oracle not choosing the index on th_merchant_id and still use index (th_update_timestamp desc),

      any ideas ??

       

      11:16:49 SYS@dr AS SYSDBA> SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE AND TH_MERCHANT_ID='M8000013';

       

          COUNT(*)

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

                 0

       

      1 row selected.

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

      | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

      |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

      |   1 |  SORT AGGREGATE               |                  |     1 |    14 |            |          |

      |*  2 |   FILTER                      |                  |       |       |            |          |

      |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |    31 |   434 |     5   (0)| 00:00:01 |

      |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     4   (0)| 00:00:01 |

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

       

      Rgs,

      Reeds

       

       

       

       

       

       

       

       

       

       

        • 1. Re: oracle not choosing the best exec plan

          It appears that you suffer from Compulsive Tuning Disorder.

          Why are you wasting your time (& ours) on SQL that completes in 1 second?

           

          If you know the CBO is broken, then you should submit a Bug Report since we can change how it behaves.

          • 2. Re: oracle not choosing the best exec plan
            Hemant K Chitale

            Do an Event 10053 trace to see what options Oracle evaluated and why it chose this index.

             

             

            Hemant K Chitale

            • 3. Re: oracle not choosing the best exec plan
              AndrewSayer

              Have you had a look at the stats you have for the index it is using? The cost of 4  in your first plan doesn't immediately look correct for the number of rows it should return . What happens when you hint for the index on th_merchant_id? Its worth considering if your predicate is a special case, are most values of th_merchant_id going to return few rows? The optimizer thinks on average it would have to read 2533 rows, take into account the clustering factor of this index and you could have a larger cost than assumed.

              • 4. Re: oracle not choosing the best exec plan
                JohnWatson2

                Is it possible that rows with the same TH_MERCHANT_ID are distributed throughout the table, whereas your TH_UPDATE_TIMESTAMPs are more closely grouped together? If so, it may be the correct decision. If you look at the DBA_INDEXES.CLUSTERING_FACTOR you my see this.

                • 5. Re: oracle not choosing the best exec plan
                  Why are you wasting your time (& ours) on SQL that completes in 1 second?


                  Don't waste your time then! Noone force you to waste your time. This is open independent forum for everyone. You don't have to help people. if you are complaining, don't cause visual pollution then! This is called impudence!     


                  you should submit a Bug Report since we can change how it behaves.

                   

                  Who is "we"?? Who are you? and who are you speaking for?

                  • 6. Re: oracle not choosing the best exec plan
                    Jonathan Lewis

                    The first thing we need to see is how Oracle has used your predicates - a plan is not complete unless it also shows the predicate section. It would also be nice to know whether the "timestamp" column was declared as type TIMESTAMP, or whether it is simple of type DATE.

                     

                     

                    Taking a couple of guesses, though:

                     

                    FIRST OUTPUT: The cost and cardinality are (as Andrew Seyer points out) not consistent. A range scan that produced 1M dates would have to go through a minimum of around 2,500 leaf blocks (ca. 400 rows per leaf block) - so the cost should be 2,500+ or the cardinality should be about 800. This suggests that either you didn't collect stats on the hidden column supporting the DESCending declaration, or you've found an Oracle bug relating to range scans on descending indexes. If it is a bug, though, you have to remember that it might be purely about which numbers have been reported in the plan rather than the optimizer's final calculation of the numbers - which makes Hemant's comment about 10053 relevant.

                     

                    One test for you - check user_tab_cols (e.g. column_name, last_analyzed, num_distinct) for the table to see what that says about the hidden column's stats, there will be at least one column with a name like SYS_NC0000x$ that is the index's hidden column.  If the stats are missing or very old then create (PRIVATE) stats on the hidden column and see if the plan changes - method_opt=>'for all hidden columns size 1' should take the minimum resources, but will affect all the SYS_NC0000x$ columns).

                     

                     

                    THIRD OUTPUT:  the cardinality on the range scan is not consistent with that of the first output - which suggests the hypothesis that there's a display bug may be the correct one. The fact that the table cardinality is larger than the index cardinality tells you that there is definitely an optimizer anomaly (not necessarily a bug) in play.  Best guess is that the anomaly relates to "unknown value" with index range scans where the "guess percentage" for tables doesn't match the "guess percentage" for indexes; there's also a possibility, though, that this is telling us that you DO have stats on the hidden column, but they are a long way (up to 30 days given your predicate), out of date.

                     

                    Bottom Line:  I'd check that the stats were accurate; if they weren't I'd gather fresh stats to make them as possible and see if that made any difference to the plans.  Then, as Andrew suggests, I'd hint the plan I expected to see if that gave me any clue about the anomaly; then I would look at the 10053 (or raise an SR with Oracle and let them have the 10053).

                     

                     

                    UPDATE:  I've just done a quick scan of my examples of bugs with descending indexes and have one which is present even in 12.1.0.2 that MIGHT be relevant.  I've mentioned it in this OTN note, which links to a note on my blog. It's not an exact match for your problem, and it's hard to tell without the predicate sections, but it might be related: Index Full scan

                     

                     

                     

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: oracle not choosing the best exec plan
                      Jonathan Lewis

                      It's possible that this is a stripped down example identifying the key feature of a bigger problem, so I wouldn't be in too much of a hurry to say that a query that (the optimzer thinks) will complete in one second is a waste of time.

                       

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: oracle not choosing the best exec plan

                        Jonathan Lewis wrote:

                         

                        It's possible that this is a stripped down example identifying the key feature of a bigger problem, so I wouldn't be in too much of a hurry to say that a query that (the optimzer thinks) will complete in one second is a waste of time.

                         

                         

                        Regards

                        Jonathan Lewis

                         

                        Absolutely I agree with you.

                        • 9. Re: oracle not choosing the best exec plan
                          user12057600

                          Hi Jonathan,

                           

                          I have generated stats for hidden columns and repeat the sql stms again. The execution plan remains that same.

                          I also checked that the histogram for columns related and the last analyzed day.

                          The data type for th_update_timestamp is date.

                           

                           

                           

                          16:11:13 SYS:PHLIVE>EXEC DBMS_STATS.GATHER_TABLE_STATS('PHUSER','TXN_HEADER',METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE 1');

                           

                           

                          PL/SQL procedure successfully completed.

                           

                           

                          Elapsed: 00:07:02.26

                          16:18:59 SYS:PHLIVE>SELECT COLUMN_NAME,TABLE_NAME,LAST_ANALYZED FROM DBA_TAB_COLS WHERE COLUMN_NAME='SYS_NC00041$' AND TABLE_NAME='TXN_HEADER';

                           

                           

                          COLUMN_NAME                    TABLE_NAME                     LAST_ANALYZED

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

                          SYS_NC00041$                   TXN_HEADER                     02-JUL-2015 16:11:26

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:00.00

                          16:19:19 SYS:PHLIVE>SELECT COLUMN_NAME,TABLE_NAME,LAST_ANALYZED FROM DBA_TAB_COLS WHERE COLUMN_NAME='TH_UPDATE_TIMESTAMP' AND TABLE_NAME='TXN_HEADER';

                           

                           

                          COLUMN_NAME                    TABLE_NAME                     LAST_ANALYZED

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

                          TH_UPDATE_TIMESTAMP            TXN_HEADER                     13-JUN-2015 08:04:27

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:00.00

                          16:19:27 SYS:PHLIVE>SELECT COLUMN_NAME,TABLE_NAME,LAST_ANALYZED FROM DBA_TAB_COLS WHERE COLUMN_NAME='TH_MERCHANT_ID' AND TABLE_NAME='TXN_HEADER';

                           

                           

                          COLUMN_NAME                    TABLE_NAME                     LAST_ANALYZED

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

                          TH_MERCHANT_ID                 TXN_HEADER                     13-JUN-2015 08:04:27

                           

                           

                          1 row selected.

                           

                          Column histogram info:

                          ===================

                          OWNER                          COLUMN_NAME                     NUM_DISTINCT    NUM_NULLS  NUM_BUCKETS HISTOGRAM       LAST_ANALYZED         SAMPLE_SIZE

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

                          PHUSER                         TH_MERCHANT_ID*                           37     41038013           23 FREQUENCY       13-JUN-2015 08:04:27         9469

                          PHUSER                         SYS_NC00041$*                       47513600            0            1 NONE            02-JUL-2015 16:11:26     90302507

                          PHUSER                         TH_UPDATE_TIMESTAMP*                45973504            0            1 NONE            13-JUN-2015 08:04:27     89010780

                           

                           

                           

                          Try  again :

                          ===========================================================================================================

                          16:24:14 SYS:PHLIVE>SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE;

                           

                           

                              COUNT(*)

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

                               2130437

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:05.01

                          16:24:21 SYS:PHLIVE>@10g_expl_curr

                           

                           

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  98b4xcy6qn2n4, child number 0

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

                          SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN

                          SYSDATE-30 AND SYSDATE

                           

                           

                          Plan hash value: 1296452271

                           

                           

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

                          | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT   |                  |       |       |     4 (100)|          |

                          |   1 |  SORT AGGREGATE    |                  |     1 |     8 |            |          |

                          |*  2 |   FILTER           |                  |       |       |            |          |

                          |*  3 |    INDEX RANGE SCAN| TXN_HEADER_IDX17 |  1076K|  8413K|     4   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             2 - filter(SYSDATE@!-30<=SYSDATE@!)

                             3 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                        "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-30))

                                 filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-30 AND

                                        SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                           

                           

                           

                           

                          25 rows selected.

                           

                           

                          Elapsed: 00:00:00.09

                          16:24:27 SYS:PHLIVE>SELECT COUNT(*) FROM TXN_HEADER WHERE TH_MERCHANT_ID='M8000013';

                           

                           

                              COUNT(*)

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

                                    47

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:00.02

                          16:24:37 SYS:PHLIVE>@10g_expl_curr

                           

                           

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  c0cvvqysh0nfb, child number 0

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

                          SELECT COUNT(*) FROM TXN_HEADER WHERE TH_MERCHANT_ID='M8000013'

                           

                           

                          Plan hash value: 1138278069

                           

                           

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

                          | Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT  |                  |       |       |    24 (100)|          |

                          |   1 |  SORT AGGREGATE   |                  |     1 |     6 |            |          |

                          |*  2 |   INDEX RANGE SCAN| TXN_HEADER_IDX14 |  2601 | 15606 |    24   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             2 - access("TH_MERCHANT_ID"='M8000013')

                           

                           

                           

                           

                          19 rows selected.

                           

                           

                          Elapsed: 00:00:00.05

                          16:24:41 SYS:PHLIVE>SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE AND TH_MERCHANT_ID='M8000013';

                           

                           

                              COUNT(*)

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

                                     0

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:03.23

                          16:24:56 SYS:PHLIVE>@10g_expl_curr

                           

                           

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  5y35tma9x9dzw, child number 0

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

                          SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN

                          SYSDATE-30 AND SYSDATE AND TH_MERCHANT_ID='M8000013'

                           

                           

                          Plan hash value: 3646420005

                           

                           

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

                          | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                          |   1 |  SORT AGGREGATE               |                  |     1 |    14 |            |          |

                          |*  2 |   FILTER                      |                  |       |       |            |          |

                          |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |    31 |   434 |     5   (0)| 00:00:01 |

                          |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     4   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             2 - filter(SYSDATE@!-30<=SYSDATE@!)

                             3 - filter("TH_MERCHANT_ID"='M8000013')

                             4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                        "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-30))

                                 filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-30 AND

                                        SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                           

                           

                           

                           

                          27 rows selected.

                           

                           

                           

                          Choose more recent days (use sysdate-10)

                          ===============================================================================================================

                          Elapsed: 00:00:00.05

                          16:24:59 SYS:PHLIVE>SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-10 AND SYSDATE AND TH_MERCHANT_ID='M8000013';

                           

                           

                              COUNT(*)

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

                                     0

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:00.61

                          16:25:08 SYS:PHLIVE>@10g_expl_curr

                           

                           

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  aprhppgjtxzb4, child number 0

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

                          SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN

                          SYSDATE-10 AND SYSDATE AND TH_MERCHANT_ID='M8000013'

                           

                           

                          Plan hash value: 3646420005

                           

                           

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

                          | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                          |   1 |  SORT AGGREGATE               |                  |     1 |    14 |            |          |

                          |*  2 |   FILTER                      |                  |       |       |            |          |

                          |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    14 |     5   (0)| 00:00:01 |

                          |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     4   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             2 - filter(SYSDATE@!-10<=SYSDATE@!)

                             3 - filter("TH_MERCHANT_ID"='M8000013')

                             4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                        "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-10))

                                 filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-10 AND

                                        SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                           

                           

                           

                           

                          27 rows selected.

                           

                           

                          choose th_merchant_id which has  most number of values M8000016

                          =====================================================================================================

                          16:25:51 SYS:PHLIVE>select count(*) from txn_header where TH_MERCHANT_ID='M8000016';

                           

                           

                              COUNT(*)

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

                              30854397

                           

                           

                          1 row selected.

                           

                          =============================================================================================================

                          Elapsed: 00:00:00.03

                          16:25:10 SYS:PHLIVE>SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN SYSDATE-30 AND SYSDATE AND TH_MERCHANT_ID='M8000016';

                           

                           

                              COUNT(*)

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

                                790446

                           

                           

                          1 row selected.

                           

                           

                          Elapsed: 00:00:02.32

                          16:25:26 SYS:PHLIVE>@10g_expl_curr

                           

                           

                          PLAN_TABLE_OUTPUT

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

                          SQL_ID  78pqrnvr7a14k, child number 0

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

                          SELECT COUNT(*) FROM TXN_HEADER WHERE TH_UPDATE_TIMESTAMP BETWEEN

                          SYSDATE-30 AND SYSDATE AND TH_MERCHANT_ID='M8000016'

                           

                           

                          Plan hash value: 3646420005

                           

                           

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

                          | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                          |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                          |   1 |  SORT AGGREGATE               |                  |     1 |    14 |            |          |

                          |*  2 |   FILTER                      |                  |       |       |            |          |

                          |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |   375K|  5136K|     5   (0)| 00:00:01 |

                          |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     4   (0)| 00:00:01 |

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

                           

                           

                          Predicate Information (identified by operation id):

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

                           

                           

                             2 - filter(SYSDATE@!-30<=SYSDATE@!)

                             3 - filter("TH_MERCHANT_ID"='M8000016')

                             4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                        "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-30))

                                 filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-30 AND

                                        SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                           

                           

                           

                           

                          27 rows selected.

                           

                           

                          Elapsed: 00:00:00.05

                          • 10. Re: Re: oracle not choosing the best exec plan
                            AndrewSayer

                            Did you confirm whether there were up to date stats on the index itself?

                             

                            SELECT index_name, blevel, leaf_blocks, num_rows, sample_size, last_analyzed FROM all_indexes WHERE index_name = 'TXN_HEADER_IDX17';

                            • 11. Re: oracle not choosing the best exec plan
                              Jonathan Lewis

                              Definitely some buggy optimizer code there.

                              Note the "doubling up" of the range-based predicates that expresses them in two different ways.  I wrote something about the effect this had on cardinality, and variations across versions some time ago, see: https://jonathanlewis.wordpress.com/2015/01/12/fbi-bug-reprise/

                               

                              4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                            "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-10))

                                     filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-10 AND

                                            SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                               

                               

                              There is another detail to chase, though - I note that your last_analyzed on the actual timestamp column is 13th June, so the high-value on the column could be well below the SYSDATE that appears in the predicate, and is probably below the sysate - 10 (though there is, perhaps, an overlap with sysdate - 30).  This is probably have some impact on the behaviour of the optimizer - it's best to have the environment in as correct a state as possible when trying to pin down possible bugs.   Check the actual high value on the timestamp, and gather stats on that column as well.  (Check, also, if there are any "silly values" allowed to represent missing date, e.g. 1-Jan-1000, or 31-Dec-9999, as this can really mess with the optimizer).

                               

                               

                              Things I'd do: 

                                   Check high value on timestamp

                                   Test a query with timestamp between to_date('X','dd-mon-yyyy') and to_date('Y','dd-mon-yyyy') where X and Y are comfortably inside the date range that Oracle knows about

                                   Vary the test to move Y just outside the known range, and Y 10, 20, 30 days outside the date range.

                                   Collect stats on timestamp to bring it up to date - if you do have a couple of "silly values", create a histogram on the column to try and help the optimizer recognise the outliers

                                   Try the sysdate predicates again and repeat the fixed-date tests.

                               

                                   Repeat with the timestamp index marked as invisible

                                   Repeat with the timestamp index visible and the two-column index invisible.

                               

                              The purpose of the exercise is to pin down whether it's a problem with the statistics, or a problem that has something to do with the same column being the leading column of two indexes and being delclared descending in one of them.  (I may find time to play with this tomorrow.)

                               

                               

                              Regards

                              Jonathan Lewis

                              • 12. Re: oracle not choosing the best exec plan
                                user12057600

                                Hi,

                                 

                                 

                                I find that the bug 11072246 in metalink which maybe similar to my case.

                                Bug 11072246  Wrong Cardinality estimations for columns with DESC indexes

                                 

                                https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=1c3x6ktpbd_77&_afrLoop=500948133980132

                                and there is another link points out the  issue using desc index in 11.2.0.1.

                                http://www.ora-solutions.net/web/2010/10/25/suboptimal-prorated-density-calculation-with-desc-index-cardinality-feedback…

                                 

                                Below pls find my tests based on your proposal.

                                The following tests are performed in my dev db, since I don't want to

                                affect  the prod db by all means.

                                 

                                No matter how update-to-date  the indexes and columns statistics are, results are the same.

                                Oracle tend to choose the index with descending order.

                                 

                                     Check high value on timestamp

                                ===========================================================================================================

                                09:30:12 SYS:phdev>select min(th_update_timestamp), max(th_update_timestamp) from txn_header;

                                06-SEP-2012 15:13:47 02-JUL-2015 15:54:03

                                 

                                 

                                 

                                 

                                     Test a query with timestamp between to_date('X','dd-mon-yyyy') and to_date('Y','dd-mon-yyyy')

                                where X and Y are comfortably inside the date range that Oracle knows about

                                ===========================================================================================================

                                The last analyzed date on indexes and columns are 11-jun-2015

                                 

                                 

                                09:39:02 SYS:phdev>select count(*) from txn_header where th_update_timestamp between to_date('01-may-2015','dd-mon-yyyy')

                                                    and to_date( '30-may-2015','dd-mon-yyyy') AND th_merchant_id='MOB001';

                                           1

                                 

                                 

                                1 row selected.

                                 

                                 

                                09:39:10 SYS:phdev>@10g_expl_curr

                                SQL_ID  g2kpnc6qhbndr, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                to_date('01-may-2015','dd-mon-yyyy') and to_date(

                                '30-may-2015','dd-mon-yyyy') AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3493582655

                                 

                                 

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

                                | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT             |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE              |                  |     1 |    18 |            |          |

                                |*  2 |   TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter("TH_MERCHANT_ID"='MOB001')

                                   3 - access("TXN_HEADER"."SYS_NC00041$">=HEXTORAW('878CFAE1FEF8FEFAFF')  AND

                                              "TXN_HEADER"."SYS_NC00041$"<=HEXTORAW('878CFAFEF8FEF8FF') )

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE(' 2015-05-01

                                              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=T

                                              O_DATE(' 2015-05-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                 

                                 

                                09:55:37 SYS:phdev>select count(*) from txn_header where th_update_timestamp between to_date('01-jun-2015')

                                                      and to_date('10-jun-2015') AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                09:57:58 SYS:phdev>@10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  11gwb8nnp0r98, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                to_date('01-jun-2015') and to_date('10-jun-2015') AND

                                th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(TO_DATE('01-jun-2015')<=TO_DATE('10-jun-2015'))

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(TO_DATE('10-jun-2015')) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(TO_DATE('01-jun-2015')))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE('01-jun-2015') AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=TO_DATE('10-jun-2015')))

                                 

                                 

                                 

                                 

                                28 rows selected.

                                 

                                 

                                09:54:04 SYS:phdev>select count(*) from txn_header where th_update_timestamp between to_date('01-jun-2015')

                                                      and to_date('22-jun-2015') AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                09:54:06 SYS:phdev>@10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  5w31mzgmg2nnp, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                to_date('01-jun-2015') and to_date('22-jun-2015') AND

                                th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(TO_DATE('01-jun-2015')<=TO_DATE('22-jun-2015'))

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(TO_DATE('22-jun-2015')) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(TO_DATE('01-jun-2015')))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE('01-jun-2015') AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=TO_DATE('22-jun-2015')))

                                 

                                 

                                 

                                 

                                28 rows selected.

                                 

                                 

                                09:54:12 SYS:phdev>

                                 

                                 

                                 

                                 

                                 

                                 

                                Vary the test to move Y just outside the known range, and Y 10, 20, 30 days outside the date range.

                                ================================================================================================================

                                09:54:12 SYS:phdev>select count(*) from txn_header where th_update_timestamp between to_date('11-jun-2015')

                                                      and to_date('01-jul-2015') AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                09:55:31 SYS:phdev>@10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  72msn0vx5tygu, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                to_date('11-jun-2015') and to_date('01-jul-2015') AND

                                th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(TO_DATE('11-jun-2015')<=TO_DATE('01-jul-2015'))

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(TO_DATE('01-jul-2015')) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(TO_DATE('11-jun-2015')))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE('11-jun-2015') AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=TO_DATE('01-jul-2015')))

                                 

                                 

                                 

                                 

                                28 rows selected.

                                 

                                 

                                09:55:37 SYS:phdev>

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                     Collect stats on timestamp to bring it up to date - if you do have a couple of "silly values", create a histogram on the column to try and help the optimizer recognise the outliers

                                ===========================================================================================================================================

                                No silly values on th_update_timestamp

                                 

                                 

                                09:30:12 SYS:phdev>select min(th_update_timestamp), max(th_update_timestamp) from txn_header;

                                06-SEP-2012 15:13:47 02-JUL-2015 15:54:03

                                 

                                 

                                 

                                generate uptodate statistics

                                ========================

                                09:58:03 SYS:phdev>exec dbms_stats.gather_table_stats('phuser','txn_header',method_opt=>'for all columns size auto',cascade=>true);

                                 

                                 

                                PL/SQL procedure successfully completed.

                                 

                                 

                                11:37:39 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-30 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:37:42 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:37:48 SYS@phd AS SYSDBA> set lines 200

                                11:37:48 SYS@phd AS SYSDBA> set long 10000

                                11:37:48 SYS@phd AS SYSDBA> set longchunk 200

                                11:37:48 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:37:48   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:37:48   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  c5qtkz2kh1418, child number 1

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-30 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-30<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-30))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-30 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                11:37:48 SYS@phd AS SYSDBA> undef 1

                                11:37:48 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-20 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:37:55 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:37:57 SYS@phd AS SYSDBA> set lines 200

                                11:37:57 SYS@phd AS SYSDBA> set long 10000

                                11:37:57 SYS@phd AS SYSDBA> set longchunk 200

                                11:37:57 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:37:57   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:37:57   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  862rdjyx4vghf, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-20 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-20<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-20))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-20 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                11:37:57 SYS@phd AS SYSDBA> undef 1

                                11:37:57 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-10 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:38:06 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:38:09 SYS@phd AS SYSDBA> set lines 200

                                11:38:09 SYS@phd AS SYSDBA> set long 10000

                                11:38:09 SYS@phd AS SYSDBA> set longchunk 200

                                11:38:09 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:38:09   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:38:09   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  7ybh8bypg5kk3, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-10 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-10<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-10))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-10 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                 

                                 

                                 

                                 

                                     Try the sysdate predicates again and repeat the fixed-date tests.

                                ==================================================================================================================================

                                 

                                 

                                11:38:10 SYS@phd AS SYSDBA> undef 1

                                11:38:10 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between '13-jun-2015' and '13-jul_2015'  AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:38:59 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:39:03 SYS@phd AS SYSDBA> set lines 200

                                11:39:03 SYS@phd AS SYSDBA> set long 10000

                                11:39:03 SYS@phd AS SYSDBA> set longchunk 200

                                11:39:03 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:39:03   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:39:03   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  8jrphkv9hmgm1, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                '13-jun-2015' and '13-jul_2015'  AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3493582655

                                 

                                 

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

                                | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT             |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE              |                  |     1 |    18 |            |          |

                                |*  2 |   TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter("TH_MERCHANT_ID"='MOB001')

                                   3 - access("TXN_HEADER"."SYS_NC00041$">=HEXTORAW('878CF8F2FEF8FEFAFF')  AND

                                              "TXN_HEADER"."SYS_NC00041$"<=HEXTORAW('878CF9F2FEF8FEFAFF') )

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE(' 2015-06-13

                                              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=T

                                              O_DATE(' 2015-07-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

                                 

                                 

                                 

                                 

                                26 rows selected.

                                 

                                 

                                11:39:03 SYS@phd AS SYSDBA> undef 1

                                11:39:03 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between '13-jun-2015' and '13-jul-2015'  AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:39:30 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:39:33 SYS@phd AS SYSDBA> set lines 200

                                11:39:33 SYS@phd AS SYSDBA> set long 10000

                                11:39:33 SYS@phd AS SYSDBA> set longchunk 200

                                11:39:33 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:39:33   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:39:33   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  2rr7xn2mydy91, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                '13-jun-2015' and '13-jul-2015'  AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3493582655

                                 

                                 

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

                                | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT             |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE              |                  |     1 |    18 |            |          |

                                |*  2 |   TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter("TH_MERCHANT_ID"='MOB001')

                                   3 - access("TXN_HEADER"."SYS_NC00041$">=HEXTORAW('878CF8F2FEF8FEFAFF')  AND

                                              "TXN_HEADER"."SYS_NC00041$"<=HEXTORAW('878CF9F2FEF8FEFAFF') )

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE(' 2015-06-13

                                              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=T

                                              O_DATE(' 2015-07-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

                                 

                                 

                                 

                                 

                                26 rows selected.

                                 

                                 

                                11:39:34 SYS@phd AS SYSDBA> undef 1

                                11:39:34 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between '01-jun-2015' and '10-jul-2015'  AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:39:53 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:39:57 SYS@phd AS SYSDBA> set lines 200

                                11:39:57 SYS@phd AS SYSDBA> set long 10000

                                11:39:57 SYS@phd AS SYSDBA> set longchunk 200

                                11:39:57 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:39:57   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:39:57   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  b3g3xw5qbr28g, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                '01-jun-2015' and '10-jul-2015'  AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3493582655

                                 

                                 

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

                                | Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT             |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE              |                  |     1 |    18 |            |          |

                                |*  2 |   TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  3 |    INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter("TH_MERCHANT_ID"='MOB001')

                                   3 - access("TXN_HEADER"."SYS_NC00041$">=HEXTORAW('878CF8F5FEF8FEFAFF')  AND

                                              "TXN_HEADER"."SYS_NC00041$"<=HEXTORAW('878CF9FEF8FEF8FF') )

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=TO_DATE(' 2015-06-01

                                              00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=T

                                              O_DATE(' 2015-07-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

                                 

                                 

                                 

                                 

                                26 rows selected.

                                 

                                 

                                 

                                 

                                 

                                 

                                 

                                     Repeat with the timestamp index marked as invisible

                                ======================================================================================================================

                                11:39:58 SYS@phd AS SYSDBA> alter index txn_header_idx11 invisible;

                                 

                                 

                                Index altered.

                                 

                                 

                                11:43:04 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-30 and sysdate AND th_merchant_id='MO

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:43:26 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:43:29 SYS@phd AS SYSDBA> set lines 200

                                11:43:29 SYS@phd AS SYSDBA> set long 10000

                                11:43:29 SYS@phd AS SYSDBA> set longchunk 200

                                11:43:29 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:43:29   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:43:29   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  c5qtkz2kh1418, child number 1

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-30 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-30<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-30))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-30 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                11:43:29 SYS@phd AS SYSDBA> undef 1

                                11:43:29 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-20 and sysdate AND th_merchant_id='MO

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:43:36 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:43:37 SYS@phd AS SYSDBA> set lines 200

                                11:43:37 SYS@phd AS SYSDBA> set long 10000

                                11:43:37 SYS@phd AS SYSDBA> set longchunk 200

                                11:43:37 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:43:37   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:43:37   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  862rdjyx4vghf, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-20 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-20<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-20))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-20 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                11:43:38 SYS@phd AS SYSDBA> undef 1

                                11:43:38 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-10 and sysdate AND th_merchant_id='MO

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:43:43 SYS@phd AS SYSDBA> @10g_expl_curr

                                11:43:46 SYS@phd AS SYSDBA> set lines 200

                                11:43:46 SYS@phd AS SYSDBA> set long 10000

                                11:43:46 SYS@phd AS SYSDBA> set longchunk 200

                                11:43:46 SYS@phd AS SYSDBA> SELECT PLAN_TABLE_OUTPUT

                                11:43:46   2    FROM TABLE(DBMS_XPLAN.DISPLAY_cursor())

                                11:43:46   3  /

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  7ybh8bypg5kk3, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-10 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 3646420005

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     4 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     4   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX17 |     1 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-10<=SYSDATE@!)

                                   3 - filter("TH_MERCHANT_ID"='MOB001')

                                   4 - access("TXN_HEADER"."SYS_NC00041$">=SYS_OP_DESCEND(SYSDATE@!) AND

                                              "TXN_HEADER"."SYS_NC00041$"<=SYS_OP_DESCEND(SYSDATE@!-10))

                                       filter((SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")>=SYSDATE@!-10 AND

                                              SYS_OP_UNDESCEND("TXN_HEADER"."SYS_NC00041$")<=SYSDATE@!))

                                 

                                 

                                 

                                 

                                27 rows selected.

                                 

                                 

                                 

                                 

                                 

                                 

                                     Repeat with the timestamp index visible and the two-column index invisible.

                                ======================================================================================================================

                                 

                                 

                                11:44:19 SYS@phd AS SYSDBA> alter index txn_header_idx11 visible;

                                 

                                 

                                Index altered.

                                 

                                 

                                11:44:30 SYS@phd AS SYSDBA> alter index txn_header_idx17 invisible;

                                 

                                 

                                Index altered.

                                 

                                 

                                11:44:36 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-30 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:44:41 SYS@phd AS SYSDBA> @10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  c5qtkz2kh1418, child number 1

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-30 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 2256881367

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     5   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX14 |    41 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-30<=SYSDATE@!)

                                   3 - filter(("TH_UPDATE_TIMESTAMP">=SYSDATE@!-30 AND "TH_UPDATE_TIMESTAMP"<=SYSDATE@!))

                                   4 - access("TH_MERCHANT_ID"='MOB001')

                                 

                                 

                                 

                                 

                                24 rows selected.

                                 

                                 

                                11:44:48 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-20 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:44:56 SYS@phd AS SYSDBA> @10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  862rdjyx4vghf, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-20 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 2256881367

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     5   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX14 |    41 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-20<=SYSDATE@!)

                                   3 - filter(("TH_UPDATE_TIMESTAMP">=SYSDATE@!-20 AND "TH_UPDATE_TIMESTAMP"<=SYSDATE@!))

                                   4 - access("TH_MERCHANT_ID"='MOB001')

                                 

                                 

                                 

                                 

                                24 rows selected.

                                 

                                 

                                11:44:58 SYS@phd AS SYSDBA> select count(*) from txn_header where th_update_timestamp between sysdate-10 and sysdate AND th_merchant_id='MOB001';

                                 

                                 

                                    COUNT(*)

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

                                           0

                                 

                                 

                                1 row selected.

                                 

                                 

                                11:45:02 SYS@phd AS SYSDBA> @10g_expl_curr

                                 

                                 

                                PLAN_TABLE_OUTPUT

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

                                SQL_ID  7ybh8bypg5kk3, child number 0

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

                                select count(*) from txn_header where th_update_timestamp between

                                sysdate-10 and sysdate AND th_merchant_id='MOB001'

                                 

                                 

                                Plan hash value: 2256881367

                                 

                                 

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

                                | Id  | Operation                     | Name             | Rows  | Bytes | Cost (%CPU)| Time     |

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

                                |   0 | SELECT STATEMENT              |                  |       |       |     5 (100)|          |

                                |   1 |  SORT AGGREGATE               |                  |     1 |    18 |            |          |

                                |*  2 |   FILTER                      |                  |       |       |            |          |

                                |*  3 |    TABLE ACCESS BY INDEX ROWID| TXN_HEADER       |     1 |    18 |     5   (0)| 00:00:01 |

                                |*  4 |     INDEX RANGE SCAN          | TXN_HEADER_IDX14 |    41 |       |     3   (0)| 00:00:01 |

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

                                 

                                 

                                Predicate Information (identified by operation id):

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

                                 

                                 

                                   2 - filter(SYSDATE@!-10<=SYSDATE@!)

                                   3 - filter(("TH_UPDATE_TIMESTAMP">=SYSDATE@!-10 AND "TH_UPDATE_TIMESTAMP"<=SYSDATE@!))

                                   4 - access("TH_MERCHANT_ID"='MOB001')

                                 

                                 

                                 

                                 

                                24 rows selected.

                                • 13. Re: oracle not choosing the best exec plan
                                  Jonathan Lewis

                                  Congratulations for having the patience to go through all that work - very few people who ask for help on this forum are that conscientious.

                                  One point that I should have made clear when I asked for the high value was that I meant user_tab_cols.high_val, not the highest value in the table. However I think you've identified the relevant bug and since I've now worked out what Oracle is doing wrong it's not important.

                                   

                                  If you're interested it works like this:

                                  The selectivity of a "column between X and Y" is (approximately):  {range you want} / {high_value - low_value for column}

                                  With some of your actual date values this turns into:  (30th June 2015 - 1st June 2015) / (3rd July 2015 - 6th Sept 2012) = 29/1030 = 0.028

                                   

                                  When you create the descending index Oracle creates a virtual column that is based on the "one's complement" of the raw stored value with an 0xff tagged on the end.

                                  The optimizer then uses the statistics it has gathered on this column to calculate selectivity - and doesn't recognise the column as anything like a date column; it uses the basic arithmetic strategy that it uses for character (or raw) values, looking at the first few bytes, converting to decimal, rounding to 15 sf. and then doing numeric arithmetic.  (It's the same algorithm that I've described for character histograms here: https://jonathanlewis.wordpress.com/2014/05/05/endpoint_value/    )

                                   

                                  Using (roughly) your dates, which I've modelled we get:

                                   

                                  6th Sept 2012 ->  703,819,340,111,320,000,000,000,000,000,000,000

                                  3rd July 2015 ->  703,880,027,955,346,000,000,000,000,000,000,000

                                       Difference = 60,687,844,026

                                   

                                  1st June 2015 -> 703,819,419,969,389,000,000,000,000,000,000,000

                                  30th June 2015 -> 703,819,411,001,549,000,000,000,000,000,000,000

                                       Difference = 8,967,840

                                   

                                  Selectivity = 8,967,840 / 60,687,844,026 = 0.000148  ... which is off by a factor of about 200.

                                   

                                  There's not a lot you can do about this except review every case where a problem arrives. The only suggestion an make is to review the need for the index with the descending column. On average you've got two rows per timestamp - where is the special benefit you get from the two-column index with one column descending ?  (If there's a good answer to that it would be worth asking whether an index defined as ((TH_UPDATE_TIMESTAMP, TH_TXN_CODE desc) could do the same job, allowing you to drop index 17 and add the extra column to index 11.

                                   

                                   

                                  UPDATE:  I forgot to mention - the bug report says it's fixed in 12.1, but my model shows exactly the same problem in 12.1.0.2, even when dynamic sampling has taken place, but is self-correcting on the second execution because "statistics feedback" (previously cardinality feedback) takes over.

                                   

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: oracle not choosing the best exec plan
                                    user12057600

                                    Thank you so much for guiding me towards the right direction and provided  extra solution to circumvent the bug.I did learn a lot from you  -- the hall of frame master.

                                     

                                    I understand that implementing optimizer is no easy task, the good points are  Oracle has a bug database and a platform like this for sharing information, and especially some experts like you.

                                     

                                    There are really some baselines that must be followed to become an appropriate Oracle DBA.

                                     

                                    Best Rgs,

                                    Reeds