Re: Interpreting cost on EXPLAIN PLAN


Jonathan Lewis wrote:
> <artmt@xxxxxxxxxxx> wrote in message
> news:1124223828.580322.5440@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> > 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

.

Relevant Pages

  • Re: Interpreting cost on EXPLAIN PLAN
    ... >> a book about the cost based optimizer, ... Jonathan Lewis ... Cost Based Oracle - Volume 1 ...
    (comp.databases.oracle.server)
  • Re: Interpreting cost on EXPLAIN PLAN
    ... Jonathan Lewis wrote: ... > The error is in the reporting of the index cost. ... > If you check lots of plans, ... > Now waiting on the publishers: Cost Based Oracle - Volume 1 ...
    (comp.databases.oracle.server)
  • Re: Parameter to influence Oracles Idea of IO Cost?
    ... Does my memory fail me? ... This parameter does not affect the cost of a single table ... Oracle docs are a bit sparse IMHO. ... SYSTEM STATISTICS INFORMATION ...
    (comp.databases.oracle.server)
  • Re: Optimizing union based view
    ...   DUAL ... Now oracle again uses a full table scan for old.t1old. ... using the calculated cost to determine the predicted least expensive ... Predicate Information: ...
    (comp.databases.oracle.server)
  • Re: Parameter to influence Oracles Idea of IO Cost?
    ... Does my memory fail me? ... This parameter does not affect the cost of a single table ... Oracle docs are a bit sparse IMHO. ... So are you suggesting to gather statistics during normal operation? ...
    (comp.databases.oracle.server)