Why use Cost-based optimizer?

The Oracle cost-based optimizer was developed as a replacement for the rule-based optimizer. Oracle recognized that if the optimizer had access to information about the distribution of table data i.e.,

1. number of rows,
2. distribution of key values

then it could make better decisions about the fastest access method for an SQL query.
Today, Oracle recommends the use of the cost-based optimizer, but it is important to note that for some queries, the rule-based optimizer makes better access decisions.

The cost-based optimizer required that statistics exist for Oracle tables and indexes. In theory, because the cost-based optimizer has information about the data in the tables, it will make a better decision about the access plan for the data. These table and index statistics are generated with the

ALTER TABLE ESTIMATE STATISTICS 

command. Once generated, the statistics should be refreshed whenever that nature of the table or index data changes. Most DBA's re-analyze their tables and indexes weekly to ensure that the cost-based optimizer statistics are valid.
Again, we will go into great detail about the cost-based optimizer in a later module.

This is used by the application in question rather than the older Rule Based Optimizer (RBO). The latter is still available under Oracle but is best viewed as being present solely for older applications that have been tuned to use it. All new development should assume the use of CBO. However until Oracle9i CBO does not use CPU resource as part of its cost equation, estimating instead what the Oracle documentation tends to refer to as I/O cost but which in reality equates more to "block visits for read." The difference between (nominal) disk reads and block visits is accounted for by Oracle's block caching mechanism, and is sometimes magnified by operating system or device controller caching.

Given that well-tuned Oracle applications are more likely to bottleneck on disk activity than on CPU, it might seem that basing CBO solely on I/O was an inspired decision. Regrettably, by factoring in the I/O savings of Oracle's multiblock reads, CBO has a marked tendency to prefer full table scans over indexed access for retrieval of more than about 5 percent of the rows in a table and this can lead to the execution of query predicate clauses for every row in the table. Depending on the complexity of these predicates, and the SQL features and functions used, the result can cause a surprising CPU load.

select count(membname) records from million;

select count(membname) records from million where memb# > 0;