Comparing access plan graphs 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 plan graphs, 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. In the Query Tuner Workflow Assistant open, click the Compare tab.

    On the Compare Access Plan Graphs 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 Graphs 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
  2. On the left side of the Compare Access Plan Graphs page, select an analysis result for the comparison.
  3. On the right side, select the other analysis result for the comparison.
  4. Click the Compare button at the top of the page. A new page opens that lists the differences. The page also shows the access plan graphs side by side, with the differences highlighted in each graph.

Example

Suppose that you wanted to generate an access plan graph for an SQL statement that you captured, tune that statement, and then generate an access plan graph for the tuned statement, so that you could compare the access plans.
  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.
  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