Oracle Hints

Oracle Database Tips by Donald BurlesonJune 13, 2015

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 fortuning
your Oracle hintsand
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 whyOracle hints
areconsidered 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 inoptimizing
your SQL,
and somehints are not
documented at all.

Note:This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.

Verify
experience!Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.

Errata? Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just e-mail:
and include the URL for the page.