Selected SQL
View Batch Run Criteria Window - Selected SQL
The Selected SQL tab is used to select or unselect SQL statements to be executed. All SQL statements are selected by default. The selected SQL statements are displayed in blue. You can select or de-select SQL statements you want to execute by clicking a row. The 
Selecting or unselecting all SQL statements
To select or unselect all the SQL statements
-
Right-click and select Unselect All or Select All.
The original SQL can be de-selected from the list only if the Original SQL checkbox is not selected on the SQL Termination and Batch Termination tab and the Always run Original SQL first option in the Selected Index tab is not selected.
Changing the order the SQL statements are executed
The SQL statements are ranked by Est. I/O Cost by default, with the exception for the original SQL which is placed at the top. You can sort either the Est. I/O column or the SQL column by clicking the column heading.
You can change the order of any SQL statement, by clicking the row and then click 
Always run Original SQL first
Specify to always run the original SQL statement first despite sort order of the Est. I/O cost. This checkbox is dimmed if the Original SQL option is selected in the Termination Criteria or Batch Termination tab of the Batch Run Criteria window.
Apply SQL selection filter
The SQL selection filter unselects the SQL statements that have an Estimated I/O cost greater that a specified value. When you select this option, 
SQL Termination (Options)
The SQL Optimizer finds all the alternative SQL statements that produce the equivalent results to your SQL. These statements may run faster than your source. They also may run longer than your source. Therefore, you will want to terminate the longer running SQL statements. The SQL Termination page is used to set the termination criteria for each SQL statement while retrieving the run time. If the current run time for a particular SQL statement exceeds the termination time, that SQL statement is terminated automatically.
Note: The termination time has a percentage delay added to it.
To define your termination criteria select one of the following options.
Original SQL
The Original SQL option enables you to retrieve the run time of SQL statements that run faster than the original SQL statement. It terminates all SQL statements that run longer than the run time from the original SQL. If you choose this option, the original SQL statement is automatically selected on the Selected SQL tab and during the Batch Run, you cannot terminate the original SQL because its run time is needed to determine when to terminate the SQL alternatives.
Best running time SQL
The Best Running Time SQL option allows you to retrieve the run time of SQL statements that run faster than the current best run time. With this option, the first SQL statement is run and the time from that statement is used as the termination time. When a SQL statement runs faster than this time, the faster time is used as the new termination time. So you are always using the current fastest run time as the termination time for the next SQL statement.
Note: The first SQL statement is either the original or the SQL with the lowest Estimated I/O cost. This depends on whether the Always run Original SQL first option is checked on the Selected SQL tab.
User-defined time
The User Defined Time option retrieves the run time of the SQL statements that are less than the defined time. If your original statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user defined termination time. If the original SQL takes 1 hour, try a 5-minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc.
Combining Criteria
You can also combine User Defined Timewith Original SQLor Best Running Time SQL by clicking the Or user-defined time checkbox next to each one.
Run without termination
The Run without termination option allows you to retrieve the run time of the SQL statements without any termination criteria. All SQL statements run to completion.
SQL Termination (Percentage Delay)
The percentage delay calculation adds additional time to the termination time. It is used to account for the time it takes the SQL statement to travel from the PC to the database server over the network and to find all the SQL statements that terminate very close the fastest SQL statement.
Percentage delay (%) - Range 1 - 200 (Default: 5)
The value entered into this field is used as a percentage to calculate the additional time that is added to the termination time. For example, if the termination time is 10 minutes and the percentage delay is 5%, then all SQL statements executed are terminated if the run time exceeds 10.5 minutes. (10+ (10 * 5%))
Minimum delay time (secs) - Range 2 - 99 (Default: 5)
This is the minimum number of seconds that is added to the termination time. It is necessary to factor into the overall termination time the time needed for the SQL statement to be sent to the database server from the PC before it starts to run. This number is only used if the Percentage delay calculation is lower than this value.
Maximum delay time (mins) - Range 1 - 9999 (Default 60)
This is the maximum number of minutes that can be added to the termination time. This number is only used if the Percentage delay calculation is higher than this value.
Batch Termination
The Batch Termination page of the Batch Run Criteria window is used to determine if and when to terminate the Batch Run. It enables you to find alternative SQL statements that give you good performance improvement without having to execute every SQL alternative.
No termination
Specify to run the Batch Run to completion.
Terminate Batch Run if the specified number of SQL falls in the criteria.
Specify to terminate the Batch Run when a specified number of SQL statements are found that meet the following requirements for terminating the Batch Run.
Number of SQL (excluding the Source)
Specify how many SQL statements must be found that a show performance improvement over the original SQL statement.
Count the SQL if its elapsed time is faster than
Specify one of the following criteria to determine how the performance improvement is determined.
Original SQL
Count all SQL statements that run faster than the run time from the original SQL.
Original SQL with a percentage of improvement
Count all SQL statements where the run time for the alternative SQL statement is the specified percentage faster than time for the original SQL statement.
User-defined time (mins/secs)
Count all SQL statements that run faster than a specified number of minutes and/or seconds.