SQL Tuning   «Prev  Next»

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
OPERATIONOPTIONSOBJECT_NAME
SELECT STATEMENT Cost=  
SORTORDER BY 
NESTED LOOPS  
TABLE ACCESSFULLMON_TAB_STATS
TABLE ACCESSBY INDEX ROWIDMON_TAB_STATS
AND-EQUAL  
INDEXRANGE SCANMON_TAB_STATS_DB_NAM
  E_IDX
INDEXRANGE SCANMON_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.