Accounting for Other Activity on the CPU When Comparing SQL Run Times

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

Minimizing the effect from multitasking

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 and waiting may actually take longer than the physically run time of the SQL. So, for very fast running SQL statements, you should select the Batch Run Multiple function when you execute the SQL statements to more accurately measure the performance of the SQL alternatives.  

This function provides the most accurate run time comparison for SQL statements with very short run times. With this function, 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 Retrieve 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.