Comparing access plan to see the results of tuning single SQL statements

Use the Compare section of the workflow assistant for query tuning to compare two access plan graphs.

Before you begin

About this task

By comparing access plans, you can determine whether changes that you made to an SQL statement improved the access plan for that statement. The Compare section lists and highlights the differences.

Procedure

To compare two access plan graphs:

  1. Capture the SQL statement that you want to use for the comparison, or select an SQL statement from a query-workload.
  2. Run advisors and tools on the statement. The results are grouped together as Analysis Result n in the upper-left corner of the workflow assistant.
    Note: If you want to be able to compare access plan graphs, as well as access plans, specify to generate an access plan graph.
  3. Follow the recommendations of the advisors or otherwise tune the SQL statement by using the findings of the tools that you ran.
  4. Ensure that the local cache of the system catalog is current.
  5. Return to the Run Single-Query Advisors and Tools page and select the Re-explain the query check box.
  6. Run advisors and tools on the statement again.
    Note: If you want to be able to compare access plan graphs, as well as access plans, specify to generate an access plan graph.
  7. Click the Compare tab and select Compare Access Plans under Single Query on the left side of the workflow assistant.

    On the Compare Access Plans page, the analysis results in the current instance of the workflow assistant are listed together with the analysis results that are in any saved projects in your workspace.

    For example, suppose that your workspace contains a project named Project1. In that project, you saved analysis results for two different SQL statements. In the current instance of the workflow assistant, you run advisors and tools on an SQL statement twice. Then, you go to the Compare section. On the Compare Access Plan page, you see these two lists.

    Table 1. The two lists that appear for this example situation
    Left side Right side
    Left side of the page Right side of the page
  8. On the left side of the Compare Access Plan Graphs page, select an analysis result for the comparison.
  9. On the right side, select the other analysis result for the comparison.
  10. Click the Compare button at the top of the page. The Access Plan Comparison Result page opens, listing the differences between the two access plans.

Example

Suppose that you wanted to compare the access plan for a statement before the statement is tuned with the access plan after the statement is tuned. Follow these steps:
  1. In the workflow assistant, generate an access plan graph for an SQL statement. You can generate an access plan graph from the Run All Single-Query Advisors and Analysis Tools page of the Invoke section of the workflow assistant. Click the Select What to Run button to generate the access plan graph, as well as recommendations from the advisors and a formatted version of the SQL statement. The workflow assistant saves the results internally as Current Project_data_server/Query Group 1/Query 1/Analysis Result 1.
  2. Tune the SQL statement, according to the recommendations and your analysis of the formatted query and access plan graph.
  3. Generate another access plan graph for the statement. Again, click the Select What to Run button on the Run Single-Query Advisors and Analysis Tools page of the Invoke section. The workflow assistant saves the results internally as Current® Project_data_server/Query Group 1/Query 1/Analysis Result 2.
  4. Click the Compare tab and select Access Plan Graph Comparisons.
  5. For the left side of the comparison, select Current Project_data_server/Query Group 1/Query 1/Analysis Result 1.
  6. For the right side of the comparison, select Current Project_data_server/Query Group 1/Query 1/Analysis Result 2.
  7. Click the Compare button at the top of the page.

Feedback