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. It covers how to consider the disk I/O when selecting the best SQL alternative.

When selecting the best performing SQL statement for your application, how should disk I/O be weighed in comparison to the run time when you are selecting the best SQL alternative to replace your original SQL statement?

Let’s take an example where you optimize a SQL statement and then test each alternate SQL statement and find that some alternate SQL statements have better performance than the original SQL. When the disk I/O of the original SQL is compared to the faster performing SQL alternatives, you find that some of the faster alternatives do more disk I/O than the original statement. There are two reasons why you may see more disk I/O for the alternative SQL than you see for the original SQL during the testing process.

The first reason has to do with the setting in the Run Time Retrieval Method section of the Tuning Lab | Execution | Execution Criteria options that you select. This setting determines the number of times you want to test run every SQL. The default setting for this section is to run the Original SQL statement twice and all other SQL statements once. On the first run of the first SQL, the data and indexes may be cached. This first run time with any additional time for caching is not used. The second run time, when there should be no caching, is used to compare to the run time of the other SQL statements. This option is designed to eliminate the effect caching the data and indexes would have on the accuracy of the time comparisons. But running the first SQL statement twice and all others once may not always account for the difference in the disk I/O. On the second run of the original SQL, the disk I/O may be less than the first run due to the fact that the index retrieval and other I/O may not need to be repeated for the second run. But some of the SQL alternatives may use indexes that are different from the indexes used by the original SQL statement. So when those alternatives are only executed once, you may see some additional disk I/O needed to cache the different indexes. Therefore, if you were to select the execution option All SQL twice using the second run time, the difference in disk I/O may be rectified. Note that this option is intended for shorter running SQL statements since the effect of caching is diminished over time.

The second reason that you may see more disk I/O for a faster SQL alternative is that an alternative SQL statement which has a faster elapsed time may use an execution plan that does more disk I/O (such as extra index retrieval, hash join, or merge join for big tables) than the original SQL. The faster SQL alternative may not necessarily be the best use of the overall system resources if disk I/O is a factor in your database environment.

If you are looking for the best possible elapsed time for retrieving all records, the first record, or the first n records, then the fastest SQL provides that solution. But you may also want to consider the overall usage of the system resources. If you are looking for a SQL statement that is faster than your original SQL but that is not using too much of the system resources, you should review the run time statistics that are displayed in the Scenario Explorer window in the Tuning Lab. Review resources usage statistics for those faster SQL alternatives to pick the one that is suitable to the needs of your database.

 

 

 
In Quest SQL Optimizer for Oracle you can select the run time statistic that you would like to use to determine the best SQL alternative in the Tuning Lab | Execution | Execution Criteria option to have the program automatically determine the best SQL alternative for you.