RelationalDBDesign 





SQL Tuning   «Prev  Next»
Lesson 8 The Oracle cost-based optimizer
Objective Understand the cost-based optimizer.

Why use the 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.
The next lesson delves into tuning with SQL hints.