The Query Tuner Workflow Assistant can
display the access plan for a statement as a diagram. The diagram
provides cost and cardinality estimates for each step in the plan.
About this task
An access plan graph describes both the "operational details"
of query execution and how the data flows. A leaf node of an access
plan graph is either a table node, a workfile node, or an index node
that represents a data source in the query execution plan. Data flows
from the bottom up (as displayed in the graph) and is processed by
operation nodes in the access plan graph.
Procedure
To generate an access plan graph:
- Capture the SQL statement that you want
to tune. See Locations from which you can capture
an SQL statement for single-query tuning.
- Select one of the captured statements
and click the Invoke Advisors and Tools button.
The Run Single-Query Advisors and Analysis Tools page
in the Invoke section opens.
- If you need to set the schema
to use for unqualified objects that the SQL statement references,
or (if the statement runs on DB2 for z/OS) change the SQL ID, specify
these values in the Schema and SQLID fields above the SQL statement
on this page. For information about setting global defaults for schemas
and SQL IDs, see Setting the schema to use for unqualified
objects, the SQL ID, or both.
- Click the Select What
to Run button.
- In the Select Activities window, select
the Display access plan graph option.
- Optional: For SQL statements that
run on DB2 for Linux, UNIX, and Windows, Version 9.7.1 or later: For
the analysis of the access plan, specify to use actual values collected
at runtime, instead of values in the EXPLAIN information for the current
SQL statement. Before selecting this option, ensure that your user
ID has the privilege to run the CREATE EVENT MONITOR name FOR
ACTIVITIES command. The following authorities have this
privilege: DBADM, SQLADM, and WLMADM.
- Click OK.
What to do next
The Query Tuner Workflow Assistant opens
to the Review section. If the Review
Access Plan page is not open because you selected additional
options in the Select Activities window, click Open
Access Plan Graph on the left side of the Review section.
For
help on this page, click the
button at the top of the
page. You can also press F1.
For more information about reading
and interpreting access plan graphs, see Tuning SQL with Optim™ Query
Tuner, Part 1: Understanding access paths.