Virtually testing recommended indexes and indexes that you propose

You can test recommended indexes and indexes that you propose without first creating them. The results of each test show you whether the indexes improve the performance of the current SQL statement, the estimated disk space that would be required for the indexes, and whether the DB2® optimizer would use the indexes in the access path for the current SQL statement.

Before you begin

Procedure

To test virtually recommended indexes and indexes that you propose:

  1. If you are tuning only a single SQL statement, follow these steps:
    1. Capture the SQL statement that you want to tune. See Locations from which you can capture an SQL statement for single-query tuning.
    2. Select the statement and click the Invoke Advisors and Tools button. The Run Single-Query Advisors and Analysis Tools page in the Invoke section opens.
    3. If you are not sure whether the statistics for objects in the access plan for the statement are current, run the Statistics Advisor and run the recommended RUNSTATS control statements. Then, after selecting the Re-explain the query option on the Run Single-Query Advisors and Tools page, run the Statistics Advisor again to be sure that it has no additional recommendations.
  2. If you are tuning a query workload and you want to test indexes virtually for a single SQL statement that is in the query workload, follow these steps:
    1. If you are not sure whether the statistics for objects in the access plans for the query workload are current, run the Workload Statistics Advisor and run the recommended RUNSTATS control statements. Then, after selecting the Re-explain the query option on the Run Workload Advisors page, run the Workload Statistics Advisor again to be sure that it has no additional recommendations.
    2. On the Show Statements page for the workload, sort the statements to find the statements that are not meeting your performance requirements. You can open this page in the Manage section.
    3. Right-click the statement and select Run Single-query Advisors and Tools on the Selected Statement,
  3. If you want to test only indexes that you propose: On the left side of the workflow assistant, expand Advanced under Single Query. Select Test Candidate Indexes. The Test Candidate Indexes page opens.
  4. If you want to test both indexes that the Index Advisor recommends and indexes that you propose, follow these steps.
    1. Select the Re-explain the query option on the Run Single-Query Advisors and Tools page.
    2. Run the Index Advisor to generate recommendations for new indexes.
    3. If the Index Advisor generates recommendations, double-click the Indexes row in the Summary table in the Review Single-Query Advisor Recommendations page of the Review section. The Index section opens.
    4. Click the Test Candidate Indexes button.
  5. On the Test Candidate Indexes page, determine the selection of indexes that you want to test virtually.
    Candidate indexes table
    If you ran the Index Advisor, recommended new indexes are listed in this table. You can edit these index definitions or leave them as is.

    You can add and edit your own indexes to the table by clicking the Add Index icon.

    Existing indexes table
    For your reference, this table lists all of the existing indexes on the tables that the current SQL statement references.
    In this table is a column with the heading Virtually Drop. Select the check box of each existing index that you do not want to include in the virtual test.
    This check box is selected by default in these cases:
    • The index is recommended to be altered, and the replacement index is in the Candidate indexes table. You cannot include in the test any indexes that the Index Advisor recommends to be altered.
    • The index is recommended to be dropped. You can deselect the check box to include the index in the test.
  6. After adding and editing the indexes that you want to test virtually, click the Test Candidate Indexes button.
  7. In the Test Candidate Indexes window, customize the statistics for the indexes, or leave the default statistics. You can also choose to generate an access plan graph for the statement that shows whether the indexes in the test would be included in a new access plan. Then, click OK to run the test.

Results

In the top-left corner of the workflow assistant, an entry titled "Test Candidate Index n" appears, where n is a number that increments by 1 every time that you run a test on the same SQL statement in a single Query Tuner project.

If you chose to generate an access plan graph, that graph is displayed. To open the Review Results of Testing Candidate Indexes, select Test Candidate Indexes on the left side of the workflow assistant.

If you did not choose to generate an access plan graph, the Review Results of Testing Candidate Indexes page opens. This page lists the indexes that you tested and shows whether the access plan used each index.

What to do next

If you decide that you want to create one or more of the indexes that you tested, right-click an index and select Show DDL. Copy or save the DDL, so that you can run it outside of the workflow assistant. Repeat this step for each index that you want to create.

If you want to run another test, return to the recommendations from the Index Advisor by clicking the corresponding "Analysis Result n" entry in the top-left corner of the workflow assistant.


Feedback