1 2 Previous Next 19 Replies Latest reply: May 6, 2010 11:19 PM by amardeep.sidhu RSS

    Oracle Selects a Plan with Higher Cost?

    thtsang
      I have the following table with > 870000 rows
      SQL> desc t
       Name                                      Null?    Type
       ----------------------------------------- -------- ----------------------------
       DATE1                                              DATE
       STRING1                                            VARCHAR2(40)
       NUM1                                               NUMBER
       NUM2                                               NUMBER
       NUM3                                               NUMBER
       NUM4                                               NUMBER
       NUM5                                               NUMBER
       STRING2                                            VARCHAR2(3)
       NUM6                                               NUMBER
       STRING3                                            VARCHAR2(240)
       STRING4                                            VARCHAR2(240)
       STRING5                                            VARCHAR2(240)
       STRING6                                            VARCHAR2(240)
       STRING7                                            VARCHAR2(240)
       STRING8                                            VARCHAR2(240)
       STRING9                                            VARCHAR2(10)
      Create 2 indices
      SQL> CREATE INDEX t1 ON t (TRUNC("DATE1"), "NUM3");
      
      Index created.
      
      SQL> CREATE INDEX t2 ON t ("NUM3", "NUM1", TRUNC("DATE1")) ;
      
      Index created.
      Analyze everything
      SQL> analyze index t1 compute statistics;
      
      Index analyzed.
      
      SQL> analyze index t2 compute statistics;
      
      Index analyzed.
      
      SQL> analyze table t compute statistics;
      
      Table analyzed.
      Oracle decides to use T2 to run the query displayed in 'execution plan' statement below
        1  explain plan for
        2  SELECT
        3  NVL(SUM(num6),0) num6_SUM
        4  FROM t
        5  WHERE 1 = 1  AND num3 = :b1
        6  AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
        7* AND STRING9 = :b4
      SQL> /
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
      Plan hash value: 1806095077
      
      --------------------------------------------------------------------------------------
      | Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      
      PLAN_TABLE_OUTPUT
      -------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |      |     1 |    15 |   316   (4)| 00:00:04 |
      |   1 |  SORT AGGREGATE               |      |     1 |    15 |            |          |
      |*  2 |   FILTER                      |      |       |       |            |          |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| T    |     2 |    30 |   316   (4)| 00:00:04 |
      |*  4 |     INDEX RANGE SCAN          | T2   |   356 |       |   270   (5)| 00:00:04 |
      --------------------------------------------------------------------------------------
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
         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))
      But in fact, Oracle thinks the cost of using T1 is lower (this is also what I think)
        1  explain plan for
        2  SELECT /*+ index(t t1) */
        3  NVL(SUM(num6),0) num6_SUM
        4  FROM t
        5  WHERE 1 = 1  AND num3 = :b1
        6  AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3
        7* AND STRING9 = :b4
        8  /
      
      Explained.
      
      SQL> select * from table(dbms_xplan.display);
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------
      Plan hash value: 1945175582
      --------------------------------------------------------------------------------------
      | Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT              |      |     1 |    15 |    61   (0)| 00:00:01 |
      |   1 |  SORT AGGREGATE               |      |     1 |    15 |            |          |
      |*  2 |   FILTER                      |      |       |       |            |          |
      |*  3 |    TABLE ACCESS BY INDEX ROWID| T    |     2 |    30 |    61   (0)| 00:00:01 |
      |*  4 |     INDEX RANGE SCAN          | T1   |   356 |       |    14   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------
      
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
      
      PLAN_TABLE_OUTPUT
      --------------------------------------------------------------------------------
         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"))<=SYSDATE@!-TO_NUMBER(:B3))
             filter("NUM3"=TO_NUMBER(:B1))
      Any idea why Oracle would use a plan which it actually thinks more expensive?

      Also, I know that EXPLAIN PLAN has some assumption on bind variables. Therefore, I've also surrounded the dates with cast(xxx as date), even I think it can recognize sysdate as a date already. This has no effect on the plan and cost estimation.


      Oracle: 10.2.0.4, 9.2.0.6
      OS: RHEL 3
        • 1. Re: Oracle Selects a Plan with Higher Cost?
          Pavan Kumar
          Hi,

          AFAIK, when you are going for truncate Option upon date column, the index will not be utilized (as you have function base index, perhaps composite one), so it went for column "num3", which utilized the "index t2". which is might be less cost effective when compared to FTS.

          As you went for the force index range scan with "index t1", as it resulted with explain might be less cost, but from oracle optimizer perspective, the first query disable to utilize the 'index t1", make an appropriate feasible index on date column with out any function based and try out and check how oracle thinks.

          Kindly, post across the stats details, and use dbms_stats that's much better.
          What is the value of db_multiblock_read_count value Optimer_cost_adj parameter values.

          - Pavan Kumar N
          - ORACLE OCP - 9i/10g
          https://www.oracleinternals.blogspot.com
          • 2. Re: Oracle Selects a Plan with Higher Cost?
            ajallen
            When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.
            • 3. Re: Oracle Selects a Plan with Higher Cost?
              thtsang
              Pavan Kumar wrote:
              AFAIK, when you are going for truncate Option upon date column, the index will not be utilized (as you have function base index, perhaps composite one), so it went for column "num3", which utilized the "index t2". which is might be less cost effective when compared to FTS.
              I don't think Oracle can't utilize the FBI, otherwise I can't get the 2nd execution plan.
              Kindly, post across the stats details, and use dbms_stats that's much better.
              What is the value of db_multiblock_read_count value Optimer_cost_adj parameter values.
              Here it is:
                1  begin
                2  dbms_stats.gather_index_stats(ownname=> 'BG', indname => 'T1');
                3  dbms_stats.gather_index_stats(ownname=> 'BG', indname => 'T2');
                4  dbms_stats.gather_table_stats(ownname=> 'BG', tabname => 'T');
                5* end;
              SQL> /
              
              PL/SQL procedure successfully completed.
              
              declare
                 v_numrows     NUMBER; 
                 v_numblks     NUMBER;
                 v_avgrlen     NUMBER;
                 v_cachedblk   NUMBER;
                 v_cachehit    NUMBER;
                 v_numdist    NUMBER; 
                 v_avglblk    NUMBER;
                 v_avgdblk    NUMBER; 
                 v_clstfct    NUMBER;
                 v_indlevel   NUMBER;
                 v_guessq     NUMBER;
              begin
              DBMS_STATS.GET_TABLE_STATS (
                 ownname         => 'BG',
                 tabname         => 'T',
                 numrows     => v_numrows, 
                 numblks     => v_numblks,     
                 avgrlen     => v_avgrlen,     
                 cachedblk   => v_cachedblk,   
                 cachehit    => v_cachehit);
              dbms_output.put_line('T: numrows: '|| v_numrows );
              ...
              
              DBMS_STATS.GET_INDEX_STATS (
                 ownname     =>     'BG', 
                 indname     =>     'T1',
                 numrows     => v_numrows, 
                 numlblks    => v_numblks,
                 numdist     => v_numdist, 
                 avglblk     => v_avglblk,
                 avgdblk     => v_avgdblk, 
                 clstfct     => v_clstfct,
                 indlevel    => v_indlevel,
                 guessq      => v_guessq ,
                 cachedblk   => v_cachedblk,   
                 cachehit    => v_cachehit);
              
              dbms_output.put_line('T1: numrows: '|| v_numrows ); 
              ...
              
              DBMS_STATS.GET_INDEX_STATS (
                 ownname     =>     'BG', 
                 indname     =>     'T2',
              ...);
              
              dbms_output.put_line('T2: numrows: '|| v_numrows ); 
              ...
              
              end;
              
              T: numrows: 790679
              T: numblks: 5463
              T: avgrlen: 43
              T: cachedblk:
              T: cachehit:
              T1: numrows: 790679
              T1: numlblks: 2456
              T1: numdist: 7998
              T1: avglblk: 1
              T1: avgdblk: 12
              T1: clstfct: 102682
              T1: indlevel: 2
              T1: guessq:
              T1: cachedblk:
              T1: cachehit:
              T2: numrows: 790679
              T2: numlblks: 2565
              T2: numdist: 7998
              T2: avglblk: 1
              T2: avgdblk: 12
              T2: clstfct: 102147
              T2: indlevel: 2
              T2: guessq:
              T2: cachedblk:
              T2: cachehit:
              
              PL/SQL procedure successfully completed.
              
              SQL> show parameter db_file_multiblock_read_count
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              db_file_multiblock_read_count        integer     8
              SQL> show parameter optimizer_index_cost_adj
              
              NAME                                 TYPE        VALUE
              ------------------------------------ ----------- ------------------------------
              optimizer_index_cost_adj             integer     100
              After all these, the cost for both plan increased by around 20%. But the plan with T1 is still significantly less costly and rejected if there's no hint.
              ajallen wrote:
              When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.
              I've never heard of this. But this should explain the mystery if Oracle works this way. Could you please provide some reference?

              Actually, I hinted the query to use t2, the plan & cost estimation are the same as when no hints are applied.

              Edited by: thtsang on May 3, 2010 6:42 PM
              • 4. Re: Oracle Selects a Plan with Higher Cost?
                Jonathan Lewis
                ajallen wrote:
                When you hinted T1, you actually lowered the cost of using that index. That is how hints work, they artificially lower the cost of that path so that Oracle will tend to favor it. That is why hints sometimes seem to be ignored, they do not lower the cost enough to make the path the lowest cost. So, when you lowered the cost of using T1, Oracle chose the plan using T1.
                Where did you get this idea ? It's wrong and needs to be corrected.
                Index hints control which indexes Oracle should consider at any point in the calculation, they do not change the arithmetic that the optimizer does.

                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+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                • 5. Re: Oracle Selects a Plan with Higher Cost?
                  Jonathan Lewis
                  The interesting oddity here is that the expressoin that appears in the ACCESS_PREDICATES -- TRUNC(INTERNAL_FUNCTION("DATE1")) -- does not match the index definition that you used to create the index. Can you show us the contents for user_ind_expressions for this table to see how your original definition looks in the data dictionary.

                  Before spending too much time chasing the details from explain plan, you ought to run the two queries and use dbns_xplan.display_cursor() to check what really happens, just in case the error is somewhere in explain plan or the presentation of results from explain plan.

                  One quick thought before you do that though - a common reason for a more expensive plan being taken is that the optimizer_mode has been set to first_rows_N, and there are various complications in the output when a plan which is more expensive as a way of getting ALL the data is actually the best plan for getting the first N rows. What's your optimizer_mode ?

                  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+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                  • 6. Re: Oracle Selects a Plan with Higher Cost?
                    user12068799
                    Hi,

                    You should collect statistics properly.
                    • 7. Re: Oracle Selects a Plan with Higher Cost?
                      thtsang
                      Jonathan, user12068799,
                      Thanks for all your input, combining the your suggestions, here is what I get:

                      1) Collecting hidden column statistics or not does not affect explain plan result (still using T2)
                      2) Without hidden column statistics, Oracle will use T2 when running the query. Using hint to force it using T1 showed that the cost of using T1 is lower than T2
                      3) With hidden column statistics, Oracle will use T1 automatically when running the query.

                      So it seems Oracle will use the correct FBI as long as hidden column statistics are available. However, EXPLAIN PLAN does not show the actual plan. (But I think EXPLAIN PLAN is different from actually running the SQL as bind variable peeking is in effect).
                      • 8. Re: Oracle Selects a Plan with Higher Cost?
                        Pavan Kumar
                        Hi jonathan,

                        Correct whether my understanding with respect to oracle flow.

                        If you check the query where clause AND TRUNC(date1) BETWEEN sysdate-:b2 AND sysdate-:b3, as he force out the truncate optionm where date column is truncated and further tried to operate a range scan upon the default system date time. When comes to considering an index into preference, the Optimzier not took the preference of respective index t1 which stated since part of the data (Hour mini sec) getting truncated (00 00 00). Leading Column index might not have taken the preference.

                        As the other Options would be the num3 column which taken the preference when compared to FTS, as I believe with respect to stats what user (OP) stated. That could be the reason where ORACLE Optimizer went for internal function to convert and compare the sysdate time of DB server.

                        Request to provide some inputs from your end sir and correct me incase.

                        - Pavan Kumar N
                        - ORACLE OCP - 9i/10g
                        https://www.oracleinternals.blogspot.com
                        • 9. Re: Oracle Selects a Plan with Higher Cost?
                          ajallen
                          >
                          Jonathan Lewis wrote...
                          Where did you get this idea ? It's wrong and needs to be corrected.
                          Index hints control which indexes Oracle should consider at any point in the calculation, they do not change the arithmetic that the optimizer does.
                          >
                          From
                          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:765050072151
                          Which is exactly on point to the question of this thread.
                          >
                          ...
                          Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods
                          ...
                          >
                          Also from an Oracle DBA class years ago. The instructor took a lot of time to explain that is how hints work, essentially the same thing as Tom wrote (and is why they are called hints and not directives).
                          • 10. Re: Oracle Selects a Plan with Higher Cost?
                            Jonathan Lewis
                            ajallen wrote:
                            >
                            From
                            http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:765050072151
                            Which is exactly on point to the question of this thread.
                            >
                            ...
                            Hints will affect the costs associated with steps - this is how hints work. An INDEX hint will artificially lower the cost of using that index and artificially inflate the costs of other access methods
                            ...
                            >
                            Thanks for the reference, I've added a note to that thread to point out that the statement is wrong.
                            Here's an example to demonstrate the error (running 10.2.0.3, 8KB block size, freelist management, 1MB extents):
                            drop table t1;
                            
                            create table t1
                            as
                            select 
                                 mod(rownum,200)          n1,
                                 mod(rownum,200)          n2,
                                 rpad(rownum,80)          v1
                            from all_objects 
                            where rownum <= 5000
                            ;
                            
                            create index t1_i1 on t1(n1);
                            
                            begin
                                 dbms_stats.gather_table_stats(
                                      user,
                                      't1',
                                      cascade => true,
                                      method_opt => 'for all columns size 1'
                                 );
                            end;
                            /
                            
                            set autotrace traceonly explain
                            
                            select n2 from t1 where n1 = 31;
                            
                            select /*+ index(t1) */ n2 from t1 where n1 = 31;
                            
                            set autotrace off
                            
                            alter session set events '10053 trace name context forever';
                            
                            select n2 from t1 where n1 = 32;
                            
                            select /*+ index(t1) */ n2 from t1 where n1 = 32;
                            
                            alter session set events '10053 trace name context off';
                            The execution plans from autotrace are as follows:
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 3617692013
                            
                            --------------------------------------------------------------------------
                            | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT  |      |    25 |   200 |    19   (0)| 00:00:01 |
                            |*  1 |  TABLE ACCESS FULL| T1   |    25 |   200 |    19   (0)| 00:00:01 |
                            --------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               1 - filter("N1"=31)
                            
                            
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 1429545322
                            
                            -------------------------------------------------------------------------------------
                            | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
                            -------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT            |       |    25 |   200 |    26   (0)| 00:00:01 |
                            |   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    25 |   200 |    26   (0)| 00:00:01 |
                            |*  2 |   INDEX RANGE SCAN          | T1_I1 |    25 |       |     1   (0)| 00:00:01 |
                            -------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                               2 - access("N1"=31)
                            So the hint has cause a change in execution plan.
                            The relevant sections from the 10053 trace files are these:

                            Unhinted - note that the optimizer calculates the cost of the tablescan and the index range scan and finds the tablescan to be cheaper.
                            SINGLE TABLE ACCESS PATH
                              Column (#1): N1(NUMBER)
                                AvgLen: 4.00 NDV: 200 Nulls: 0 Density: 0.005 Min: 0 Max: 199
                              Table: T1  Alias: T1     
                                Card: Original: 5000  Rounded: 25  Computed: 25.00  Non Adjusted: 25.00
                              Access Path: TableScan
                                Cost:  19.09  Resp: 19.09  Degree: 0
                                  Cost_io: 19.00  Cost_cpu: 1463394
                                  Resp_io: 19.00  Resp_cpu: 1463394
                              Access Path: index (AllEqRange)
                                Index: T1_I1
                                resc_io: 26.00  resc_cpu: 195257
                                ix_sel: 0.005  ix_sel_with_filters: 0.005
                                Cost: 26.01  Resp: 26.01  Degree: 1
                              Best:: AccessPath: TableScan
                                     Cost: 19.09  Degree: 1  Resp: 19.09  Card: 25.00  Bytes: 0
                            Hinted - note that the optimizer has NOT calculated the cost of the tablescan because it has been told it MUST use an index (if a legal index exists) - an "Oracle hint" is not an "English language hint", it is an order that must be obeyed if legal and correct. There is a legal index, and the cost of using that index is the same as it was in the unhinted code.
                            SINGLE TABLE ACCESS PATH
                              Column (#1): N1(NUMBER)
                                AvgLen: 4.00 NDV: 200 Nulls: 0 Density: 0.005 Min: 0 Max: 199
                              Table: T1  Alias: T1     
                                Card: Original: 5000  Rounded: 25  Computed: 25.00  Non Adjusted: 25.00
                              Access Path: index (AllEqRange)
                                Index: T1_I1
                                resc_io: 26.00  resc_cpu: 195257
                                ix_sel: 0.005  ix_sel_with_filters: 0.005
                                Cost: 26.01  Resp: 26.01  Degree: 1
                              Best:: AccessPath: IndexRange  Index: T1_I1
                                     Cost: 26.01  Degree: 1  Resp: 26.01  Card: 25.00  Bytes: 0
                            ***************************************
                            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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                            • 11. Re: Oracle Selects a Plan with Higher Cost?
                              Hemant K Chitale
                              I've added a note to that thread to point out that the statement is wrong.
                              Uh Oh ! (And I agree with you).

                              [What is a Hint ?|http://jonathanlewis.wordpress.com/2006/11/11/what-is-a-hint/]

                              [Hints Again|http://jonathanlewis.wordpress.com/2007/06/17/hints-again/]

                              "+The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen+" (from "Hints Again") might still be causing confusion ?. "scale factor" sounds like "lower the cost".


                              [Hints on Hints|http://jonathanlewis.wordpress.com/2009/05/09/hints-on-hints/]

                              was quite useful as well.

                              Thanks !

                              Hemant K Chitale
                              • 12. Re: Oracle Selects a Plan with Higher Cost?
                                Jonathan Lewis
                                Hemant K Chitale wrote:
                                I've added a note to that thread to point out that the statement is wrong.
                                Uh Oh ! (And I agree with you).

                                [What is a Hint ?|http://jonathanlewis.wordpress.com/2006/11/11/what-is-a-hint/]
                                [Hints Again|http://jonathanlewis.wordpress.com/2007/06/17/hints-again/]

                                "+The parallel() hint does not tell Oracle to run a query in parallel, it tells the optimizer to use a certain scale factor in certain areas of its calculation. But if a path is still too expensive, even after scaling, that path will not be chosen+" (from "Hints Again") might still be causing confusion ?. "scale factor" sounds like "lower the cost".
                                I don't see why you think that the quote is worth an "Uh Oh!" - the fact that the parallel() hint is supposed to change the cost of a tablescan doesn't mean that an index hint lowers the cost of using an index. (Although you might want to say that the parallel_index() hint is an index hint that make may the optimizer to change the cost of using an index).

                                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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                                • 13. Re: Oracle Selects a Plan with Higher Cost?
                                  ajallen
                                  I appreciate you taking the time to prove-up. I learned something new - or unlearned something old - today. It seems that the earlier we lean things, the harder they are to unlearn.

                                  Thanks.
                                  • 14. Re: Oracle Selects a Plan with Higher Cost?
                                    Hemant K Chitale
                                    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".


                                    Hemant K Chitale
                                    1 2 Previous Next