RULE to COST Oracle 10 Optimizer: Why?

Question

Oracle decided to abandon the rule-based optimizer from version 10g, leaving only one option based on costs.

I think the rule-based optimizer has an invaluable positive side of being always predictable . I saw how Oracle 10g changes its execution plans with new products, which leads to tortoise performances.

What could be causing this change?

Answer 1

Since everything you can do with RBO can be done with CBO .

CBO can also be rule-based - moreover, you can decide the "rules" yourself.

To create your own “rules”, you are hinting at your request or CREATE OUTLINE , which will tell you this. As a result, your execution plan is stable.

The contours are saved in the OUTLN system diagram; they are editable.

As for me, I always provide hints for my queries running in the production database.

Answer 2

RBO is often predictively bad and also predictably good. It also does not support partitioning and some other database functions. CBO is much better, and according to Kvasnuy, stability of the plan is also a feature of CBO.

Answer 3

RBO has been deprecated for a long time; it really was just kept for backward compatibility with legacy applications. Oracle announces the demise of RBO with version (IIRC) 8, which appeared about 10 years ago.

RBO was deterministic, but not so smart. Oracle was originally developed before cost-based optimizers were even available, not to mention mature technology. RBO has long been frozen and does not support many features of modern Oracle engines.

Cost-based optimization is much smarter. However, if you had queries optimized for RBO, they might not play well with CBO. You may need to rewrite or indicate your requests accordingly to configure them for CBO. It is also possible to specify a request plan and redefine CBO with this plan. This will give you deterministic query execution with stable plans.

Answer 4

(I am not a DBA.)

I understand that Oracle has been leaving RBO for a long time in favor of CBO. It seems to me useful to stop supporting a function that is no longer in active development (given a sufficiently long depreciation period) so that everyone uses the most efficient functions.

Interestingly, you called predictability the “invaluable" effect of using the rule-based optimizer. It seems that when the data changes to make the execution plan suboptimal, it would be better to switch to a new one. Only in the case when you indicated where the optimizer-triggers between the two execution plans will have a problem with choosing the best plan for the data that you are actually requesting. I am not sure that the advantage of predictability is in a more normal situation.

Ultimate support for the legacy optimizer should free up support for the new optimizer.

Answer 5

The reason they switched to cost-effective optimization is that it can work better because it is based on an analysis of statistical information that does not have a rule-based optimizer.

In order for CBO to work better, it is important to understand the role that statistics collection plays in changes to the execution plan that directly affect performance. On the one hand, working with more or less frequent statistics can help you. Here is a good article on CBO and statistics:

Optimization statistics Oracle Optimizer

Answer 6

I think you should do rule-based programming. Do not think about the situation, follow the list of unbreakable rules, regardless of the situation, no matter what you think is the best way, if the rules say FOR LOOP is used in case X, then you should use a loop, even if you know if only 1, cycle from 1 to 1.

We will make a reservation:

Each request has a better plan.

Each query optimizer will determine this plan x% of the time.

RBO had nowhere else to go, this percentage accuracy was lower than CBO, but it never improved. It was limited, like any rule-based system.