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 other activities on the CPU when you are testing very fast running SQL statements. The previous blog discussed minimizing the effect of caching. The next blog will discuss eliminating the effect of network traffic.

 

When you are tuning SQL statements with very short run times, mainly those running in milliseconds, the multitasking of all the other activities on the CPU can easily skew the accuracy of the results. The elapsed run time of a SQL statement is calculated from the clock time, which is the time the SQL statement starts to run on the CPU to the time it finishes. So if it runs for a brief moment and is swapped out for another activity, and then is swapped back in and finishes, this overhead caused by the swapping may actually take longer than the physical runtime of the SQL. So, for very fast running SQL statements, such as a SQL statement embedded into a loop, you should use the Multi-Execute option to accurately measure the performance of the SQL alternatives.

 

The multi-execute option, Tuning Lab | Execution | Execution Method | Number of times to execute each scenario, is designed to take this into account.

 

 

 

This option provides the most accurate run time comparison for SQL statements with very short run times. With this option, the SQL statement is run multiple times and the run time is calculated as the average elapsed run time of all the executions.

 

It includes the Include trace statistics option, so that you can include or exclude the trace statistics that are collected from the Oracle trace log. This option captures detailed statistics from the Oracle trace log files from the server and transfers them to the PC. When you are executing a SQL statement several times, it transfers the statistics for each execution. Including the statistics from the Oracle trace log will add to the amount of time it takes to test because the trace log statistics are retrieved for each execution. Therefore, it is best not to select this option in order to have the most accurate times for comparison.

 

If you would like to learn more about Quest SQL Optimizer for Oracle, please visit the Inside SQL Optimizer for Oracle community.