Forum Stats

  • 3,874,659 Users
  • 2,266,761 Discussions
  • 7,911,932 Comments

Discussions

understand cost in explain plan

spur230
spur230 Member Posts: 399
edited Nov 8, 2009 10:59AM in General Database Discussions
Hello Experts,

To my knowledge cost are cumulative of it child step. If so what is cost of ID 1 only 3 when its child (ID 2) is 11. Why isn't cost of ID1 sum of ID1 and ID2 which is 11+3?

Also the select statement ( ID 0) is only 11, why it is not 11+3? Please help me clarify?

Thanks is advance.
                                                                                                    
------------------------------------------------------------------------------------
| Id  | Operation                       |  Name            | Rows  | Bytes | Cost  |      
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |     1 |    90 |    11 |      
|*  1 |  TABLE ACCESS BY INDEX ROWID    | CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
|   2 |   NESTED LOOPS                  |                  |     1 |    90 |    11 |      
|   3 |    NESTED LOOPS SEMI            |                  |     1 |    59 |     8 |      
|   4 |     NESTED LOOPS                |                  |     1 |    43 |     6 |      
|*  5 |      TABLE ACCESS BY INDEX ROWID| CLAIM_HEADER     |     1 |    12 |     3 |      
|*  6 |       INDEX UNIQUE SCAN         | XPKCLAIM_HEADER  |     1 |       |     2 |      
|*  7 |      TABLE ACCESS BY INDEX ROWID| CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
|*  8 |       INDEX RANGE SCAN          | TUNE_CAS_01      |     4 |       |     2 |      
|*  9 |     TABLE ACCESS BY INDEX ROWID | CLM_LN_X_FUND_D  |     1 |    16 |     2 |      
|* 10 |      INDEX RANGE SCAN           | XIF3CLM_LN_X_FU  |     1 |       |     1 |      
|  11 |    INLIST ITERATOR              |                  |       |       |       |      
|* 12 |     INDEX RANGE SCAN            | XAK1CLM_HDR_CLM  |     1 |       |     2 |      
------------------------------------------------------------------------------------      

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    Answer ✓
    spur230 wrote:

    In the documentation link given by SP92072 it says: "*Cost is not determined for table access operations.*" However, in the explain plan, table access operators such as TABLE ACCESS BY INDEX ROWID is increasing the cost.

    Isn't TABLE ACCESS BY INDEX ROWID a table acces operation?
    What does the statement in bold actually mean , I seem to be missing something here?
    I've spent a little while trying to imagine what point the author was trying to make when they said this - but I can't make sense of it. Sometimes accidents happen when the manuals are being written, and odd sentences that are obviously wrong (or cut and pasted into the wrong place) get into them.

    You'll notice that the INLIST ITERATOR line in your plan had no cost, and if you look at plans that include BITMAP INDEX operations they show no cost - perhaps the author was thinking about some case of that type, but that seems a fairly unconvincing guess. Call it a documentation bug, until further notice.


    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
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                

Answers

  • 657203
    657203 Member Posts: 1,116
    Cost is the estimated time by CBO that it will take to execute appropriate step.
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    Too bad for all concerned you decided we did not need to know Oracle version to 4 decimal places
    or EXACTLY how the post details were obtained.

    EXPLAIN_PLAN are very much version dependent.

    from URL below
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/ex_plan.htm#i16344
    COST
    	
    
    NUMERIC
    	
    
    Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns.
    sb92075
  • spur230
    spur230 Member Posts: 399
    edited Nov 7, 2009 12:08PM
    Thanks for you prompt responses.

    Just to clarify , for my example above is the total cost for the select statement 11 or sum of all individual steps?

    My Oracle version is Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production

    Also the documentation says " Cost is not determined for table access operations" but why would my
    has cost of 3 although it is TABLE ACCESS BY INDEX ROWID .
  • Hello,

    In your case the total cost is 11.

    The cost is in relationship with disk I/O,...,... not only "time". This is the reason why some intermediate cost is already 11.

    You can find explanations on the following link:

    --> http://www.billmagee.co.uk/oracle/sqltune/060_explain.html

    Best regards,
    Jean-Valentin Lubiez
    Lubiez Jean-Valentin
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Nov 7, 2009 12:41PM
    spur230 wrote:
    To my knowledge cost are cumulative of it child step. If so what is cost of ID 1 only 3 when its child (ID 2) is 11. Why isn't cost of ID1 sum of ID1 and ID2 which is 11+3?

    Also the select statement ( ID 0) is only 11, why it is not 11+3? Please help me clarify?
                                                                                                        
    ------------------------------------------------------------------------------------
    | Id  | Operation                       |  Name            | Rows  | Bytes | Cost  |      
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                  |     1 |    90 |    11 |      
    |*  1 |  TABLE ACCESS BY INDEX ROWID    | CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
    |   2 |   NESTED LOOPS                  |                  |     1 |    90 |    11 |      
    |   3 |    NESTED LOOPS SEMI            |                  |     1 |    59 |     8 |      
    |   4 |     NESTED LOOPS                |                  |     1 |    43 |     6 |      
    |*  5 |      TABLE ACCESS BY INDEX ROWID| CLAIM_HEADER     |     1 |    12 |     3 |      
    |*  6 |       INDEX UNIQUE SCAN         | XPKCLAIM_HEADER  |     1 |       |     2 |      
    |*  7 |      TABLE ACCESS BY INDEX ROWID| CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
    |*  8 |       INDEX RANGE SCAN          | TUNE_CAS_01      |     4 |       |     2 |      
    |*  9 |     TABLE ACCESS BY INDEX ROWID | CLM_LN_X_FUND_D  |     1 |    16 |     2 |      
    |* 10 |      INDEX RANGE SCAN           | XIF3CLM_LN_X_FU  |     1 |       |     1 |      
    |  11 |    INLIST ITERATOR              |                  |       |       |       |      
    |* 12 |     INDEX RANGE SCAN            | XAK1CLM_HDR_CLM  |     1 |       |     2 |      
    ------------------------------------------------------------------------------------      
    What you're seeing is the effect of Oracle showing "prefetching" for a nested loop; however, although the shape of the plan has been changed to display this mechanism (a) it may not be implemented at run time and (b) the way Oracle shows the costs of the plan has not been brought in line with the shape of the plan.

    If you had the 8i version of the plan, the table access at lline 1 would be just above line 12, and the costing would make more sense - viz:
                                                                                                        
    ------------------------------------------------------------------------------------
    | Id  | Operation                       |  Name            | Rows  | Bytes | Cost  |      
    ------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                |                  |     1 |    90 |    11 |      
    |   2 |  NESTED LOOPS                   |                  |     1 |    90 |    11 |      
    |   3 |   NESTED LOOPS SEMI             |                  |     1 |    59 |     8 |      
    |   4 |    NESTED LOOPS                 |                  |     1 |    43 |     6 |      
    |*  5 |     TABLE ACCESS BY INDEX ROWID | CLAIM_HEADER     |     1 |    12 |     3 |      
    |*  6 |      INDEX UNIQUE SCAN          | XPKCLAIM_HEADER  |     1 |       |     2 |      
    |*  7 |     TABLE ACCESS BY INDEX ROWID | CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
    |*  8 |      INDEX RANGE SCAN           | TUNE_CAS_01      |     4 |       |     2 |      
    |*  9 |    TABLE ACCESS BY INDEX ROWID  | CLM_LN_X_FUND_D  |     1 |    16 |     2 |      
    |* 10 |     INDEX RANGE SCAN            | XIF3CLM_LN_X_FU  |     1 |       |     1 |      
    |  11 |   INLIST ITERATOR               |                  |       |       |       |      
    |*  1 |    TABLE ACCESS BY INDEX ROWID  | CLM_HDR_CLM_LN_  |     1 |    31 |     3 |      
    |* 12 |     INDEX RANGE SCAN            | XAK1CLM_HDR_CLM  |     1 |       |     2 |      
    ------------------------------------------------------------------------------------      
    With this layout, you can see that the INLIST ITERATOR is simply not reporting a value, but it would be reporting the value 3 from the line below it (labelled line 1). Then the cost of 11 in line 2 is the cost of 8 in line 3 plus 1 (rows in line 3) times the cost (3) of line 11.


    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
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • spur230
    spur230 Member Posts: 399
    Wow Thanks for the response, it is starting to make perfect sense now but I have just one more question.

    In the documentation link given by SP92072 it says: "*Cost is not determined for table access operations.*" However, in the explain plan, table access operators such as TABLE ACCESS BY INDEX ROWID is increasing the cost.

    Isn't TABLE ACCESS BY INDEX ROWID a table acces operation?
    What does the statement in bold actually mean , I seem to be missing something here?
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    Answer ✓
    spur230 wrote:

    In the documentation link given by SP92072 it says: "*Cost is not determined for table access operations.*" However, in the explain plan, table access operators such as TABLE ACCESS BY INDEX ROWID is increasing the cost.

    Isn't TABLE ACCESS BY INDEX ROWID a table acces operation?
    What does the statement in bold actually mean , I seem to be missing something here?
    I've spent a little while trying to imagine what point the author was trying to make when they said this - but I can't make sense of it. Sometimes accidents happen when the manuals are being written, and odd sentences that are obviously wrong (or cut and pasted into the wrong place) get into them.

    You'll notice that the INLIST ITERATOR line in your plan had no cost, and if you look at plans that include BITMAP INDEX operations they show no cost - perhaps the author was thinking about some case of that type, but that seems a fairly unconvincing guess. Call it a documentation bug, until further notice.


    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
    .
    
    "Science is more than a body of knowledge; it is a way of thinking" 
    Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • spur230
    spur230 Member Posts: 399
    Thank you Jonathan for your reply.
This discussion has been closed.