SQL Optimizer for DB2 LUW 4.4.1 - Installation Guide

Time

The run time calculation from the Run for All Records, Run for First Record and the Batch Run is used to compare the alternative SQL and index-set alternatives in the SQL Optimizer window. It is used to help determine the best performing SQL statement versions and the best indexes for your applications. All run times are calculated to the nearest milliseconds in the format of HH:MM:SS.MS.

Excludes network traffic time for data

The calculation of the run time is based on the CPU time of the database server. The data is not moved to the client computer.

Fluctuation of elapsed time

If you execute an alternative more than once, there will be a slight fluctuation in the run time as database optimizer will store the SQL statement in memory. The optimizer does not need to re-parse the SQL statement because the parse tree and the access plan are already in the available cache. Other factors, such as, memory cache status, data pages and index pages distribution status, also affect the performance of SQL statements. In addition, other jobs may be competing for CPU time while the SQL statement is executing. These factors all contribute to a fluctuation of the run time from one execute to the next.

Run Time Calculation for All Records

The execution process retrieves the run time for all records using the following steps.

1. Set the Row array size for getting elapsed time according to the Options setting on the General tab. This will have the server allocate enough buffer to accommodate the specified number of records for each Fetch call.

2. Get the current time as the start time.

3. Run the SQL.

4. Fetch the data until no more record are returned. Each fetch will retrieve the specific number of records from the Row array size for getting elapsed time setting to the buffer in the database server. Note that records are not actually transferred back to the client but are put into a buffer in database.

5. Get the current time as the end time.

6. Calculate the run time (end time - start time = run time).

For example: if you have a SQL that returns 250 records and the Options setting for array size is 100, then the calls that contribute to the run time would be:

1. Get the current time as the start time.

2. Execute the SQL.

3. Fetch (first 100 records)

4. Fetch (second 100 records)

5. Fetch (last 50 records)

6. Get the current time as the end time.

7. Calculate the run time (end time - start time = run time).

Note: Each of the calls to the network does involve the network time, since the call travels over the network from the client computer to database server. However, the data does not travel over the network, since it is retrieved to a database buffer.

Now with the same SQL but the array size changed to 500, the number of calls would be:

1. Get the current time as the start time.

2. Execute the SQL.

3. Fetch (all 250 records)

4. Get the current time as the end time.

5. Calculate the run time (end time - start time = run time).

You can see from this example how the Row array size for getting elapsed time setting in the Options affects the number of fetch calls and in turn affects the amount of network traffic because each fetch travels over the network.

Run Time Calculation for First Record

The run time for retrieving the first record is calculated using the following steps.

1. Set the row array to 1.

2. Get the current time as the start time.

3. Run the SQL.

4. Fetch the first record to the database buffer.

5. Get the current time as the end time.

6. Calculate the run time (end time - start time = run time).