Describe the Oracle Optimizer Functions

The query optimizer is the key to one of the great advantages of all relational databases. Before we jump into the features of the Oracle optimizer, we should review the important reasons why a query optimizer is necessary, as well as the basics of Oracle's own query optimizer.

One of the foundations of a relational database is that users will not have to specify an access path to retrieve data.
Question: What if there are multiple ways to access the data needed to satisfy a particular query?
Virtually all relational databases include some form of query optimizer. As the name implies, the query optimizer is a piece of software that chooses the optimal path to access the data needed for a query. Choosing the right query access path can dramatically affect the performance of a query. For instance, if the data requested in a query can be selected and retrieved by using an index, it is usually much faster to use the index to access the data than to go directly to the database row. But what if multiple indexes could be used? Are there times when using an index will not be the fastest way to access the data? The job of the query optimizer is to provide the best path for any query.

Oracle has had a query optimizer for a long time. In the early days, Oracle offered a rule-based optimizer. As the name implies, this optimizer was based on a fixed set of rules. These rules tried to use the syntax of the Structured Query Language (SQL) query to determine the optimal access path, referred to as an execution plan ^[1]. For instance, if a query contained a reference to an index, the query optimizer would usually use that index to create the execution plan.
There were two disadvantages of the rule-based optimizer:

1. It had somewhat limited complexity, because there were not many rules.
2. The rules, by themselves, did not always lead to the right decision.

To correct these deficiencies, Oracle7 introduced a cost-based optimizer. As the name implies, the cost-based optimizer includes calculations that estimate the cost of using a particular access path in the execution plan, which in turn results in a more accurate plan. Oracle has endorsed the cost-based optimizer, which means that new features, such as partitioning, are no longer considered by the rule-based optimizer.
By default, Oracle uses the cost-based optimizer. The OPTIMZER_MODE setting tells Oracle which type of optimizer to use from among the following:

1. RULE for the rule-based optimizer
2. COST for the cost-based optimizer
3. CHOOSE so that Oracle can choose which optimizer to use, based on the presence or absence of statistical information or stored outlines, both of which are described later in this course

The link below contains some answers with regards to why calculate the cost.
Why calculate cost?

When you upgrade to Oracle Database 11g, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time-consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade. To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g, Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics.
In the next lesson, you will learn about the specific enhancements to Oracle8's query optimizer.

[1] Execution Plan: A description of the steps the Oracle database will take to retrieve and select the data requested by a query.