Written by Rene Woody

This blog is a continuation 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. When you are executing SQL statements to find which one is the best, you need to take into consideration the factors that can skew the accuracy of the results of the testing. These factors include caching the data, caching the indexes, caching the SQL statement, other activities on the CPU, and network traffic. Quest SQL Optimizer provides option settings that allow you to minimize these factors so that you can find the SQL statement that is best for your database environment. This blog discusses minimizing the effect of caching in order to have the most accurate run time comparisons. The next two blogs will discuss how to minimize the effect of other activities on the CPU and network traffic.

When you are testing the SQL statements, the time that is best to compare is how long the SQL statement is going to take to execute on the CPU. So, it is best to try to eliminate other factors that may add time to the execution time when you are comparing the run time of the execution of one SQL statement to another.

The elapsed run time in Quest SQL Optimizer is calculated from the time the SQL statement starts to run on the CPU until the time it finishes.

Caching the Data, Indexes and SQL Statements

Caching the data, the indexes, and the SQL statement can affect the comparison times if one SQL statement does the caching and others do not. The following options are available for retrieving the run time so that you can select the one that provides you with the most accurate comparison of the run times.

Option: 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 from the disk. This process takes a few moments. The next time you access that data, it is already in memory so accessing the data is faster for the following SQL statements. Therefore, the first SQL statement will have the additional time it takes to cache the data included in the run time whereas the following SQL statements do not have that additional time included in their run times. So, to have a comparable test, the Original SQL twice using second run time and all others once runs the first SQL twice. The time from the first execution is ignored and the time from the second run is compared to the time from the other statements, which are all run once. With this option, all SQL statements are executed with the data cached. This is the option that is selected by default.

Option: All SQL twice using the second run time

For fast running SQL statements, the All SQL twice using the second run time option is a good choice as it executes all SQL statements twice, which enables you to eliminate two additional factors that can affect the accuracy of the comparison run times: caching, parsing, and optimizing the SQL statement and caching the additional indexes used by the SQL statements. If a SQL statement has been recently executed, then the SQL information for that statement is likely to be resident in the cache and the statement should execute a little faster because the SQL statement does not need to be parsed and optimized. Also, if some of the SQL statements use different indexes, one index may be resident in the cache and another may not. The additional time for caching an index will be added to the run time.

This option eliminates time variation caused by additional overheads since it runs all SQL statements twice, so, you get a more accurate comparison of the run time of each alternative SQL statement because you are comparing the actual time it takes to execute on the CPU. This setting is especially suitable for short runtime SQL such as SQL executions of less than 3 seconds.

Option: All SQL once

For long running SQL, the All SQL once option is the best selection because there is no need to run any statement twice since the effect from additional overhead is relatively insignificant compared with the actual SQL execution time.

1 comment(s) so far...