Review Alternatives
Use the Alternatives pane to review the SQL text and execution plan for the alternatives SQL Optimizer generates.
You can also review the run-time statistics for your original SQL statement and the alternatives after you test run the statements.
To review details for an alternative
- Select the Optimize SQL tab in the main window.
-
Select an alternative in the Alternatives pane.
-
Review details for the alternative in the Alternative Details and Execution Plan panes.
Note: You must test run the SQL alternatives before you can review the run-time statistics. See Test Run SQL Alternatives for more information.
Alternative SQL Text
The text editor is used to display the SQL text of the selected alternative. When there’s only original alternative or the selected alternative is user defined, the SQL text can be edited. Otherwise, it’s read-only.
View SQL Information (SQL Rewrite)
This topic may not include a description for every field in the screen or dialog.To view SQL information
- Select the Optimize SQL tab in the main window.
-
Select a SQL Rewrite session.
-
Select the SQL Information tab in the Optimize SQL window. Click the following to view the information that displays for each pane.
SQL Information Description Classification
Statement type as classified by SQL Optimizer. Types include Simple, Complex, or Problematic. Classification Rules Detail Description Details
Applicable SQL Classification Rules. Rules used for the classification process are set on the SQL Classification Options page. See SQL Classification Options for more information. Note: SQL Classification information only displays for the original SQL statement.
Optimization Details Description Intelligence Level
Intelligence level used for optimization. This option is set on the Optimizer Intelligence options page. See Intelligence Options (Optimization) for more information. Semantically equivalent SQL statements
Number of semantically equivalent SQL statements generated by SQL Optimizer. Alternative execution plans produced
Number of alternative execution plans produced for the original SQL statement. SQL statements eliminated due to identical execution plans
Number of statement alternatives eliminated with the same execution plan as the original SQL statement.
Note: SQL statements with the same execution plan produce the same run time because the database executes these statements in the same way. Therefore, SQL Optimizer eliminates alternatives with equivalent execution plans.
Optimization Time Details Description Optimization started at
Time optimization started. Optimization finished at
Time optimization finished. Total optimization time
Total time to complete optimization. Average optimization time
Average time to generate each statement alternative. Notes:
- This information is only available after you generate SQL alternatives.
- Optimization details only display for the original SQL statement.
Index Details Description Intelligence level
Intelligence level used for index generation. This option is set in the Index Generation Intelligence options menu. See Intelligence Options (Index Generation) for more information. Indexes have been generated
Number of indexes generated. Index sets were composed to generate different execution plans
Number of index sets produced to generate alternative execution plans. Index sets eliminated due to identical execution plans
Number of index sets eliminated because Database Engine Tuning Advisor information indicated they are not used by the original SQL statement. Indexes have been used in index sets
Number of indexes used in the selected index set. Index Time Details Description Index started at
Time index generation started. Index finished at
Time index generation finished. Total index time
Total time to complete index generation. Average index time
Average time to generate each index set. Note: This information is only available after you generate index alternatives.
Alert Details Alert messages about one or more of the following:
- Errors SQL Optimizer encountered during the optimization process.
- SQL classification details.
Note: This pane is only available if SQL Optimizer generates alert messages.
Classification
View Execution Statistics (SQL Rewrite)
You can view execution statistics in the Alternatives pane or in the Execution Statistics tab.
To view execution statistics in the Alternatives pane
- To sort alternatives by an execution statistic, click a column header in the Alternatives grid.
- To change the Optimization Goal, click the Execution Elapsed Time column header and select another goal from the drop-down list.
To view Execution Statistics tab
- Select the Optimize SQL tab in the main window.
- Select Rewrite SQL from the Optimize SQL start pages.
-
Select the Execution Statistics tab in the SQL Rewrite window.
-
Review the following for additional information:
Statistics Name Description Execution Time Shows CPU time and elapsed time required to execute the SQL statement after SQL Optimizer complies it. Compile Time Shows CPU time and elapsed time required to compile the SQL statement. IO Statistics Shows run time scan count, logical reads, physical reads, and read-ahead reads information recorded during execution of the SQL statement.
Also displays information for each table used during execution.
Note: This information is only available after you execute the SQL statement.