## Forum Stats

• 3,874,659 Users
• 2,266,761 Discussions

Discussions

# understand cost in explain plan

Member Posts: 399
edited Nov 8, 2009 10:59AM
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?

```
------------------------------------------------------------------------------------
| 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 |
------------------------------------------------------------------------------------      ```
Tagged:

• Member Posts: 10,117 Blue Diamond
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ```

• Member Posts: 1,116
Cost is the estimated time by CBO that it will take to execute appropriate step.
• 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
```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.```
• 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 .
• Member Posts: 2,737
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
• 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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    ```
• 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?
• Member Posts: 10,117 Blue Diamond
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                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ```
• Member Posts: 399