Re: Interpreting cost on EXPLAIN PLAN

<artmt_at_hotmail.com> wrote in message
news:1124235255.707893.33770_at_g44g2000cwa.googlegroups.com...
>> The optimizer_index_cost_adj is probably
>> the most significant thing.
>>
>> You have an index cost reported at 72 with
>> a cardinality of 22 - assume that is 22 separate
>> visits to the table, so the table line should show
>> a cost of 94. But you have oica = 10, so take
>> 10% of 94 and you get 9.4 (and explain plan
>> reports only integer values - giving you 9).
>>
>> There are various oddities, special cases,
>> and rounding/printing errors that appear
>> through execution plans all the time - but
>> setting oica and oicaching leads to much
>> more arithmetical confusion.
>
> So should I interpret the plan as a mistake in estimating the index
> cost or the total cost of the statement?
>
>
> I read your FAQ and found a lot of useful stuff. Thank you for putting
> it together.
>
> Is the draft of your book webbed? I own several books on the subject,
> but none of them are very good.
>

The error is in the reporting of the index cost. If you check lots of plans, you will find that there are several cases where some number is wrong, even though the correct number is actually used elsewhere - even to the extent that the 10053 trace shows one number, but the execution plan prints another.

The draft of the book is not on the web.

I hadn't realised that anyone had written a book about the cost based optimizer,
although Dan Tow has written a very
interesting book about tuning SQL, and
a couple of other people have done some
pretty good stuff about tuning SQL in the past.

-- 
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