cost based optimization

[Alex Ivascu]

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).

Thanks.

Alex

[Daniel Roy]

> 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

[Mark D Powell]

da*************@hotmail.com (Daniel Roy) wrote in message news:<37************************@posting.google.co m>...

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 --