Written by Rene Woody
This blog is a continuation of a series about the Batch Optimizer in Quest SQL Optimizer for Oracle. It covers the execution options settings for determining the criteria for selecting the best alternative, for terminating longer running SQL, and eliminating the effect of caching on the comparison run times.
Selecting the Best Alternative
The best alternative is selected based on the setting selected in the Best alternative selected based on lowest option. This is either the fastest Total elapsed time or the fastest First row elapsed time. The Total elapsed time is how long it takes to retrieve or process all the records. The First row elapsed time is how long it takes to retrieve or process the first record.
Termination Criteria
In testing of the SQL alternatives, the goal is to find a SQL statement that runs faster than the original. So as soon as it is determined that a SQL alternative is not the best alternative, it should be terminated to minimize the time it takes for testing.
The Terminate execution of SQL alternative if it runs longer than option provides three settings for determining when to terminate the execution of a SQL statement.
Run time of fastest SQL is the default setting. With this option, the original SQL statement is executed first 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 termination time, the faster time is used as the new termination time. So you are always using the fastest run time as the termination time for the next SQL statement.
Run time of original SQL terminates a SQL alternative if it runs longer than the original SQL statement.
User defined time lets you set your own termination time. It first retrieves the run time of the original SQL statement. When the SQL alternatives are executed, they are terminated if they reach the specified time.
Accounting for Caching
Caching the data, the indexes, and the SQL statement can affect the comparison times if one SQL statement has to do some caching and others do not. Therefore, options are available so that you can select the one that provides you with the most accurate comparison of the run times in your environment.
The Retrieving run time executing optionhas these settings:
Original SQL twice using second run time and all others once
The first time you access data from a table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory. Therefore, the first SQL statement executed will have the additional time it takes to cache the data included in the run time. Whereas, the SQL statements that follow do not have the additional time included in their run time. So to have a comparable test, the first SQL is run twice and the time from the second run is compared to the time from the other statements which are run once. In this case, all SQL statements are executed with the data cached.
All SQL twice using the second run time
For fast running SQL statements, executing all SQL statements twice and using only the second run time enables you to eliminate two additional caching factors that can affect the accuracy of the comparison results: caching the SQL statement and caching the indexes. If some SQL statements have been recently executed, then the SQL information for those statements is likely to be resident in the cache and the statements may execute faster because the SQL statement does not need to be cached. Also, if some of the SQL statements use different indexes, one index may be resident in the cache and the other may not be in the cache. So running all the SQL statements twice ensures that each index is cached.
All SQL once
For long running SQL statements, there is no need to run any statement twice since the effect from caching is diminished over time.