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 Invoke Advisors and Tools button. The Run
Single-Query Advisors and Analysis Tools page in the Invoke section
opens.
- 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:
- 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,
- Add the indexes that you want to test:
- 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:
- 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.
- 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.
Two tables appear on the Test Candidate
Indexes page:- 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 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.
- Optional: Use the icons above the Candidate
indexes table to add indexes that you want to include
in the test and edit recommended indexes.
- 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. The Review Results of Testing Candidate
Indexes page opens and lists the indexes that you tested
and shows whether the access plan used each index. Also, in the top-left
corner of the workflow assistant, an entry titled "Test Candidate
Index
Results
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.