Oracle Database Administration: The Essential Refe

Cost-Based Optimizer

The cost-based optimizer (CBO) is Oracle’s preferred optimizer. All new functionality added to the kernel will only be supported from within the cost-based optimizer. Likewise, all research being done to find ways to improve performance of existing features will only be reflected in enhancements to the cost-based optimizer.

The cost-based optimizer uses information about the objects in the query to determine the most cost-efficient access path. It uses information about the number of rows in the tables and the distribution of values in indexed columns to calculate the relative costs of the various ways in which the query can be optimized. It then chooses the access path with the lowest computed cost.

Unlike the rule-based optimizer (described in Section 8.5, later in this chapter), the cost-based optimizer is very configurable. There are multiple INIT.ORA parameters that need to be set properly in order for the cost-based optimizer to work efficiently. Once you’ve assigned the appropriate parameter values, you’ll need to collect information about the size of the tables and the distribution of data within columns in order for the cost-based optimizer to make the proper decisions. Finally, because you know your data better than Oracle does, you can provide hints to the optimizer to improve its ability to choose wisely.