Tuning SQL with Hints

Oracle Optimizer still makes use of "hints" to determine the fastest access path to the data. Hints are instructions that can be given to the optimizer to force it to choose a particular execution plan. Hints can be used to override the optimizer's default choices, or to provide information that the optimizer may not have otherwise known.
There are a number of different types of hints available in Oracle. Some of the most common hints include:

1. NDEX: This hint tells the optimizer to use a specific index for a particular query.
2. LEADING: This hint tells the optimizer to use a specific table as the leading table in a join.
3. NL: This hint tells the optimizer to use a nested loops join.
4. HASH: This hint tells the optimizer to use a hash join.

Hints can be a useful tool for tuning the performance of Oracle queries. However, it is important to use hints sparingly. Too many hints can make the optimizer's job more difficult, and can actually lead to worse performance.
Here are some of the guidelines for using hints:

1. Use hints only when you know that the optimizer's default choices are not optimal.
2. Use hints only when you have a good understanding of the impact that they will have on the performance of the query.
3. Test the performance of the query with and without the hint to make sure that the hint is actually improving performance.

Overall, hints can be a useful tool for tuning the performance of Oracle queries. However, it is important to use hints sparingly and to understand the impact that they will have on the performance of the query.

As we discussed in a prior lesson, the Oracle optimizer does not always make the best decision about the fastest access path to the data.
This is not always Oracle’s fault. No SQL optimizer can predict in advance the number of rows that will be returned by queries, and the number of rows that are returned by sub-queries can have a dramatic impact upon query performance.
Hence, Oracle provides a huge amount of hints that can be used to force the Oracle optimizer to change the way the table and indexes are accessed. Hints can be used to invoke parallelism, for the use of a specific index, as well as a host of other functions. To illustrate how it works, consider the query from the prior lesson. We saw that the EXPLAIN PLAN used nested loops to access the data.
View the Oracle SQL Code below.

 
OPERATION         OPTIONS        OBJECT_NAME
---------------- --------------- -----------------
SELECT STATEMENT  Cost = 87
SORT              ORDER BY
NESTED LOOPS
TABLE ACCESS      FULL           MON_TAB_STATS
TABLE ACCESS      BY INDEX ROWID MON_TAB_STATS
INDEX             RANGE SCAN     MON_TAB_STATS_TAB_IDX                                          

Let us assume that we have determined that this query will run faster with the rule-based optimizer.
We can change the SQL statement to add a hint that tells Oracle to use the rule-based optimizer: select /*+ rule */
Once this is added to the end of the SQL select line, we can re-explain the query and see that the access path has changed.
View the Code below.

Here we see a radically different access path to the data.
To verify which access path is fastest, many DBAs use the SET TIMING ON option in SQL*Plus and run the queries with the hints. We will cover hints in greater detail later in this course.
The next lesson concludes this module.