Feb
13
Written by:
Richard To
Friday, February 13, 2009 6:16 AM
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 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
- On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
- Select Custom Settings.
- 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.
- On the Tuning Lab | Optimizer | Intelligence page, select Intelligence Level 4 using the slider.
- Select Predefined.
- 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 Settings and only apply a few hints that you choose.
