Improving query performance using index recommendations

Edit online

IBM® Z Database Assistant can evaluate the indexes used by a query and recommend changes to improve query performance.

About this task

During the exploration process, IBM Z Database Assistant determines whether creating a new index or replacing an existing index would improve query performance. If so, it generates recommendations and the required DDL statements to implement the index changes. Use the following procedure to evaluate the recommended index changes and to deploy them after you determine that they are acceptable.

Procedure

  1. Click the Index recommendation tab view details about the recommended index changes for the query.
    The Index recommendation tab has three sections.
    Index recommendation summary

    Expand this section to display information about the recommendations, including the estimated CPU savings, the minimum number of times the query needs to run to realize the performance improvements, and the DDL for implementing the index recommendations.

    The minimum number of query execution frequency is a tradeoff between the query performance benefit and maintenance cost caused by more indexes. If your query executes more frequently than the minimum number, then creating the recommended index is overall beneficial.

    The minimum number of query frequency is calculated based on the insert rate, update rate, and delete rate. You can edit these rates in the recommendation details section.

    Index recommendation details
    Expand this section to view details about the tables and columns involved in the index recommendations, as well as other performance-related data.

    The insert rate, update rate, and delete rate come from real-time statistics. See How SQL operations affect real-time statistics counters for more information about the INSERT, UPDATE, and DELETE real-time statistics.

    You can edit the Insert rate (times/day), Update rate (times/day), and Delete rate (times/day) columns to get a new evaluation of query frequency. Changing the insert rate, update rate, or delete rate updates the minimum number of times the query needs to run to gain an overall performance improvements.

    To edit a column, click the edit icon (), then type in the desired rate or use the up and down arrows, and click the confirm icon (). After confirming the change, the minimum execution frequency in the Index recommendation summary section is updated.

    Existing indexes
    This section contains details about the indexes that are defined on the tables referenced in the query.
  2. Validate the index recommendations by copying the DDL from the Index recommendation summary section and deploying it in a test environment.
  3. When you have verified that the recommended index changes are performing as expected, deploy them to your production environment.