What are CBO and RBO?

June 9, 2015

in DBA, faq, performance

CBO, RBO Oracle Optimizers

In large applications that juggle tons of data, it isn’t enough to get a query to run without errors. The query must also perform well – get the results in fastest possible time, using the least possible resources.

This is where Oracle’s inbuilt optimizers come into play. Till Oracle 9i, Oracle server provided the options of CBO (cost-based optimizer) and RBO (rule-based optimizer). RBO has been desupported by Oracle 10G onwards, but it is still worth knowing about to understand the evolution of Oracle’s optimization techniques.

This post explores the key differences between CBO and RBO.

Oracle’s optimizer determines the most efficient way to execute a SQL statement, and gives as output an "optimizer plan" that describes the optimum method of execution.

CBO and RBO differ in how they build the optimizer plan, and in various other ways. For a summary of these differences, a tabulated comparison of Oracle’s two optimizers: CBO and RBO.

  CBO RBO
Stands for Cost-Based Optimizer Rule-Based Optimizer
History Introduced in Oracle 7 (year 1992). Evolved with every Oracle release since then, and has now replaced RBO. Introduced in Oracle 5 (year 1985), as the only optimizer mode at that time. Co-existed with CBO Oracle 7 onwards. Phased out formally Oracle 10G onwards (year 2003).
Overall Approach Chooses an execution plan that is most cost-effective (in terms of I/O, CPU, memory, etc.) Chooses an execution plan based on a predefined set of rules (picks the lowest-ranked access path from a list of access paths)
Impact of Data Distribution Takes into account data volume and distribution while determining the access path Does not take into account data volume and distribution while determining the access path
Stats Gathering Required? Yes. For optimal CBO execution plans, stats should be updated and realistic. No. Stats gathering has no impact on RBO execution plans.
Impact of FROM clause table order Table order in FROM clause does not matter. CBO determines the best plan based on stats and other information available to it. Tables are processed from right to left in the FROM clause
Impact of WHERE clause predicate order Predicate order in WHERE clause does not matter. CBO determines the best plan based on stats and other information available to it. WHERE clause predicates are processed from right to left
Understands SQL Hints?

CBO understands SQL hints.

Any well-formed SQL hint makes Oracle use the CBO, even if the optimizer mode is set otherwise. The only exception is the/*+ RULE */ hint.

RBO does not understand SQL hints.

The /*+ RULE */ hint makes Oracle use the RBO, even if the optimizer mode is set otherwise.

Limitations CBO is evolving with each Oracle release to become more robust and intelligent: a side-effect of this is potential performance regression (i.e. execution plan changes for the worse) after a database upgrade. Oracle offers guidelines for fixing sub-optimal plans in this scenario. RBO does not produce optimal plans in several scenarios – a classic case is one where a full-scan is more cost-effective than index-based scan, but the RBO is set to always use an index.

RBO does not work with newer features of Oracle such as partitioned tables, function-based indexes, query rewrite with materialized views, etc.

Further Reading (9.2 SQL Reference) Cost-Based Optimizer Rule-Based Optimizer

{ 2 comments… read them below or add one }

Hans June 15, 2015 at 3:25 pm

Excellent summary.

sasanka ghosh May 24, 2017 at 10:49 am

Excellent

Leave a Comment

Previous post:

Next post: