Re: cost based optimization

From: Daniel Roy <danielroy10junk_at_hotmail.com>
Date: 4 Apr 2004 12:11:10 -0700
Message-ID: <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 Received on Sun Apr 04 2004 - 21:11:10 CEST

Original text of this message