Re: When does performance worsen when going from RULE to COST

There is one generic issue I have seen several times: the difference between a production and a development database. Usually the number of records for reference tables is correct in both cases and the number of fact records is significantly higher in production databases. The application has been tuned against the development database and presto: it doesn't work anymore.

Regards,

--
Sybrand Bakker, Oracle DBA
Leonard F. Clark <leonard_at_lf-clark.prestel.co.uk> wrote in message news:389c7503.30438097_at_news.prestel.co.uk...
> My experience is that this is nearly always because of poor stats. If
> you ask many DBAs they'll be quite wary of COST based. On a number of
> occasions I've refreshed stats and the performance has improved
> significantly.
>
> I'm sure there *are* ocassions where the optimizer makes the wrong
> choice - and I guess that's why Oracle bottled out and left you with
> the hints.
>
> Len
>
> >Hi g's,
> >
> >When you want to change a database from rule based to cost based you're
> >naturally are going to be worried somewhat about the general and query
> >specific effects
> >
> >My question: Are there general causes for degradation people stumble upon
> >when doing this change? This of course under the assumption that every
table
> >has been analyzed (ANALYZE TABLE <name> COMPUTE STATISTICS).
> >
> >For example (one I've seen often): the rule based optimizer uses an
index,
> >and the cost based doesn't under the false assumption that a literal in
the
> >WHERE clause is one of the evenly distributed values of a column, where
in
> >fact it is a very rare value (e.g. the literal 'F' for the column GENDER
for
> >a personnel table in a database used by the army). Of course this can be
> >gotten rid of by gathering histogram statistics for this column, but
solving
> >this particular example is not the issue here.
> >
> >Jaap.
> >
> >
>
Received on Sun Feb 06 2000 - 15:24:47 CST