Databases Reference
In-Depth Information
The query transformation step accepts the parsed statement, divides it into query blocks (for
example, identifying a subquery), and determines if it's better to transform the query blocks
into a different SQL statement—semantically equivalent—that can be processed in a more
efficient way.
The estimator determines the overall cost of an execution plan, based on selectivity,
cardinality, and the cost of each operation involved in the plan. If statistics are available, the
estimator uses them for computation, improving the accuracy of the result.
The plan generator explores various plans for each query block, due to various factors:
different access paths, joins, and/or join order. We can follow many paths to answer a query;
the plan generator chooses the plan with the minimal cost.
Even though the Oracle optimizer does a great job without user intervention, in this chapter,
we will see how to tweak the query optimizer to obtain the best performance from each query.
We will suggest to the optimizer the best strategy to obtain the answer to our queries, use
statistics and histograms to provide more information about our data to the estimator, create
stored outlines for plan stability, and use some tools to tune our queries.
Exploring optimizer hints
In this recipe, we will see how to suggest (to the optimizer) the strategy to adopt for choosing
the best execution plan, using optimizer hints.
The use of optimizer hints is a trick and should be considered
only when no solution seems to work. The query optimizer is
designed to choose the best execution plan, based on many
different considerations, so it's very important to keep updated
statistics to help the query optimizer in doing its work.
How to do it...
The following steps will explore optimizer hints:
1.
Connect the database to the
SH
schema:
CONNECT sh@TESTDB/sh
2.
Set the auto-trace functionality in SQL*Plus to see only the execution plan without
executing the queries:
SET AUTOT TRACE EXP
