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 eliminating the effect of network traffic when you are comparing SQL run times. The previous two blogs discuss how to minimize the effect of caching and how to minimize the affect of other activities on the CPU.
Eliminating the Effect of Network Traffic
When comparing the run times of two SQL statements, you want to know which SQL statement is going to execute best in your environment.
Although network traffic certainly may affect the performance of a SELECT SQL statement, when you are optimizing a SQL statement through rewriting the SQL syntax and adding optimization hints, your goal is eliminate any factors that would skew the comparison of the run times of the SQL statements.
No matter which SQL statement alternative you choose when tuning a SQL statement, you will still have the same amount of data to pass over the network. And if the network is slow for one SQL statement and faster for another, including the data transfer time in the comparison time could skew the comparison of the run times of the SQL alternatives against the original SQL statement.
In Quest SQL Optimizer, you have the choice to execute the SQL statements on the database server only, which does not return the data to the client. Or, you can select to execute it so that when a SELECT SQL statement is executed, the data is returned to the client but it is not displayed.
Run on server
The Run on server option found under the Tuning Lab | Execution | Execution Method calculates the run time of a SQL statement from the server’s clock time when the SQL statement starts to execute on the CPU to the time it finishes without sending the data to the client computer. Executing the SQL statements under this option tells you how long it takes to execute on the database server’s CPU. This is the option selected by default.
Note: For Oracle, your logon account must have privileges to execute the SYS.DBMS_SQL package to retrieve the run time from the server.
Run on client
The Run on client option executes the SQL statement and returns the data to the client. Executing the SQL statement under this option provides you with the run time that includes the time it takes to transfer the data to the client computer.
With this option you can limit the number of rows that are retrieved using the Limit rows retrieved to option. The default number of rows that are returned using this option is 100. If you enable this option, it is important to note that this is not comparing the way the SQL statement will actually be executed in the application since you are not retrieving all of the data. It is only good for online queries that browse the first few pages of data. The execution results may be different when you retrieve all the rows.
You can also specify the number of rows that will be retrieved at one time when fetching the data from the server using the Number of rows returned in a single network transfer option.
Result Comparison
When SELECT SQL statements are executed, a comparison of the results is done to further illustrate that the result set for an alternative SQL statement is the same as the original SQL statement. When you select the Run on server option, the comparison made between the original SQL statement and the SQL alternative is the number of rows returned. No comparison of the result data is performed.
When SELECT SQL statements are executed using the Run on client option, the comparison is done between the hash values of the data. To compare the result sets of the original and alternative SQL statements, each row of the result sets is hashed and then the hash values are stored in the memory of the client compute. The values from the result set of the original SQL statement are compared to the values from each SQL alternative to illustrate that the result sets are the same. The data is not stored in memory nor is it stored on the disk drive of the client computer.
For some SQL statements without an ORDER clause specification, the alternative SQL statement’s execution plan may not retrieve the records in the same order as the original SQL statement. The Run on client option tells you when the order is different in the Result Comparison column of the Scenario Explorer window.
