This tip sheds some insight on why the SQL optimization process can take hours to run and what you can do to shorten the process.


SQL Statement with Views

When the SQL statement is using Views instead of Tables the optimization process has an option for rewriting the SELECT statement from the View as well as your original SQL statement. Say you are using three Views in your SQL statement. The optimization process would then rewrite four SQL statements (the original and all three views). You can see how this would add a lot of time to the optimization process particularly if any of the views are quite complex.

To have the optimization skip this process

  1. To open the Options window, click Options TT_Optimization_GeneratingMoreAlternatives_Pic1.png-550x0.
  2. On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
  3. Select Custom Settings.
  4. On the Tuning Lab |Optimizer | Optimization page, clear Transform view to inline view.



Only Transform SQL Syntax

You can also specify to have the optimization process just transform the syntax of the SQL statement and not apply any of the Oracle optimization hints.

  1. On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
  2. Select Predefined.
  3. In the list next to Predefined, select Do not use Oracle optimization hints. 
    Note: You can select any of the other options which only applies a few of the hints. Or you can select the Custom Settingsand only apply a few hints that you choose.


About the Author

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.

Start the discussion at