Take Advantage of The Cost-Based Optimizer
The component of the Oracle software that determines the execution plan for a SQL statement is known as the optimizer. Oracle supports two approaches to query optimization. They are:
- The rule-based optimizer determines the execution plan based on a set of rules. The rules rank various access paths. For example, an index-based retrieval has a lower rank than a full table scan. A rule-based optimizer uses indexes wherever possible.
- The cost-based optimizer determines the execution plan based on an estimate of the computer resources (the cost) required to satisfy various access methods. The cost-based optimizer uses statistics (including the number of rows in a table and the number of distinct values in indexes) to determine the optimum plan.
Early experiences with the cost-based optimizer in Oracle 7.0 and 7.1 were often disappointing and gave the cost-based optimizer a bad reputation. However, the cost-based optimizer has been improving in each release. The rule-based optimizer is virtually unchanged since Oracle 7.0. Advanced SQL access methods (such as star and hash joins) are only available when you use the cost-based optimizer.
The cost-based optimizer is the best choice for almost all new projects. Converting from rule to cost-based optimization is worthwhile for many existing projects. Consider the following guidelines for getting the most from the cost-based optimizer:
- OPTIMIZER_MODE. The default mode of the cost-based optimizer (OPTIMIZER_MODE=CHOOSE) attempts to optimize the throughput (that is, the time taken to retrieve all rows) of SQL statements. It often favors full table scans over-index lookups. When converting to cost-based optimization, many users are disappointed to find that previously well-tuned index lookups change to long running table scans. To avoid this, set OPTIMIZER_MODE=FIRST_ROWS in init.ora or ALTER SESSION SET OPTIMIZER_GOAL=FIRST_ROWS in your code. This instructs the cost-based optimizer to minimize the time taken to retrieve the first row in your result set and encourages the use of indexes.
-
Hints. No matter how sophisticated the cost-based optimizer becomes, there are still occasions when you need to modify its execution plan. SQL hints are usually the best way of doing this. By using hints, you can instruct the optimizer to pursue your preferred access paths (such as a preferred index), use the parallel query option, select a join order, and so on. Hints are entered as comments following the first word in a SQL statement. The plus sign (+) in the comment lets Oracle know that the comment contains a hint. Hints are fully documented in the Oracle Server Tuning Guide and some of the most popular hints are summarized in Optimizer Hints. In the following example, the hint instructs the optimizer to use the CUST_I2 index:
SELECT /*+ INDEX(CUSTOMERS CUST_I2) */ *
FROM CUSTOMERS
WHERE NAME=:CUST_NAME
- Analyze your tables. The cost-based optimizer’s execution plans are calculated using table statistics collected by the ANALYZE command. Make sure you analyze your tables regularly, that you analyze all your tables, and that you analyze them at peak volumes (for instance, do not analyze a table just before it is about to be loaded by a batch job). For small to medium tables, use analyze table table_name compute statistics. For larger tables take a sample (for example, analyze table table_name estimate statistics sample 20 percent).
- Use histograms. Prior to Oracle 7.3, the cost-based optimizer included the number of distinct values in a column, but not the distribution of data within the column. This meant that it might decline to use an index on a column with only a few values, even if the particular value in question was very rare and would benefit from an index lookup. Histograms, introduced in Oracle 7.3, allow column distribution data to be collected, and allow the cost-based optimizer to make better decisions. You can create histograms with the FOR COLUMNS clause of the analyze command (for instance ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS FOR ALL INDEXED COLUMNS). You cannot take advantage of histograms if you are using bind variables.
Related Topics
Isolate and Tune Problem SQL
Tune Your Oracle Database
