Comparing access plans by using snapshots of EXPLAIN information after testing candidate indexes

You can find out whether access plans for a query workload would be improved by a set of indexes that you tested virtually.

Before you begin

Ensure that the prerequisites are met.

About this task

In this procedure, you generate an EXPLAIN snapshot just before running the Workload Index Advisor. You then run a virtual test of a set of indexes, which are called candidate indexes because they are candidates for being created, altered, or dropped. When you run the test, a virtual EXPLAIN snapshot is generated. This virtual snapshot makes use of the results of the test. Finally, you compare this virtual snapshot with the EXPLAIN snapshot that you generated when you ran the Workload Index Advisor.

Procedure

To compare access plans from before testing candidate indexes and from after testing candidate indexes:

  1. Create a query workload.
    1. Capture the SQL statements that you want to tune.
    2. After capturing SQL statements, click the Create Query Workload from All Statements  button.
    3. In the Input Workload Name and Description window, specify information for identifying the new query workload.
    The Manage and Tune Workloads page of the Manage section opens. The new query workload appears in the table on this page. The status of the query workload is CAPTURED.
  2. Run the Workload Statistics Advisor and implement its recommendations.
  3. Ensure that the local cache of the system catalog is current.
  4. Specify to re-explain the query workload before you run the Workload Statistics Advisor again.
    1. On the left side of the workflow assistant, click the Invoke tab, if the Invoke section is not already open.
    2. On the Run Workload Advisors page, select the option Re-collect EXPLAIN information before running workload advisors.
  5. Run the Workload Statistics Advisor again to find out whether it has additional recommendations. If it does, implement them.
  6. If you implemented any additional recommendations from the Workload Statistics Advisor, ensure that the local cache of the system catalog is current.
  7. Specify to re-explain the query workload when you run the Workload Index Advisor.
    1. On the left side of the workflow assistant, click the Invoke tab, if the Invoke section is not already open.
    2. On the Run Workload Advisors page, select the option Re-collect EXPLAIN information before running workload advisors.
  8. Run the Workload Index Advisor, but do not implement the recommendations.
  9. In the Indexes section of the Review Workload Advisor Recommendations page, click the Test Candidate Indexes button.
  10. Optional: Add or edit indexes that you want to include in the test. You can also specify whether to exclude one or more existing indexes.
  11. Click the Test Candidate Indexes button. The Test Candidate Indexes window opens.
  12. Optional: Modify the statistics for any of the indexes to match or to reflect accurately your database environment.
  13. Click OK to run the test.
  14. In the Recommendations section in the results of the test, click the Compare Access Plans icon. The workflow assistant generates and saves a virtual EXPLAIN snapshot that incorporates the results of the test.
  15. Choose to compare two EXPLAIN snapshots for the selected query workload.
  16. In the Compare Access Plans from Two EXPLAIN Snapshots window, select the virtual snapshot that the test created and the EXPLAIN snapshot that you generated when running the Workload Index Advisor. Then, click OK.
    Note: You could instead choose to compare the virtual snapshot with an EXPLAIN snapshot that you generated at a different time or with a virtual snapshot that you generated together with recommendations from the Workload Index Advisor.

What to do next

On the Comparison History page, double-click the entry for the comparison. On the View Comparison Results by Workload EXPLAIN Snapshots and SQL Statements page, review the Statements table to look for statements with differences in the two EXPLAIN snapshots. If you need to share the results with other people, select Generate Comparison HTML Report in the More actions field above the table.

Feedback