Oracle tuning with hints
Oracle ensures that the cost-based SQL optimizer becomes more
sophisticated with each release. With each new release, Oracle
provides an increasing number of methods for changing the execution
plans for SQL statements. While hints are used for tuning as well as
documentation, the most common use for Oracle hints is as a
debugging tool. The hints can be used to determine the optimal
execution plan, and then work backward, adjusting the statistics to
make the vanilla SQL simulate the hinted query.
Using Oracle hints can be very complicated, and Oracle developers
only use hints as a last resort, preferring to alter the statistics
to change the execution plan. Oracle contains more than 124 hints,
and many of them are not found in the Oracle documentation.
Hints can be used to alter optimizer execution plans. Remember, an
optimizer hint is a directive that is placed inside comments inside
the SQL statement and used in those rare cases where the optimizer
makes an incorrect decision about the execution plan. Since hints
are inside comments, it is important to ensure that the hint name is
spelled correctly and that the hint is appropriate to the query.
For example, the following hint is invalid because
first_rows access and
parallel access are mutually exclusive. That is because parallel
access always assumes a full-table scan and
first_rows favors index
-- An invalid hint
select /*+ first_rows parallel(emp,8)*/
Some Oracle professionals will place hints together to reinforce
their wishes. For example, if there is a SMP server with eight or
more CPUs, one may want to use Oracle Parallel Query to speed up legitimate full-table scans.
When using parallel query, one should seldom turn on parallelism at
the table level, alter table
customer parallel 35, because the setting of parallelism for
a table influences the optimizer. This causes the optimizer to see
that the full-table scan is inexpensive. Hence, most Oracle
professionals specify parallel query on a query-by-query basis,
combining the full hint with the parallel hint to ensure a fast
parallel full-table scan:
-- A valid hint
select /*+ full parallel(emp,35)*/
Now that the general concept of hints has been introduced, it is an
appropriate to look at one of the most important hints for optimizer
The ordered hint
determines the driving table for the query execution and also
specifies the order that tables are joined together. The ordered
hint requests that the tables should be joined in the order that
they are specified in the FROM clause, with the first table in the
FROM clause specifying the driving table. Using the ordered hint can
save a huge amount of parse time and speed SQL execution because the
optimizer is given the best order to join the tables.
The Ion tool
is also excellent for identifying SQL to tune and it can show SQL
execution over time with stunning SQL graphics.
For example, the following query uses the
ordered hint to join the
tables in their specified order in the FROM clause. In this example,
the execution plan is further refined by specifying that the
dept join use a hash join
and the sal to
bonus join uses a nested
/*+ ordered use_hash (emp, dept) use_nl (sal, bon) */
where . . .
Of course, the ordered
hint is most commonly used in data warehouse queries or in SQL that
joins more than five tables.
SQL execution is dynamic, and tuning a SQL statement for the current
data may not be optimal at a future date. The following section
provides a brief tour of the Oracle10g Automated Workload Repository
(AWR) and how it can be used to perform proactive SQL tuning.
SEE CODE DEPOT FOR FULL SCRIPTS