Chat now with support
Chat with Support

SQL Optimizer for DB2 LUW 4.4.1 - Release Notes

Run Time Pane

ClosedView the Run Time pane

The Run Time pane in the SQL and Statistics pages in the SQL Optimizer window lists the current SQL and index-set alternatives generated for the original SQL. The following shows for each alternative.

Notes:

  • Execute Batch Run to capture a complete set of statistics.
  • Initially the alternatives are listed by DB2 LUW Cost in ascending order.

Item

Description

Cost Order

The order of alternatives is initially based on the ascending order of DB2 LUW Cost. The original SQL statement will always be the first item on the grid.

DB2 LUW Cost

The cost value provided by DB2 LUW as an estimate of performance.

Note: The lower the DB2 LUW cost, the better the estimated performance of the SQL statement. However, the cost value should not be used as the actual indication of performance. It is best to execute the alternatives to find which performs the best in your database environment.

Elapsed Time (All Records)

The elapsed time for all records shows the actual elapsed time required to retrieve all records from the database. The calculation of the run time is based on the CPU time of the database server. Thus network traffic is excluded from the time. If the SQL statement is used to retrieve all the records from the database, such as reports or batch processes, the SQL statement with the best elapsed time for all records should be used as a criteria for selecting the alternative.

Times of Improvement (All Records)

The times of improvement shows how many times faster the alternative is for retrieving all records than the original SQL statement.

Records Returned (All Records)

The number of records indicates the total number of records influenced by the alternative. This figure should remain constant throughout the original and optimized SQL statements.

Elapsed Time (First n Records)

The elapsed time for the first record indicates the time it takes for the first record to be returned from the SQL statement. For some on-line retrieval screens, interactive applications, or processes that do not retrieve all records from the SQL statement at once, the best elapsed time for retrieving the first record should be used as a criteria for selecting the best alternative.

Times of Improvement (First n Records)

The times of improvement shows how many times faster the alternative is for retrieving the first record than the original SQL statement.

Records Returned (First n Records)

The first record retrieved, this figure should be 0 or 1.

Remarks

Information from the Batch Run is included in this column. It includes:

  • If the alternative was terminated by the termination criteria.

  • If the alternative was run more than once.

  • If a database error occurred.

  • If the number of records retrieved by the alternative does not match the number of records retrieved by the original SQL statement.

 

 
Related Documents