Feb 11, 2015
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 cost-based 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 re-uses 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 six-way
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 15-way
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 15-join 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
;
This example requests that the
optimizer join the tables in the order
that they are specified in the SQL
statement?s FROM clause, with the first
table in the FROM clause specifying the
driving table. Ordered hints are commonly
used in conjunction with other hints to
ensure that multiple tables are joined in
their proper order. Its use is also very
common in tuning data warehouse queries
that join more than four tables together.
As another example, the following query
uses an ordered hint to join the tables in
a specific order: emp, dept, sal, and
finally, bonus. I further refine the
execution plan by specifying that the emp
to dept join should use a hash join and
the sal to bonus join should use a nested
loop join.
select /*+ ordered
use_hash (emp, dept) use_nl (sal, bonus)
*/
from
emp,
dept,
sal,
bonus
where . . .
Practical suggestions for optimizer
permutations
In reality, it's more efficient to reduce
the size of the optimizer_max_permutations
parameter in production environments and
always use optimizer plan stability or
stored outlines to prevent time-consuming
reparsing of queries with large numbers of
table joins. Once the best table join
order has been found, you can make it
permanent by manually specifying the join
order for the tables by adding the ordered
hint to the query in question and saving
it?s stored outline.
When you plan to use optimizer plan
stability to make the execution plan
permanent in this fashion, it is
acceptable to temporarily set the
optimizer_search_limit up to the number of
tables in your query to allow all possible
table join orders to be considered. You
could then tune the query by reordering
the table names in the WHERE clause, and
use the ordered hint with stored outlines
to make the change permanent. In queries
involving four or more tables, the ordered
hint and stored outlines eliminate the
time-consuming task of evaluating SQL
parses for table join orders, thus
improving the speed of the query.
Once the optimal join order has been
determined, using the ordered hint
overrides the optimizer_search_limit and
optimizer_max_permutations parameters. The
ordered hint requests that the tables be
joined in the order that they appear in
the FROM clause of the query, so the
optimizer doesn?t enter the picture.
As an Oracle professional, you know that
there may be a significant start-up delay
the first time that a SQL statement enters
the library cache. But a savvy Oracle DBA
and developer can change the table search
limit parameters or specify the table join
order manually using the ordered hint,
dramatically reducing the time needed to
optimize and execute a new query.
and include the URL for the page.
