Re: cost based optimization

danielroy10junk_at_hotmail.com (Daniel Roy) wrote in message news:<3722db.0404041111.22b8c521_at_posting.google.com>...
> > Hello. I'm looking for some resources on using the CBO properly.... it
> > seems that this company is inclining to going to RBO, just because the CBO
> > sucks... (so they say). I would be more interested in parameters that will
> > speed up DML statements, and trying to get away from using dbms_stats. (if
> > possible).
>
> The answer is going to vary greatly depending on your Oracle version.
> CBO is the way to go, and the only option starting with Oracle 10G.
> When you say that the performance sucks under CBO, do you mean
> everything, or some queries. If it's everything, you probably have
> some database parameter which is not set properly (not enough shared
> memory allocated, not enough buffer blocks, sort area too small, ...).
> In that case, switching to RBO wouldn't help anything anyway. If it's
> specific queries which are running too slowly, ask the DBA to get the
> exec plans, and tune them. Some examples of actions which could help
> specific queries under CBO include:
> - Gather some fresh stats for the tables involved
> - Add an index
> - Create a histogram on a skewed indexed column
>
> If your DBA is fed up running dbms_stats, you can (with Oracle 9i)
> start monitoring tables, and use dbms_stats in a way that with one
> statement, only the tables requiring it get their stats refreshed
> (look up the doc to see the exact syntax).
>
> HTH
>
> Daniel

I agree that CBO based development is the way to go. Our primary system is a 300G OLTP MRPII application and we have ran cost based since version 7.0. Sometimes the CBO is wrong, but that is what tuning is all about. The majority of times the CBO is either right or close enough that it does not matter.

HTH -- Mark D Powell -- Received on Mon Apr 05 2004 - 15:03:49 CEST