Optimization Methods

In general, the cost-based approach is the recommended approach. In most cases, the cost-based approach determines an execution plan that is as good or better than the rule-based approach. If you have manually tuned your SQL Statements, however, you may get better performance with rule-based optimization than with cost-based.

The rule-based approach can be useful if you are moving a highly tuned application from an older version of Oracle that has been using the rule-based approach. This and a lack of statistics may cause rule-based optimization to be more efficient than the cost-based approach. However, as you gather statistics on your database, you may want to migrate to cost-based optimization.

The following sections examine both approaches. Hints can also be very useful in optimizing the execution of your SQL statements.

Rule-Based Approach

The rule-based approach to Oracle optimization is the simpler of the two methods. In the rule-based approach, the execution plan is derived by examining the available paths and comparing them against a table of the rank of these paths. The table of costs is shown in Table 17.1.

Because the rule-based approach is based simply on the SQL statements themselves, it is unnecessary to have any statistics about the database tables. The rule-based approach follows these steps to determine the execution plan:

1.  Determine the possible execution plans.

2.  Rank the different plans according to Table 17.1.

3.  Choose the approach with the lowest ranking.

In this way, the rule-based optimization approach is very efficient and works well. However, if statistics are available for your tables, clusters, or indexes, the cost-based approach can be very efficient.

Cost-Based Approach

The cost-based approach to optimization uses information about the structure and contents of your database to choose the most efficient execution plan. During the normal operation of the RDBMS, or by executing the ANALYZE command, statistics are gathered on the data distribution and storage characteristics or your database tables, clusters, and indexes. The cost-based optimizer uses this information to determine the most optimal execution plan.

This approach is done in three steps:

1.  The optimizer generates a set of possible execution plans, just as the rule-based optimization approach does.

2.  The cost of each plan is determined based on statistics gathered about the database. This cost is based on CPU time, I/O, and memory necessary to execute the plan.

3.  The optimizer compares the costs and chooses the execution plan with the smallest cost based on your specifications.

---

Note:  
The number of tables in the FROM clause the optimizer uses to determine the best join permutation can be set by the initialization parameter OPTIMIZER_SEARCH_LIMIT as described next.

---

The default goal of the cost-based optimizer is to generate an execution plan that gives the best throughput. You can specify other optimization goals, including the following:

By choosing the optimization approach that best suits your particular installation and application, the performance of your SQL statements can be tuned to specifically meet your needs.

Using the ANALYZE Command

You can use the ANALYZE command to gather statistics about your system that can be used for the cost-based optimizer. This command can be used not only for statistics gathering but for other purposes as well. The ANALYZE command can be used to do the following:

The statistics gathered by the ANALYZE command can better help the optimizer make the correct choice in determining an execution plan.