Previous | Table of Contents | Next |
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.
Rank | Access Path |
---|---|
1 | Single row by ROWID |
2 | Single row by cluster join |
3 | Single row by hash cluster key with unique or primary key |
4 | Single row by unique or primary key |
5 | Cluster join |
6 | Hash cluster key |
7 | Indexed cluster key |
8 | Composite key |
9 | Single-column indexes |
10 | Bounded range search on indexed columns |
11 | Unbounded ranch search on indexed columns |
12 | Sort-merge join |
13 | MAX or MIN of indexed column |
14 | ORDER BY on indexed columns |
15 | Full table scan |
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:
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:
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:
Optimization Goal | Description |
---|---|
Minimal Resources | This goal causes the optimizer to choose the execution plan that uses the least amount of system resources. This is done by selecting ALL_ROWS. |
Best Response Time | This goal causes the optimizer to choose the execution plan that has the best response time. This is done by selecting FIRST_ROWS. |
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.
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:
Function | Description |
---|---|
Gather statistics | The ANALYZE command can be used to gather statistics about tables, clusters, and indexes to be used to assist the cost-based optimizer in choosing the best execution plan for your system. |
Check data integrity | The ANALYZE command can be used to validate the integrity of the structure of a table, index, or cluster. |
Chained-row statistics | The ANALYZE command can be used to gather statistics about the number of chained rows in a table or cluster. |
The statistics gathered by the ANALYZE command can better help the optimizer make the correct choice in determining an execution plan.
Previous | Table of Contents | Next |