First page Back Continue Last page Graphics

USE_NL Hint

Notes:

Hints, as we learned earlier in this course, can be used to override CBO decisions. For example, let’s assume that your server is providing a hash join for a query, and you would like to see the effect of a nested loops join. Instead of altering the session optimizer mode (as demonstrated earlier in this lesson), you could apply the USE_NL hint as shown above.
The Oracle Database Performance and Tuning Guide says the table specified within the parenthesis will be the “inner” table, i.e. the driven table in the join (BIG, in this example). I found that:
BIG had to be included to obtain the nested loops join (if only SMALL was coded, a hash was used).
The order of tables specified within the parenthesis had no affect. Use the ORDERED hint to control the driver. Regarding performance, with SMALL as the driver, I experienced ~84,000 I/O’s and 2.30 seconds elapsed time. However, as shown below, using the ORDERED hint to force BIG as the driver negatively impacted performance.

Notes for this slide continue on the next page…