Tuning SQL with Hints

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.