This discussion is archived
1 2 Previous Next 19 Replies Latest reply: May 6, 2010 9:19 PM by amardeep.sidhu RSS

Oracle Selects a Plan with Higher Cost?

thtsang Journeyer
Currently Being Moderated
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?
    PavanKumar Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    You should collect statistics properly.
  • 7. Re: Oracle Selects a Plan with Higher Cost?
    thtsang Journeyer
    Currently Being Moderated
    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?
    PavanKumar Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    >
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points