Oracle 10 optimizer from RULE to COST: why?

Question

Oracle decided to dismiss the rule-based optimizer from version 10g, leaving the cost-based one as the only choice.

I think that a rule-based optimizer has the unvaluable positive side of being always predictable. I've seen instead Oracle 10g changing execution plans from night to day, leading to turtle-like performances.

Which could be the rationale behind this change?

Answer 1

Because everything you can do with RBO, can be done with CBO.

The CBO can be rule-based too — more than that, you may decide the "rules" yourself.

To create your own "rules", you hint your query or do a CREATE OUTLINE which will hint it for you. As a result, your execution plan is stable.

The outlines are stored in a system schema called OUTLN, they are editable.

As for me, I always supply hints to my queries running in a production database.

Answer 2

The RBO is often predicatably bad as well as predictably good. It also doesn't support partitioning and some other database features. The CBO is much better, and as Quassnoi says plan stability is a feature of the CBO also.