SQL Selector Workflow: Plan Control
The SQL Selector workflow allows you to choose original SQL from a batch of SQL statements.
To select original SQL using SQL Selector for Plan Control
- Select the Optimize SQL tab in the main window.
- Select Plan Control from the Optimize SQL start page.
-
Enter SQL statements in the Alternative Details pane of the SQL Details tab.
- Click an icon to perform an operation.
-
Select a connection.
-
The SQL Selector window displays your SQL statements.
The SQL list displays in the control grid on the left. When you select one of the SQL statements, the corresponding SQL text is highlighted in the right grid.
The buttons on the user interface change accordingly if the selected SQL statement has a variable(s) or temp table(s). Review the following for additional information:
If the SQL statement… the user interface displays… Does not contain a variable » Click the OK button to use the selected SQL as the original SQL statement. Contains a variable(s) To enter bind variable values
- Click the Enter Bind Variables button.
- Click OKto use the selected SQL as the original SQL statement.
Note: Click
to return to the SQL list to select a different SQL as the original SQL statement.
Contains a temp table(s) To create temp tables
-
Click the Create Temp Tables button to create the relevant temp table(s).
Note: If the SQL script for creating temp table(s) has variable(s), the Enter Bind Variables window displays.
- Click the OK button to use the selected SQL as the original SQL statement.
Note: After you select the original SQL statement, the whole text is kept as the Batch Scope for a plan guide.
View example
Execution Plans
The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.
Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.
SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.
The annotated execution plan includes the following information for each step:
-
Execution order number
-
Join syntax (annotated)
-
Filter syntax (annotated)
-
Object name
-
Table access
-
Index scan
-
Cost
-
Partition name
Execution Plan Window
The Execution Plan window contains an Estimated Plan tab and an Actual Plan tab.
Estimated Plan tab
The Estimated Plan is generated by the SHOWPLAN command.
Actual Plan tab
The Actual Plan is retrieved after executing SQL and includes real statistics such as Actual Rows, Actual Rebinds and Actual Rewinds that are not in an estimated plan.
About Execution Plans
The execution plan displays the steps a database takes to execute a SQL statement. You can use the execution plan to determine if a statement is efficient.
Each step of the tree indicates how SQL Optimizer retrieves rows of data. The first line of the execution plan displays the SQL statement type, such as SELECT. The remaining lines represent an operation. The operations are numbered in the order of execution to make the plan easier to read.
SQL Optimizer executes each child operation before the parent operation. For some SQL statements, SQL Optimizer executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that SQL Optimizer retrieve all rows from the child operation before it executes the parent operation.
The annotated execution plan includes the following information for each step:
-
Execution order number
-
Join syntax (annotated)
-
Filter syntax (annotated)
-
Object name
-
Table access
-
Index scan
-
Cost
-
Partition name
Execution Plan Window
The Execution Plan window contains an Estimated Plan tab and an Actual Plan tab.
Estimated Plan tab
The Estimated Plan is generated by the SHOWPLAN command.
Actual Plan tab
The Actual Plan is retrieved after executing SQL and includes real statistics such as Actual Rows, Actual Rebinds and Actual Rewinds that are not in an estimated plan.
Get Execution Plans
SQL Optimizer automatically retrieves the execution plan when you optimize or execute a SQL statement. The Execution Plan window contains an Estimated Plan tab and an Actual Plan tab.
Note: When you generate virtual index alternatives, the execution plan is not retrieved for the SQL statement because SQL Optimizer does not physically create indexes on the database. See Fill Missing Estimated Plans for more information.
Estimated Plan
The Estimated Plan is generated by the SHOWPLAN command.
To get an estimated plan
- Enter a SQL statement in the Alternative Details pane.
-
Do one of the following:
- Click
.
- Select the Estimated Plan tab and click Get Estimated Plan.
Actual Plan
The Actual Plan is retrieved after executing SQL and includes real statistics such as Actual Rows, Actual Rebinds and Actual Rewinds that are not in an estimated plan.
To get an actual plan
| » | To retrieve an Actual Plan, do one of the following: |
Execute the selected SQL statement or plan alternative.
For the selected statement or alternative, select the Actual Plan tab and click Test Run - Current.
Tips for both Estimated and Actual Plans:
- To select which columns to display in the execution plan, right-click the plan and select Plan Options. The settings will apply to the current module only
- To change the font style and color, as well as the highlighting color, for the execution plans in all modules, select Options | General | Execution Plan See. Execution Plan Options for more information.
-
Use the Plan toolbar (right side of pane in tree view) to select from several options that display different plan details (operators, predicates, statistics).
Note: The Actual Plan tab displays and additional button at the top of the Plan toolbar. Click the Plan Comparison button to display a comparison of estimates and actual statistics.
Execution Plan Actions
Right-click in the Execution Plan window to select from the following actions:
| Action | Description |
| Copy | Copies the execution plan to the clipboard. |
| Save | Saves the execution plan as a JPG file. |
| Opens the print window so you can print the execution plan. | |
| Style |
Select a style type for displaying the plan. |
| View Plan | Allows you to change how the execution plan is displayed. |
| Step Description | Select this option to display a description of the step selected in the execution plan. |
| Plan Information | If you select this option, SQL Optimizer identifies whether an execution plan uses any of the following: cardinality feedback, dynamic sampling, SQL plan directives, or adaptive plan. If applicable, the information displays in the Execution Plan pane directly above the Execution Plan tab. |
| Animate Plan Steps | Highlights, one-by-one, the execution plan steps. |
| View Missing Indexes | Create a user-defined alternative. |
| Generate Indexes | Explore different index alternatives. |
| Plan Options | Opens the Execution Plan Options window so you can select which information is displayed in the execution plan and whether to display specific items in a column. |
| Help on SELECT | Displays definition of SELECT operator. |
| Help on Execution Plan | Opens online help for the Execution Plan window. |