The
most important component of Oracle SQL execution
time is the time spent preparing a new SQL
statement for execution. But by understanding the
internal mechanisms of the generation of the
execution plan, you can control the amount of time
Oracle spends evaluating table join order, and
boost the performance of queries in general.
For complete tips on SQL tuning with the optimizer,
see my book "Oracle
Tuning: The Definitive Reference".
Preparing
the SQL statement for execution
When a SQL statement enters the Oracle library
cache, the following steps must occur before the
statement is ready to execute:

Syntax check  The SQL
statement is checked for proper spelling and
word order.

Semantic parse  Oracle
verifies all of the tables and column names
against the data dictionary.

Stored Outline check  Oracle
checks the data dictionary to see if a stored
outline exists for the SQL statement.

Query Transformation
 If enabled (query_rewrite=true),
Oracle will transform complex SQL into simpler,
equivalent forms and replace aggregations with
materialized views, as appropriate.

Generate execution plan  Oracle
uses costbased optimizer algorithms and
statistics in the data dictionary to determine
the optimal execution plan.

Create binary code  Oracle
generates a binary executable based on the
execution plan.
Once a SQL statement is prepared for execution,
subsequent executions will happen very fast,
because Oracle recognizes identical SQL statements
and reuses executables for those statements.
However, for systems that generate ad hoc SQL or
SQL with embedded literal values, SQL execution
plan generation time is significant, and previous
execution plans often can?t be used. For those
queries that join many tables, Oracle can spend a
significant amount of time determining the proper
order to join the tables together.
Evaluating table join order
The most expensive step in the SQL preparation
process is the generation of the execution plan,
particularly when dealing with a query with
multiple joins. When Oracle evaluates table join
orders, it must consider every possible
combination of tables. For example, a sixway
table join has 720 (permutations of 6, or 6 * 5 *
4 * 3 * 2 * 1 = 720) possible ways that the tables
can be joined together. This permutation issue
becomes even more pronounced when you have more
than 10 joined tables in a query: For a 15way
table join, there are over one trillion
(1,307,674,368,000 to be exact) possible query
permutations that must be evaluated.
Set a
limit with the optimizer_search_limit parameter
You can control this situation using the
optimizer_search_limit parameter, which specifies
the maximum number of table join combinations that
will be evaluated by the optimizer. The default
value of five means that 5 factorial (5!)
possibilities will be considered.
Use of this
parameter prevents the optimizer from spending an
inordinate amount of time evaluating every
possible table join combination. If the number of
tables in the query is less than the optimizer_search_limit, the optimizer examines all
possible table join combinations.
For example, a query joining five tables would
have 120 (5! = 5 * 4 * 3 * 2 * 1 = 120) possible
table join combinations, so if the
optimizer_search_limit is five (the default) the
optimizer would evaluate all 120 of these
possibilities. The optimizer_search_limit
parameter also controls the threshold for invoking
a star join hint. A star hint will be honored when
the number of tables in the query is less than the
optimizer_search_limit.
The
optimizer_max_permutations parameter
The optimizer_max_permutations initialization
parameter defines the upper boundary for the
maximum number of permutations considered by the
optimizer, and is dependent on the
optimizer_search_limit initialization parameter.
The default value for optimizer_max_permutations
is 80,000.
The optimizer_search_limit and
optimizer_max_permutations parameters work
together to place an upper limit on the number of
permutations the optimizer will consider: The
optimizer will generate possible table join
permutations until the value specified by
optimizer_search_limit or
optimizer_max_permutations is exceeded. Once the
optimizer stops evaluating table join
combinations, it will choose the combination with
the lowest cost.
Specify a
join order with the ordered hint
You can set an upper limit on the number of
evaluations the optimizer will perform. But even
with a very high value of allowed permutation
evaluations, there is a significant chance that
the optimizer may give up too early on complex
queries. Recall my example of the 15join query
with over one trillion possible join combinations.
If the optimizer were to stop after evaluating
80,000 combinations, it would only have evaluated
0.000006 percent of the possible permutations, and
would probably not have located the optimal join
order for this large query.
One of the
best ways around this problem in Oracle
SQL is to manually specify the table join
order. The rule to follow here is join the
tables together so that the smallest
solution set is created as quickly as
possible, usually by joining into the
table with the most restrictive WHERE
clause first.
The code below is an example of a query
execution plan that is forced to perform a
nested loop join with a parallel query on
a table called emp. Note that I have
invoked the ordered hint to direct the
optimizer to evaluate the tables in the
order they are presented in the WHERE
clause.
select /*+ ordered
use_nl(bonus) parallel(e, 4) */
e.ename,
hiredate,
b.comm.
from
emp e,
bonus b
where
e.ename = b.ename
;