Comparing access plans by using snapshots of EXPLAIN information after generating index recommendations

You can find out whether the access plans for a query workload would be improved by recommendations from the Workload Index Advisor.

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 also generate a virtual EXPLAIN snapshot that makes use of the recommendations that the Workload Index Advisor generates. You then run a comparison of these two EXPLAIN snapshots.

Procedure

To compare access plans by using snapshots of EXPLAIN information after generating index recommendations:

  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. On the Run Workload Advisors page of the Review section, 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. Where the recommendations from the Workload Index Advisor appear, click the icon Compare Access Plans. The workflow assistant generates and saves a virtual EXPLAIN snapshot that incorporates the recommendations.
  10. Choose to compare two EXPLAIN snapshots for the selected query workload.
  11. In the Compare Access Plans from Two EXPLAIN Snapshots window, select the snapshot that was taken when you ran the Workload Index Advisor and the virtual snapshot that makes use of the index recommendations. Then, click OK.

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