Toad World Blog

How to generate SQL alternatives without Oracle hints

May 10, 2013 11:00:00 PM by Steve Hilker

Oracle provides optimization hint that can be added to the syntax of a SQL statements to attempt to influence the execution plan that the database optimizer will use to execute the SQL statement. Quest SQL Optimizer for Oracle uses this technique in its optimization process to generate more unique execution plans for your original SQL statement.

Basically, using Oracle hints to tune SQL is an acceptable method that makes your SQL statements to be more stable in the production database. Some people may argue that using hints should be the last resort or even be forbidden for tuning SQL, since hints will limit the flexibility of the Oracle SQL optimizer to handle future data changes or adapt to new optimizer enhancements. But the point is that for a problematic SQL statement that Oracle cannot handle well today, why not fix it today with the best execution plan even if it uses a hint instead of holding to the expectation that the Oracle optimizer may automatically solve it in future. What this means is that you are sacrificing the current performance for the unforeseeable future improvement or maybe even a further performance degradation. If you do not want any of the Oracle hints applied to the SQL alternatives that are generated for your original SQL statement, you can use an option setting to turn off this feature.

  1. Click TT_Optimization_GeneratingMoreAlternatives_Pic1.png-550x0.

  2. Select Tuning Lab | Optimizer | Intelligence.

  3. Select Predefined Settings.

  4. In the list, select Do not use Oracle optimization hints.    

TT_Optimization_OptimizingWithoutHints_Pic2.png-550x0

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.