Re: Interpreting cost on EXPLAIN PLAN

<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

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.

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