Toad World Blog

Why Run Times Vary

May 10, 2013 11:00:00 PM by Steve Hilker

This tip covers why the run time of a SQL statement may vary from one execution to the next.

When you execute a SQL statement several times in Quest SQL Optimizer, you may notice that the run time will vary from one execution to the next. This adds an additional challenge to picking out the best SQL statement from a group of alternative statements.

The run time varies because each time the SQL statement is executed it is sharing the database and CPU processing with other jobs running on the computer. It is especially true for the first time you execute your SQL statement, since some data may be cached into memory. So it is normal that the first execution will take longer than the following executions. When you execute a SQL statement in the Quest SQL Optimizer, the run time (Total Elapsed Time) is the “clock time” from the moment that the SQL statement starts executing on the CPU to the moment it is finished. So since the execution of the SQL statement is sharing the CPU with other processes, the “clock time” is likely to vary from one execution to the next depending on how much sharing of the CPU occurrs while the SQL statement is executing.

There is no overall solution to this challenge. To perform the run time testing of the SQL statements when there is less of a workload on the system is a good practice.

For SQL statements that run in sub-second times, it is recommended to run each SQL alternative several times using the Batch Run Multiple function. See the previous tip Minimizing the Effect of Other Activities on the CPU for more information.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.