Use the Compare section of the workflow
assistant for query tuning to compare two access plan graphs.
Before you begin
- Generate the two access plan graphs that you want to compare.
- Optional: Set your preferences for the types of differences to
show and the colors to use for highlighting the differences. Select . In the Preferences window, expand and select Access Plan Graph Compare.
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:
- Capture the SQL statement that you want to use for the
comparison, or select an SQL statement from a query-workload.
- 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.
- Follow the recommendations of the advisors or otherwise
tune the SQL statement by using the findings of the tools that you
ran.
- Ensure that the local cache of the system catalog is current.
- Return to the Run Single-Query Advisors and Tools page
and select the Re-explain the query check box.
- 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.
- 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 |
 |
 |
- On the left side of the Compare Access Plan Graphs page,
select an analysis result for the comparison.
- On the right side, select the other analysis result for
the comparison.
- 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:
- 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.
- Tune the SQL statement, according to the recommendations and your
analysis of the formatted query and access plan graph.
- 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.
- Click the Compare tab and select Access
Plan Graph Comparisons.
- For the left side of the comparison, select Current Project_data_server/Query
Group 1/Query 1/Analysis Result 1.
- For the right side of the comparison, select Current Project_data_server/Query
Group 1/Query 1/Analysis Result 2.
- Click the Compare button at the top of
the page.