May 10, 2013 11:00:00 PM by Steve Hilker
When you are optimizing a complicated SQL statement, Quest SQL Optimizer is capable of generating hundred to even thousands of SQL alternatives. While theoretical this may be nice, it is not practical to review, select, and test hundreds of SQL statements, especially if it is a long running SQL statement. This tip covers how to limit the number of SQL alternatives generated by the optimization process.
The options settings in Quest SQL Optimizer for Oracle enable you to control the maximum number of SQL alternatives generated, select which Oracle optimization hints are applied, exclude a few of the transformation rules that are used, and limit the maximum number of SQL statements generated.
We’ll take a look at how you can do this using the Optimizer options. These options apply to the optimization process in both the Tuning Lab and the Batch Optimizer.
To open the Options window, click Options.
Select Tuning Lab | Optimizer | Intelligence.
By default, the “intelligence level” of the optimization process is set to Predefined Settings with level 4 selected. In order to customize the number of alternative statements that are generated and other options, set the intelligence level to Custom Settings.
Tuning Lab | Optimizer | Optimization.
If the Transform view to inline view or Transform query to inline view apply to your SQL statement, fewer SQL alternatives will be generated if you unselect these options.
The Transform view to inline view option inserts the SELECT statement from each VIEW used in the SQL statement and then transforms the syntax of the original SQL statement and the SQL statements from the VIEWs.
The Transform query to inline view option transforms the subquery to an inline view (a subquery used as a table in a FROM clause).
Select Tuning Lab | Optimizer | Hints.
The Oracle optimization hints are displayed on six pages. Select the hints you want to use for optimizing your SQL statements. The fewer hints selected the fewer alternative SQL statements will be generated by the SQL Optimizer.
Select Tuning Lab | Optimizer | Quota.
Set the quotas to reduce the number of SQL statements generated during the optimization process.
The Syntax transformation quota limits the number of SQL alternatives generated during the SQL syntax transformation phase of the optimization process. What this means is that the SQL Optimizer will stop finding syntactically different SQL statements after the quota has been reached.
Note: Although the SQL statements are syntactically different, they still produce the same result. The Table join permutation quota limits the number of ways the order of the tables is rearranged. This table order rearrangement is actually one of the many rules that comprise the syntax transformation rules.
The Table join permutation quota is used to control how many SQL syntax transformations are created using only this one rule.
The Hints quota is the number of SQL alternatives that are generated by applying the Oracle optimization hints to the original SQL statement and the SQL alternatives that were created by transforming the SQL syntax.
By changing the options above, fewer or more SQL alternatives can be generated for your original SQL statement.
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.