 |
|
Optimizer Hints
Oracle Tips by Burleson Consulting |
Optimizer mode hints are used to redirect the
overall optimizer goal. The most common mode for SQL tuning is the
rule hint, which is used when you suspect that the CBO is
using a suboptimal execution plan. It should be noted that the
optimizer hints only cause Oracle to apply the optimizer to the SQL,
and additional hints can still be added for further tuning.
The all_rows Hint
The all_rows hint represents the
cost-based approach designed to provide the best overall throughput
and minimum resource consumption. The all_rows mode favors
full-table scans and is not suitable for OLTP databases. When adding
this hint to a rule-based database, make sure that all tables and
indexes that participate in the query have statistics with the
analyze command.
The rule Hint
The rule hint directs Oracle to apply
rule-based optimization to the query. This hint is generally the
first one to try when tuning a SQL statement where you suspect that
the CBO is generating a suboptimal execution plan.
The rule hint ignores the table and
index statistics and uses basic heuristics to generate an execution
plan. Up until the release of Oracle8i, the rule-based
optimizer often generated a faster execution plan than cost-based
optimization.
The first_rows Hint
This is the cost-based approach designed to
provide the best response time. Just as with the guidelines for
adding the all_rows hint, make sure that all tables and
indexes that participate in the query have statistics with the
analyze command. The first_rows hint is useful when
testing the execution plan for a database with a questionable
optimizer mode such as choose. By using the first_rows
hint, you can tune your SQL for what the CBO considers to be the
fastest response time. Generally, all SQL running under
optimizer_mode=choose should be tested with the first_rows
and rule hints to see if the performance will improve.
Next, let’s look at how hints can be used
improved to the efficiency of a table join.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
