1 2 Previous Next 19 Replies Latest reply: May 6, 2010 11:19 PM by amardeep.sidhu Go to original post RSS
      • 15. Re: Oracle Selects a Plan with Higher Cost?
        thtsang
        Do you mean that Oracle does not like to use the index compare trunc(date1) with sysdate-:b2 (or b3) because it is truncated? This seems unlikely because trunc(date1) is still a date, it just happens that the hour/min/sec are all 0. Oracle should not handle it specially.

        My conclusion: it seems (at least in some cases) Oracle dislike using a FBI if no hidden column stats are available, even the optimizer thinks the FBI is less costly. EXPLAIN PLAN reflected this and therefore displayed a more expensive plan.
        • 16. Re: Oracle Selects a Plan with Higher Cost?
          Pavan Kumar
          Hi,
          My conclusion: it seems (at least in some cases) Oracle dislike using a FBI if no hidden column stats are available, even the optimizer thinks the FBI is less costly. EXPLAIN PLAN reflected this and therefore displayed a more expensive plan.
          Thanks for posting across your inputs, that would help out to understand in better way. Their would be cases where Optimizer might not consider the things when compared to Leading column indexes or say indexes (atleast). Yes column stats profound the Optimizer in muich better when compared to the unavailablility of stats. Perhaps, I thought why Optimizer might not went up for dynamic sampling with respect to first execution plan.

          - Pavan Kumar N
          - ORACLE OCP - 9i/10g
          https://www.oracleinternals.blogspot.com
          • 17. Re: Oracle Selects a Plan with Higher Cost?
            Jonathan Lewis
            Hemant K Chitale wrote:
            Jonathan,

            My "Uh oh !" was in response to your line
            "I've added a note to that thread to point out that the statement is wrong."
            --- meaning "Uh oh ! , Jonathan Lewis has pointed out to Tom Kyte that his statement is wrong".
            I understand, thanks for the clarification.
            Still, there's no cause for concern - he'll either create a demonstration of why I'm wrong or he'll agree with me; he's not going to come out round here foaming at the mouth and screaming abuse.

            Regards
            Jonathan Lewis
            http://jonathanlewis.wordpress.com
            http://www.jlcomp.demon.co.uk

            To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
            {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
            fixed format
            .
            
            There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
            
            "Science is more than a body of knowledge; it is a way of thinking" 
            Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
            • 18. Re: Oracle Selects a Plan with Higher Cost?
              Jonathan Lewis
              thtsang wrote:
              My conclusion: it seems (at least in some cases) Oracle dislike using a FBI if no hidden column stats are available, even the optimizer thinks the FBI is less costly. EXPLAIN PLAN reflected this and therefore displayed a more expensive plan.
              I was sufficiently curious that I built a model to reproduce your result. I think you may have discovered a bug (although there's a chance that it's deliberate), but it's not do to with hidden stats or anything like that. My example goes wrong even after calling dbms_stats to compute all stats on the table and its indexes.

              Here's the bit of SQL I used in the test:
              prompt     ===========================
              prompt     Uses index t1_i2 by default
              prompt     ===========================
              
              explain plan for
              SELECT
                   NVL(SUM(num6),0) num6_SUM
              FROM t1
              WHERE 
                   1 = 1  
              and     num3 = :b1
              AND     TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
              and     string9 = :b4
              ;
              
              select * from table(dbms_xplan.display)
              ;
              
              
              prompt     =====================
              prompt     But t1_i1 was cheaper
              prompt     =====================
              
              explain plan for
              SELECT
                   /*+ index(t1 t1_i1) */
                   NVL(SUM(num6),0) num6_SUM
              FROM t1
              WHERE 
                   1 = 1  
              AND     num3 = :b1
              AND     TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
              and     string9 = :b4
              ;
              
              select * from table(dbms_xplan.display)
              ;
              And the two execution plans:
              ===========================
              Uses index t1_i2 by default
              ===========================
              
              Explained.
              
              
              PLAN_TABLE_OUTPUT
              ------------------------------------------------------------------------------------------------------------------------
              Plan hash value: 1130895096
              
              -----------------------------------------------------------------------
              | Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
              -----------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |       |     1 |    21 |    33 |
              |   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
              |*  2 |   FILTER                      |       |       |       |       |
              |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    33 |
              |*  4 |     INDEX RANGE SCAN          | T1_I2 |    18 |       |    15 |
              -----------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 2 - filter(SYSDATE@!-TO_NUMBER(:B2)<=SYSDATE@!-TO_NUMBER(:B3))
                 3 - filter("STRING9"=:B4)
                 4 - access("NUM3"=TO_NUMBER(:B1) AND
                            TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2) AND
                            TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDATE@!-TO_NUMBER(:B3))
                     filter(TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2
                            ) AND TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDATE@!-TO_NUMBER(:B3))
              
              Note
              -----
                 - cpu costing is off (consider enabling it)
              
              26 rows selected.
              
              =====================
              But t1_i1 was cheaper
              =====================
              
              Explained.
              
              
              PLAN_TABLE_OUTPUT
              ------------------------------------------------------------------------------------------------------------------------
              Plan hash value: 4191978214
              
              -----------------------------------------------------------------------
              | Id  | Operation                     | Name  | Rows  | Bytes | Cost  |
              -----------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |       |     1 |    21 |    20 |
              |   1 |  SORT AGGREGATE               |       |     1 |    21 |       |
              |*  2 |   FILTER                      |       |       |       |       |
              |*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    21 |    20 |
              |*  4 |     INDEX RANGE SCAN          | T1_I1 |    18 |       |     2 |
              -----------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 2 - filter(SYSDATE@!-TO_NUMBER(:B2)<=SYSDATE@!-TO_NUMBER(:B3))
                 3 - filter("STRING9"=:B4)
                 4 - access(TRUNC(INTERNAL_FUNCTION("DATE1"))>=SYSDATE@!-TO_NUMBER(:B2
                            ) AND "NUM3"=TO_NUMBER(:B1) AND TRUNC(INTERNAL_FUNCTION("DATE1"))<=SYSDA
                            TE@!-TO_NUMBER(:B3))
                     filter("NUM3"=TO_NUMBER(:B1))
              
              Note
              -----
                 - cpu costing is off (consider enabling it)
              
              25 rows selected.
              I haven't worked out exactly what's going wrong, but it seems to be related to queries the fact that I have two function-based indexes that could be used, where one of the indexes has the function-based column as the first column and would require an index range scan to take place on that column. In certain circumstances the index that starts with the function-based column will not be used, even though the optimizer calculates that it is the cheaper index.

              To complicate matters, the decision to accept or reject the index seems to be based on the total cost, or possibly cardinality of the query. In my example if I drop the predicate against string9 the optimizer uses the right index, and in fact if I leave the predicate in place the number of distinct values of string9 seems to aaffect whether or not the optimizer chooses the right index.

              I'd raise an SR.



              Regards
              Jonathan Lewis
              http://jonathanlewis.wordpress.com
              http://www.jlcomp.demon.co.uk

              To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
              {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
              fixed format
              .
              
              There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
              
              "Science is more than a body of knowledge; it is a way of thinking" 
              Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
              • 19. Re: Oracle Selects a Plan with Higher Cost?
                amardeep.sidhu
                Just for information.

                Tom has replied to the post made by Jonathan. Quoting the same here:

                Jonathan

                thank you - you are absolutely correct, I mispoke on that.

                I have examples in other places where I use the hint to show what the cost of using the index (or whatever) would be to show that the cost of using that other approach would be more expensive (in direct contradiction to what I wrote here a long time ago)

                appreciate the followup.

                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:765050072151#2625287100346255645

                Regards,
                Amardeep Sidhu

                Added the URL.

                Edited by: Amardeep Sidhu on May 7, 2010 9:49 AM
                1 2 Previous Next