Toad World Blog

Criteria for Terminating SQL in the Batch Run

May 10, 2013 11:00:00 PM by Steve Hilker

This tips covers the options for terminating the SQL alternatives when they are executed in the Batch Run in the Optimize SQL module. The SQL optimization process in the Optimize SQL module generates several alternative SQL statements that produce the equivalent results to your original SQL statement. You are obviously looking for the SQL alternatives that run faster than your original SQL statement. Most likely, some of the alternative SQL statements Quest SQL Optimizer will run longer than your original statement and some will run faster.

When testing the performance of the alternatives, you will undoubtedly want to terminate the longer running SQL statements, once you have determined that they do not meet your performance criteria so that you do not waste time waiting for them to finish executing. You can do this using the Option settings in the Optimize SQL | Optimizer | Batch Run | Order and Termination | SQL Termination Criteria section.

When a SQL statement is terminated by the criteria that you select, ?Terminated by criteria ?is displayed in the Status column of the Alternatives window to let you know that the SQL statement exceeded the termination time specified in the options and did not run to completion.

In Quest SQL Optimizer 7.4 for Oracle or later, these three options are provided for choosing when to terminate the SQL alternatives.  

TT_ExecutingSQL_CriteriaForTerminatingSQL_Pic1.png-550x0

The Run time of fastest SQL 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 for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time for the next SQL statement. So, you are always using the fastest run time that has been found so far as the termination time for the next SQL statement. This option is the default setting.

The This percentage of the original SQL run time option cancels SQL statements whose total elapsed time is longer than the specified percentage 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.

The User defined time option cancels SQL statements that run longer than a time you specify. The option can be useful in a couple of ways. For example, if you know that your original SQL statement runs in half an hour, you can select to not run the original statement and set the termination time for 30 minutes, thereby saving yourself a half hour of testing time.

Another example for using the user defined time to cancel the SQL statements is 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 you find one that executes that quickly, you will certainly save yourself considerable time in testing. If no alternative statements execute in under 5 minutes, raise the termination time to 10 minutes, etc.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.