Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: When does performance worsen when going from RULE to COST
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 Sat Feb 05 2000 - 13:10:13 CST