 |
|
Initialization
Parameters that Affect Cost-Based Optimizer Behavior
Oracle Tips by
Burleson
|
There are many initialization parameters that
affect the behavior of the cost-based optimizer and SQL hints. Often,
there are prerequisite initialization parameters that are required in
order for a hint to be invoked. For example, the
star_transformation_enabled initialization parameter must be set
to TRUE or a star hint will have no effect on the execution
plan for the SQL.
Other initialization parameters affect the
threshold for invoking a hint. For example, the hash_area_size
initialization parameter controls the threshold for the invocation of
a use_hash hint. In any case, all of the following
initialization parameters affect the costing that determines the
execution plan for the CBO.
Initialization Parameters That Affect Table Joins
While we will be going into great detail on
tuning table joins in Chapter 16, you need to understand the
relationship between the initialization parameters and table join
behavior. The following initialization parameters can be used to
direct the CBO to use different table join techniques.
Limiting the Number of SQL Query Permutations
Oracle provides initialization parameters to
control the amount of work performed by the cost-based optimizer when
evaluating a query. While parsing is quite fast for simple queries,
complex queries with more than six tables can parse for many minutes
while Oracle evaluates every possible table join combination.
Sometimes, data warehouse DBAs are perplexed
when they find that a 15-way table join takes 30 minutes to parse!
This is because there are 15 factorial possible permutations of the
query and over one trillion (1,307,674,368,000) query permutations.
While the ultimate solution is to employ stored
outlines to remove the parsing phase, Oracle has two important
initialization parameters that work together to control the number of
possible execution plans generated by the Oracle optimizer.
TIP: If you do not have complex SQL queries that
join five or more tables together, you need not be concerned with the
optimizer_search_limit or optimizer_max_permutations
parameters. These only apply when Oracle is computing possible table
join combinations for queries with large numbers of tables.
The optimizer_search_limit Parameter
The optimizer_search_limit parameter
specifies the maximum number of table join combinations that will be
evaluated by the CBO when deciding the best way to join multiple
tables. The reason is to prevent the optimizer from spending an
inordinate amount of time on every possible join ordering. The
optimizer_search_limit parameter also controls the threshold for
invoking a star join hint, and a star hint will be honored when the
number of tables in the query is less than the
optimizer_search_limit. The default value is 5.
TIP: Most SQL tuning experts always use
stored outlines or the ordered hint for any query involving
four or more tables. This eliminates the time-consuming evaluation of
the SQL parses for table join orders, and it improves the speed of the
query. However, when tuning the SQL to determine the best table join
order, it is wise to leave optimizer_search_limit to a high
value so that all possible table join orders are considered.
If the number of tables in the query is less
than optimizer_search_limit, the optimizer examines all
possible table join combinations. The number of joins orders is the
factorial value of the number of tables in the query. For example, a
query joining five tables would have 5! = 5 * 4 * 3 * 2 * 1 = 120
possible combinations of table join orders. The number of possible
evaluations is the factorial of the optimizer_search_limit, so
with the default value for optimizer_search_limit of five, the
cost-based optimizer will evaluate up to 120 table join orders.
The optimizer_search_limit and
optimizer_max_permutations parameters work together, and the
optimizer will generate possible table joins permutations until the
value specified by optimizer_search_limit or
optimizer_max_permutations is exceeded. When the optimizer stops
evaluating table join combinations, it will choose the combination
with the lowest cost. For example, queries joining nine tables
together will exceed the optimizer_search_limit but still may
spend expensive time attempting to evaluate all 362,880 possible table
join orders (nine factorial) until the optimizer_max_permutations
parameter has exceeded its default limit of 80,000 table join orders.
However, when tuning a SQL statement when you
plan to use optimizer plan stability to make the execution plan
permanent, it is acceptable to temporarily set the
optimizer_search_limit up to the number of tables in your query,
tune the query by reordering the table names in the where
clause, and then use the ordered hint with stored outlines to make the
change permanent.
Note: The use of the ordered hint
overrides the optimizer_search_limit and
optimizer_max_permutations parameters. This is because the
ordered hint requests that the tables be joined in the order that
they appear in the from clause of the query. The ordered
hint is the way most SQL tuning professionals disable table join
evaluation, once the optimal join order has been determined.
This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald
K. Burleson, published by Oracle Press.
|
