Why use Cost-based optimizer?

It's important to note that as of Oracle Database 11g, Oracle Corporation has deprecated the Rule-Based Optimizer (RBO), and it is no longer supported. The recommended and default optimizer in Oracle databases is now the Cost-Based Optimizer (CBO). Regardless, to provide a historical perspective, the parameters a DBA might have considered when determining whether to use the RBO or CBO are as follows:

1. Cost-Based Optimizer (CBO):
   1. Data Distribution and Volume: The CBO uses statistics about the data to make informed decisions, so it works well with databases of any size and is particularly effective when the data volume is large or the distribution is uneven.
   2. Complex Queries: The CBO is more capable of handling complex queries, including those with multiple table joins, subqueries, or complex predicates.
   3. Database Statistics: The CBO relies heavily on the database's statistics. The DBA needs to ensure that the statistics are accurate and updated regularly.
   4. Change in Workloads: If the database has to handle changing workloads or data distributions, the CBO, unlike the RBO, can adapt to these changes because it bases its decisions on the current statistics.
2. Rule-Based Optimizer (RBO):
   1. Simpler Queries and Database Structures: The RBO could have been chosen for smaller databases or databases with simpler query structures and less complex relationships. The RBO did not need data statistics, so it could perform consistently without regular statistics updates.
   2. Lack of Statistics: Before Oracle deprecated the RBO, if statistics were not available or not regularly updated, the RBO might have been chosen as it doesn't require data statistics.
   3. Compatibility and Legacy Systems: Some older applications designed around the behavior of the RBO might have required its use. In such cases, transitioning to the CBO would require careful planning, testing, and potentially changes to the application.

While it might have been a consideration in the past, the choice between the CBO and RBO is no longer a relevant decision for Oracle DBAs, since Oracle has deprecated the (RBO) rule-based optimization and fully supports the CBO. Modern Oracle databases should use the CBO and ensure that database statistics are kept up to date for the best performance.

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;

ran in just over 2 seconds and the query

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

took exactly the same elapsed time because the additional CPUrequired was overlapped with disk reading.
The next lesson delves into tuning with SQL hints.