Apr
17
Written by:
Richard To
Friday, April 17, 2009 2:37 AM
Written by Rene Woody
This blog is the first of a series about test running the SQL statements in the Tuning Lab in Quest SQL Optimizer for Oracle to find the best performing SQL statement in your database environment. It covers the options for terminating the SQL alternatives when they are executed in a batch.
The SQL optimization process in the Tuning Lab generates multiple 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 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. You can do this using the Option settings 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 in the Results Comparison column of the Scenario Explorer window to let you know that the SQL statement exceeded the termination time specified in the options.
In Quest SQL Optimizer 7.4 for Oracle or later,these three options are provided for choosing when to terminate 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 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 Cancel execution at 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 using this default value.
The Cancel execution by 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.