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.

Optimizer plan stability

See also Fallacies of the Cost Based Optimizer by Wolfgang Breitling.

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

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.

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

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.

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.

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).

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.