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

You can compare the differences in the access plans that are produced by two separate virtual tests of candidate indexes.

Before you begin

Ensure that the prerequisites are met.

About this task

In this procedure, you 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. You then run a second virtual test and generate a second virtual EXPLAIN snapshot. Finally, you compare both virtual EXPLAIN snapshots.

Procedure

To compare access plans from two tests of 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 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.
  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 test results, click the Compare Access Plans icon to generate and save an EXPLAIN snapshot that incorporates that results. When a message asks whether you want to compare access plans, click Cancel.
  15. In the test results, click the Test Candidate Indexes button to return to the Workload Test Candidate Indexes page.
  16. 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.
  17. Click the Test Candidate Indexes button. The Test Candidate Indexes window opens.
  18. Optional: Modify the statistics for any of the indexes to match or to reflect accurately your database environment.
  19. Click OK to run the test.
  20. In the test results, click the Compare Access Plans icon to generate and save an EXPLAIN snapshot that incorporates that results. When a message asks whether you want to compare access plans, click OK. Then, select the EXPLAIN snapshot that you created in step 14 and the EXPLAIN snapshot that you generated in this step.

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