Written by Rene Woody
 
This blog is a continuation of a series about the SQL optimization process in the Batch Optimizer and the Tuning Lab modules of Quest SQL Optimizer for Oracle.  It explains how to generate SQL alternatives that do not have Oracle optimization hints.

Oracle provides optimization hints 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. For more information on how the SQL alternatives are generated, see this previous blog: Optimizing SQL Part 1 – The Optimization Process.

To learn more about how the hints are applied by Quest SQL Optimizer see this previous blog: Optimizing SQL Part 6 – Using Oracle Optimization Hints when tuning SQL. 

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 would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.