By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

cost based optimization

P: n/a
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

Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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
Jul 19 '05 #2

P: n/a
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 --
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.