Lesson 9 | Tuning SQL with hints |
Objective | Describe how a Hint changes Access Paths |
Tuning SQL with Hints
Why use 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.
Cost
OPERATION | OPTIONS | OBJECT_NAME |
SELECT STATEMENT Cost= | | |
SORT | ORDER BY | |
NESTED LOOPS | | |
TABLE ACCESS | FULL | MON_TAB_STATS |
TABLE ACCESS | BY INDEX ROWID | MON_TAB_STATS |
AND-EQUAL | | |
INDEX | RANGE SCAN | MON_TAB_STATS_DB_NAM |
| | E_IDX |
INDEX | RANGE SCAN | MON_TAB_STATS_TAB_IDX |
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.