Re: Interpreting cost on EXPLAIN PLAN

<artmt_at_hotmail.com> wrote in message
news:1124221026.905778.9540_at_g47g2000cwa.googlegroups.com...
> My understanding is that the cost value for "SELECT STATEMENT" at the
> top of the plan indicates what the optimizer estimates to be the total
> cost for the entire statement.
>
> If this is true then how do I interpret plans that show low cost for
> "SELECT STATEMENT" and much larger cost for one of the operations?
>
> In the example below "SELECT STATEMENT" cost=15 and "INDEX (RANGE
> SCAN)" cost=72.
> I have also seen much bigger differences.
>
> Thanks
> -Art
>
>
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=111)
> 1 0 VIEW (Cost=15 Card=1 Bytes=111)
> 2 1 WINDOW (SORT) (Cost=15 Card=1 Bytes=106)
> 3 2 TABLE ACCESS (BY INDEX ROWID) OF 'RECEIPT' (Cost=2
> Card=1 Bytes=76)
>
> 4 3 NESTED LOOPS (Cost=13 Card=1 Bytes=106)
> 5 4 VIEW (Cost=12 Card=1 Bytes=30)
> 6 5 SORT (UNIQUE) (Cost=12 Card=1 Bytes=43)
> 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'RECEIPT'
> (Cost=9 Card=1 Bytes=43)
>
> 8 7 INDEX (RANGE SCAN) OF 'RCTH_DT_DISTRIBUTE_I'
> (NON-UNIQUE) (Cost=72 Card=22)
>
> 9 4 INLIST ITERATOR
> 10 9 INDEX (RANGE SCAN) OF 'PK_RECEIPT' (UNIQUE)
> (Cost=3 Card=1)
>

Which version of Oracle, and do you have a list of optimizer related parameters which are not set to their default values.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005