Specify Session Test Run Criteria
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.
Execution Method and Run Time
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:
- Run all SQL twice if original
SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and
the All SQL twice options into one option and allows you to determine
(by the number of seconds a SQL statement runs) which option to use.
The original SQL statement always runs twice. The SQL alternatives
run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
- Original SQL twice and all others
once—Caches data from a table into memory the first time
you access it. The next time you
access that data, it is already in memory so the following SQL statements
run faster. To provide an accurate comparison, the first
SQL statement runs twice but only the time from the second run is compared to the times
for the other statements.
- All SQL twice—Executes all SQL statements twice to eliminate factors that can affect the accuracy of the results. If
you recently executed a SQL statement, the information
for that statement may be cached and the statement
may execute faster. This option eliminates time variation caused by caching
since it runs all SQL statements twice but only uses the second run time for comparison.
- All SQL once—Executes all SQL statements once. For long running SQL, you do not need to run any statement
twice since the effect from caching diminishes over time.
|
Order and Termination
Select from the following options:
| Execution Order |
Description |
|
Execution order for SQL
|
Select one of the following options:
- Intelligent order—Executes representative SQL statements with various plan cost according to SQL Optimizer's intelligence engine.
- Plan cost—Executes SQL statements in order of plan cost.
|
| SQL Termination Criteria |
Description |
|
Terminate execution of SQL alternative if it runs longer than
|
Select one of the following options:
- Run time of fastest SQL—Cancels SQL statements that run longer than the current fastest
run time. With this option, the first SQL statement runs and the time
from that statement is used as the termination time for the next SQL statement.
When a SQL statement runs faster than this time, the faster time is used
as the new termination time.
- This percentage
of the original SQL run time—Cancels SQL statements whose total elapsed time is the specified
% of the total elapsed time for the original SQL statement. It terminates
all SQL statements that run longer than the calculated termination time.
- User defined time (mins/secs)—Cancels SQL statements that run longer than a time you specify.
- Run without termination—Runs all SQL statements to completion regardless of run time.
|
|
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.
|
SQL to Execute
| Execute Criteria |
Description |
|
Execute all alternatives with criteria
|
Select one of the following:
-
% of alternatives with lowest cost—Enter the percentage of SQL alternatives to execute with the lowest cost. You can also enter the minimum and maximum number of alternatives to execute.
-
Number of alternatives with lowest cost—Enter the number of SQL alternatives to execute with the lowest cost.
-
All alternatives with cost less than or equal to original SQL—Select to execute all SQL alternatives with a cost less than or equal to the cost of the original SQL statement.
-
All alternatives with cost less than the average of all alternatives—Select to execute all SQL alternatives with a cost less than the average cost of all alternatives.
-
All alternatives with cost less than the original SQL by percentage—Enter a percentage used to determine the SQL alternatives selected for execution. Optimize SQL executes alternatives with a cost that is the specified percentage lower than the cost of the original statement.
-
All alternatives with cost less than the original SQL by N times—Enter a value for N used to determine the SQL alternatives selected for execution. Optimize SQL executes alternatives with a cost N times lower than the original SQL statement.
-
All alternatives—Select to execute all SQL alternatives.
|
Tips:
- Click
to stop the test run process.
- You can select an executing SQL statement and click
to abort only the selected statement.
Related Topics
About Quest SQL Optimizer for IBM DB2 z/OS
Open Optimizer SQL Sessions
Execution Method Options
Order and Termination Options