SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. You can then test run the alternatives to benchmark their performance. SQL Optimizer provides execution times and run-time statistics for each alternative to help you identify the best SQL statement for your database environment.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To optimize a SQL statement
Select a connection to use. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Schema and SQLID | Description |
Schema |
Click |
SQLID |
Click |
When SQL Optimizer is finished generating alternatives, use one of the following methods to test run the SQL alternatives:
To test run all SQL alternatives, click
.
- To test run a single SQL alternative, select the alternative and click
.
To test run two or more alternatives simultaneously, select the alternatives, click the arrow beside
and select Test Run - Selected.
The execution statistics display in the Alternatives pane once the test run completes. Select an alternative to see more information in the Alternative Details and Execution Plan panes.
Tips:
To clear the SQL Rewrite window
To clear only the results and retain the original SQL statement, right-click within the Alternative Details pane and select Clear Optimization Results | Keep Original Scenario.
To clear the SQL Rewrite window, right-click within the Alternative Details pane and select Clear Optimization Results | Clear Original Scenario.
Use the Session Test Run Criteria dialog to specify options to use for the current test run of SQL statements in the Optimize SQL module. The options you specify are used for the current test run only. Global test run options for Optimize SQL are modified at Options | Optimize SQL | Test Run.
Select from the following options:
Execution Method | Description |
Maximum rows to be retrieved (records) |
Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records. |
Run Time Retrieval Method | Description |
Run SQL options |
Select one of the following options:
|
Select from the following options:
Execution Order | Description |
Execution order for SQL |
Select one of the following options:
|
SQL Termination Criteria | Description |
Terminate execution of SQL alternative if it runs longer than |
Select one of the following options:
|
Cancellation delay (seconds) |
Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server. |
Execute Criteria | Description |
Execute all alternatives with criteria |
Select one of the following:
|
Tips:
Use the Auto Optimize function to perform the optimization and testing processes simultaneously. The function optimizes your original SQL statement by generating alternatives and starts testing once SQL Optimizer generates the first alternative. The Auto Optimize function reduces the time required to find the best alternative by not waiting until SQL Optimizer generates all alternatives before starting the testing process. You can stop the Auto Optimize function once you find a satisfactory SQL statement alternative.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To automatically optimize a SQL statement
Click .
Tip: Click to stop the optimization and testing processes.
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.
The database executes each child operation before the parent operation. For some SQL statements, the database executes the parent operation once it retrieves a single row from the child operation. Other SQL statements require that the database retrieve all rows from the child operation before it executes the parent operation.
The Execution Plan pane shows types of information for the original SQL or the SQL associated with the currently selected alternative.
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 option to display different plan details (operators, predicates, statistics). |
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. |
Animate Plan Steps | Highlights, one-by-one, the execution plan steps. |
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 RETURN | Displays the help text for the currently selected operation in the execution plan. |
Help on Execution Plan | Opens online help for the Execution Plan window. |
© ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center