How to Shorten a Long Optimization Time

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 .
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.