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.
Procedure
To test virtually recommended indexes and indexes that
you propose:
- If you are tuning only a single SQL statement, follow these
steps:
- Capture the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select the statement and click the Tune
Statement button. The Run Single-Query Advisors and Analysis Tools
page in the Invoke section opens.
- 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.
- 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:
- 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.
- 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.
- Right-click the statement and select Run
Single-query Advisors and Tools on the Selected Statement,
- 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.
- If you want to test both indexes that the Index Advisor
recommends and indexes that you propose, follow these steps.
- Select the Re-explain the query option
on the Run Single-Query Advisors and Tools page.
- Run the Index Advisor to generate recommendations for
new indexes.
- 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.
- Click the Test Candidate Indexes button.
- 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.
- After adding and editing the indexes that you want to test
virtually, click the Test Candidate Indexes button.
- 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.