I am using a trial version of Optimizer. After loading the SQL and running Optimize, I get all the scenarios. Then I run Execute All. The lower left part of the screen showed each Alt as it processed. At the end it said All processing complete. I click on Compare Scenarios and Execution Statistics both, and the “Results Comparison” column on those screens both say “Query Cancelled. Exceed benchmark.”
What is wrong?
Hi,
Since the SQL being executed exceeds the SQL termination criteria, then it was killed and stopped. It is correct that the SQL is displayed with that information in the “Results Comparison” column in the Compare Secnarios and Execution Statistics window. It doesn’t mean the SQL executed with error.
Actually, the SQL termination criteria could be changed. Simply go to options window, then click on the node “Execution” under Tuning Lab.
Regards
Cliff
Hello,
The SQL Optimizer generates all the alternative SQL statements that produce the equivalent results to your SQL statement. These statements may run faster than your original SQL statement. They also may run longer than your statement. When testing the alternatives, you will mostly want to terminate the longer running SQL statements. This is done using the option setting in the Tuning Lab | Execution | Execution Criteria | SQL Termination Criteria section. When a SQL statement is terminated by the criteria that you select, “Query Cancelled. Exceed benchmark” is displayed to let you know that the SQL statement exceeded the termination time specified in the options.
In the upcoming release of Quest SQL Optimizer 7.4 for Oracle, we have added two new criteria for terminating the execution of the SQL alternatives. The Cancel execution by the fastest SQL run time option cancels SQL statements that run longer than the current best run time. With this option, your original 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. This new option is the default setting.
The other new option, Cancel execution by the user defined time, cancels SQL statements that run longer than a specified 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.
The original option, Cancel execution at 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 past the calculated termination time. The default value is set at 100% so a SQL alternative will be terminated if it runs longer than your original statement.
Rene