Criteria for Selecting Best SQL

This tip 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 Optimize SQL | Optimizer | Batch Run | Execution Method options that you select.

This setting determines the number of times to execute every SQL. We will consider that case when you select the option Run orginal SQL twice using second run time and all others once. This option is designed to eliminate the effect caching the data and indexes would have on the accuracy of the time comparisons. On the first run of the original SQL, the data and indexes may be cached and this activity would add more disk I/O. This first run time with any additional activity for caching is not used. The second run time, when there should be no caching, is used to compare the run time of the original to run time the SQL alternatives. 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 from caching the different indexes. Therefore, if you were to select the execution option All SQL twice using the second run time, or Run all SQL twice if the original SQL run faster than (5 seconds), the difference in disk I/O may be rectified.

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 all the run time statistics that are displayed in the Alternatives window in the Optimize SQL module. Review those resources usage statistics for the faster SQL alternatives to pick the one that is most 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 Optimize SQL | Optimizer | Batch Run | Best Alternative Criteria option to have the program automatically determine the best SQL alternative for you.