Question: What are Oracle hints, and how do
I use them?
Answer: An Oracle hint is an optimizer
directive that is embedded into an SQL statement to suggest to
Oracle how the statement should be executed. For example, here
is an Oracle hint to change the default optimizer mode for a query:
Select /*+first_rows(10) */
mycol,
from
mytab;
You can follow these steps for tuning
your Oracle hints and
then reverse engineer the regular SQL statements to emulate the
execution plan generated by the hints is one of many ways to use
Oracle hints. Using Oracle hints can be extremely complicated,
which is why Oracle hints
are considered to be a
last resort measure as a means to change your SQL execution
plan (after changing CBO statistics, instance parameters, etc.).
There is a large number of Oracle hints for use in optimizing
your SQL,
and some hints are not
documented at all.
Here are some related notes on Oracle hints:
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
