René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

The cost based optimizer (CBO) of Oracle

With Oracle 10g, CBO will be the only optimizer as RBO will be dead.
The impact of logical and physical io for the CBO.
See also Fallacies of the Cost Based Optimizer by Wolfgang Breitling.

Most important task

The most important task for the CBO is to design an execution plan for an SQL statement.

Permutations

The CBO takes an SQL statement and tries to weigh different ways (plan) to execute it. It assigns a cost to each plan and chooses the plan with the smallest cost. The number of permutations of plans the cbo tries can be set with optimizer_max_permutations.

Costs

The cost for a statement is calculated like: physical io + logical io / 1000 + net io.

Tuning the CBO

One of the most important parameters to tune the CBO is optimizer_index_cost_adj. Actually, optimizer_index_cost_adj doesn't tune CBO itself but is important for the CBO in deciding for an execution plan which affects the speed of the SQL query.
Other important parameters are: optimizer_search_limit and optimizer_max_permutations.

Gathering statistics

CBO needs some statistics in order to assess the cost of the different access plans. These statistics include values such as
  • size of tables
  • size of indexes
  • number of rows in the tables
  • number of distinct keys in an index
  • number of levels in a B* index
  • average number of blocks for a value
  • average number of leaf blocks in an index
These statistics can be gathered with dbms_stats and the monitoring feature.

Setting OPTIMIZER_MODE

The optimizer_mode determines if Oracle will use CBO or RBO.
As with Oracle 10g, there is only CBO, there is no imperative need to set this parameter to use the CBO. However, it will influence the the plan either to have a the first rows as soon as possible (first_rows) or all rows (all_rows).

Watching CBO do its decisions

Setting diagnostic event 10053 allows to watch the CBO how it decides for an execution plan.
See also A look under the Hood of CBO: The 10053 Event by Wolfgang Breitling.