Generating and acting on recommendations for indexes for improving the performance of query workloads that run on DB2 for Linux, UNIX, and Windows

The Workload Index Advisor recommends new indexes and changes to existing indexes on tables that are referenced by SQL statements in query workloads.

Before you begin

About this task

Indexes are important for query performance because a good set of indexes can provide more efficient access to tables and speed up query processing. Indexes can improve performance by providing local filtering, making join processing more efficient, avoiding the need for sort processing, providing index-only access, enforcing referential integrity, and more.

After the Workload Index Advisor generates recommendations for new indexes and changes to existing indexes, you can review those recommendations in detail. If you have the appropriate authorities on the database, you can run the DDL statements to carry out the recommendations that you select.

Procedure

  1. In the Manage section, open the Manage and Tune Workloads page.
  2. Select the query workload and click the Invoke Advisors and Tools icon, which is on the left end of the toolbar above the list of query workloads. The Run Workload Advisors page of the Invoke section opens.
  3. Optional: Modify values of the options for the Workload Index Advisor.
    Click Set Advisor Options under Workload on the left side of the Invoke section. Then, click the Indexes tab to display the options that you can modify. For help with the options, click the ? icon. After you are finished modifying the values for options, click Run Workload Advisors on the left side of the Review section.
  4. Ensure that the Re-collect EXPLAIN information before running workload advisors option is selected.
  5. Click the Select What to Run button.
  6. In the Select Activities window, select the Indexes check box and click OK.
  7. In the Collect EXPLAIN Information window, specify values for the runtime environment of the SQL statements that are in the query workload.
    The workflow assistant must run the EXPLAIN statement for each of the SQL statements that are in the query workload. The Workload Index Advisor requires up-to-date information about the SQL statements. For more information, click the Help icon in the lower-left corner of the window.
  8. In the Review Workload Advisor Recommendations page, click the Indexes tab if there are new recommendations for indexes.
  9. Review this information at the top of the Indexes section.
    Estimated performance improvement
    The percentage by which the time to run all of the statements in the query workload improves, if all of the recommended indexes are created.
    Disk space required (DASD space)
    The amount of disk space that is required to create all of the recommended indexes.
    Total IUD cost
    The total amount of time in milliseconds to run all of the INSERT, UPDATE, and DELETE statements that are in the query workload, after the recommended indexes are created.
  10. Review the list of tables that the Workload Index Advisor has recommendations for.
    This information appears for each table.
    Cardinality
    The number of rows that are in the table. The value is -1 if the cardinality is unknown.
    References to Table
    The number of SQL statements in the query workload that reference the table.
    Cumulative Total Cost
    The cumulative cost to execute all of the SQL statements that reference the table.
    Recommended Indexes
    The number of indexes that the Workload Index Advisor recommends for the table.
    IUDM Statements
    The number of INSERT, UPDATE, DELETE, and MERGE statements that reference the table.
  11. Select the check box next to each table that you want to review the recommendations for.
  12. In the Recommendations section, review the new and changed indexes that the advisor recommends.
    This information appears in this section.
    Index
    The name of the recommended index. You can change the name.
    Table
    The name of the table that the index is recommended for.
    Action
    The action that is recommended.
    Create
    It is recommended to create the index.
    Modify
    It is recommended to modify the existing index.
    Index Columns
    The key columns in the recommended index.
    Include Columns
    The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Estimated Performance Gain
    The percentage by which the recommended index can improve the speed of the execution of statements against the table.
    Estimated Disk Space
    The amount of space that is required to create the recommended index.
    Times Used In Workload
    The sum of the execution counts of all SQL statements in the query workload that reference the table.
    Unique
    Indicates whether or not the index is unique.
    Reason Recommended
    The reason that the advisor is making the recommendation.
  13. In the Existing Indexes section, you can find out whether the DB2 optimizer is using existing indexes and whether the optimizer would continue to use existing indexes after you followed the recommendations from the advisor.
    This information appears in this section.
    Index
    The name of the index.
    Table
    The name of the corresponding table.
    Creator
    The qualifier of the index.
    Index Columns
    The key columns in the existing index.
    Include Columns
    The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Used After
    Indicates whether the index would be used if the recommended indexes were created.
    Used Before
    Indicates whether the index is used in the current access plans for the statements that reference the corresponding table.
    Foreign Key Index
    Indicates whether or not the index is on a foreign key in the corresponding table.
  14. In the Index Chosen by Optimizer but Not Recommended section, you can review a list of indexes that the advisor considered recommending and that the DB2 optimizer would have used, but that the advisor did not recommend because they violated constraints that you set. If you want to see the constraints, click the Constraints tab.
    This information appears in this section.
    Index
    The name of the index.
    Table
    The name of the table that the index was evaluated for.
    Index Columns
    The key columns in the index.
    Include Columns
    The columns that are appended to the key columns and that can allow queries to use index-only access when accessing data. These columns are not used to enforce uniqueness, but they can be appended only to unique indexes. Include columns are distinct from key columns.
    Estimated Disk Space
    The amount of disk space that the index would require
    Reason Not Recommended
    The constraint that the index violates
  15. If you want to see which SQL statements would make use of the recommended indexes, in the Recommendations section select the check box next to each index that you are interested in and click the Show SQL Affected by the Selected Indexes icon.
  16. If you want to run or save the DDL statements for the recommendations, click the Run DDL icon. The Run DDL for Selected Indexes window lets you run the statements or save them to a file. You can also modify the statements before running or saving them.

Feedback