cost base vs rule base optimization

Hi,

what is the performance impact of no statistics if optimizer_mode is choose? I know that the optimizer will default to rule base, but is that bad for the db. And why?


Thanks in advance!

Rule based optimization is an older method that the optimizer can use to figure out how to execute your query. The Cost based optimizer is a more "intelligent" optimizer that looks at statistics to determine how to execute your query.

When you set your optimizer goal to CHOOSE, the optimizer will by default use the CBO unless you don't have statistics. When running in CBO mode, it is very important you have accurate statistics on your database (ANALYZE TABLE, or DBMS_STATS.Gather*). Most of the new 8i query features (FGAC,Function Based Indexes, etc.) will only work with the CBO.

But what is the impact if any if you run on rule base.

The "Impact" is your queries might not run as fast or you can't use the new features. Will that affect a plain-old OLTP database with poorly written queries? Probably not.

Quote:

---

Originally posted by marist89
The "Impact" is your queries might not run as fast or you can't use the new features. Will that affect a plain-old OLTP database with poorly written queries? Probably not.

---

lol
:rolleyes:

http://tahiti.oracle.com

concepts guide

for the difference between cbo and rule based, see the concepts guide.

It also depends on the application that is running against your db.
If the application is written with rbo in mind, it's beter not to change it.

If you choose to use cbo, don't forget to analyse your schema's.

HTH
Gert