Review Plans
Use the Plans pane to review the SQL text and execution plan for the plans SQL Optimizer generates. You can also review the run time statistics for your original SQL statement and the plans after you execute the plan alternatives.
To review details for a plan
- Select the Optimize SQL tab in the main window.
- Select a Plan Control session.
- Select an execution plan alternative in the Plans pane.
-
Review the details of the alternative plan in the Execution Plan pane.
Note: You must execute the alternative plans before you can review run time statistics. See Test Run Execution Plan Alternatives for more information.
View Execution Statistics (Plan Control)
Compare Execution Plan Alternatives
Original SQL Text
The text editor is used to display the SQL text of the selected plan guide. It can be edited when there is only an original plan. When the plan is user defined, you can change the plan by clicking the hyperlink to add query hints and table hints.
View SQL Information (Plan Control)
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 Plan Control 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.
Plan Generation Details Description
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 because they had the same execution plan as the original SQL statement.
Note: SQL statements with the same execution plan have the same run time because the database executes these statements in the same way. SQL Optimizer eliminates these alternatives.
Plan Generation Time Details Description Plan generation started at Time plan generation started. Plan generation finished at Time plan generation finished. Total plan generation time Total time to complete plan generation. Average plan generation time Average time to generate each plan alternative. Notes:
- This information is only available after you generate plan alternatives.
- Plan generation details only display for the original SQL statement.
Classification
About Optimizing SQL (Plan Control)
View Execution Statistics (Plan Control)
View Execution Statistics (Plan Control)
You can view execution statistics in the Plans pane or in the Execution Statistics tab.
To view execution statistics in the Plans pane
- To sort alternatives by an execution statistic, click a column header in the Plans 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
- Select the Optimize SQL tab in the main window.
-
Select Plan Control from the Optimize SQL start page.
- Select the Execution Statistics tab in the Plan Control 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.