Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: When does performance worsen when going from RULE to COST

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

From: Leonard F. Clark <leonard_at_lf-clark.prestel.co.uk>
Date: Sat, 05 Feb 2000 19:10:13 GMT
Message-ID: <389c7503.30438097@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 Sat Feb 05 2000 - 13:10:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US