|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.
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.
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.
CBO needs some statistics in order to assess the cost of the different access plans. These statistics include values such as
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).
Watching CBO do its decisions
See also A look under the Hood of CBO: The 10053 Event by Wolfgang Breitling.