Re: Interpreting cost on EXPLAIN PLAN

Jonathan Lewis wrote:
> <artmt_at_hotmail.com> wrote in message
> news:1124223828.580322.5440_at_g14g2000cwa.googlegroups.com...
> > Running Oracle 9.2.0
> >
> > The only non-default optimizer parameters are:
> > optimizer_mode CHOOSE
> > optimizer_index_cost_adj 10
> >
> > Thanks.
> >
>
>
> 9.2.0.what ? 9.2.0.1 was particularly buggy,
> so exact version is usually necessary

My apologies.
I will get the exact version when I get back to work tomorrow.

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

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

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.

Thanks
-Art Received on Tue Aug 16 2005 - 18:34:15 CDT