SQL Optimizer for DB2 LUW 4.3.1 - User Guide

Retrieve the Run Time for a Group of Alternatives

The Batch Run function is used to retrieve run times for a group of index-set and SQL alternatives. You can then evaluate run-time results to determine the best performing alternative.

To setup and start the Batch Run

1. Click .

   The Batch Run Criteria window is divided into the following tabs:

   Selected SQL/Index Set

   SQL Termination

   Batch Termination

   Run Time Mode/ Repeat Test

   Batch Run Schedule

2. Select the batch run criteria.

3. The settings can be saved for next optimization by selecting the Save settings for next batch run checkbox. By default, this checkbox is unselected.

The Batch Run window displays the run-time criteria and posts the run time for each alternative as it is retrieved. Stop Current and Stop Batch Run functions are available to terminate the currently running alternative and to stop the Batch Run process. Each selected SQL statement is executed one by one, retrieving the run time unless it is terminated.

Terminate Batch Run

To terminate the complete batch run process

Click .

To terminate the current SQL statement

Select SQL | Stop Current.

New Database Session

When you log on to SQL Optimizer, it extracts the logical structure of the database, and maintains this connection throughout the application execution. Another database session is required for when executing the SQL statements for retrieve the run time. This connection is established when the Run for First Record, Run for All Records, or Batch Run functions are initiated and then this new session is dropped after the execution is complete.

Input Parameter Values

SQL Optimizer identifies variables defined within the SQL statement for each SQL or index-set alternative and highlights them in red in the SQL Optimizer window. When the test run process begins, SQL Optimizer prompts you to enter a value for the variable and to select the data type from the Parameters window.

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).

Commit or Rollback

In order to obtain the correct result, SQL Optimizer executes the SQL statement against the database. After executing an UPDATE, INSERT, and DELETE statement, SQL Optimizer prompts you to commit changes that the execution made to the database:

Select Yes to commit the changes or No to roll back the changes.