Use
the Access Plan Explorer to find the most expensive components of
an access plan in a format that lets you sort by cost and see relationships
between components.
About this task
- For an SQL statement that runs on DB2 for Linux, UNIX, and Windows
- The Access Plan Explorer shows the operations that are in the
access plan and details about each operation.
- You can view the operations in a table, in which you can sort
the operations by cost, or as a tree.
- For an SQL statement that runs on DB2 for z/OS®
- The Access Plan Explorer shows the access plan as a tree that
displays a query block for each subquery and the mini-plans in each
query block.
- You can sort the mini-plans on various attributes, including cost.
Procedure
To browse an access plan with the Access Plan Explorer:
- 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.
- On the Run Single-Query Advisors and Analysis
Tools page, click the Select What to Run button
and select Show access plan in Access Plan Explorer. The Query Tuner Workflow Assistant opens
to the Review section. If the Review Access
Plan in Access Plan Explorer page is not open, click Open
Access Plan Explorer on the left side of this section.
- Review the access plan to find potential problems.
- For access plans on DB2 for Linux, UNIX, and Windows: Sort
and arrange the columns in the table view to find the most costly
operations in the access plan. Use the Highlight Inflow and Highlight
Outflow buttons to see which operators feed information
to a selected operator and which operator is sent information by the
selected operator. You can also view the flows of information by using
the tree view. Select an operator and view the information about it
in the expandable tables in the lower portion of the Access Plan Explorer.
For
general descriptions about the operators that appear in the access
plan, see Operators
that appear in the Access Plan Explorer for access plans on DB2 for Linux, UNIX,
and Windows
- For access plans on DB2 for z/OS: Select a query block
or mini-plan and view the information about it in the expandable tables
in the lower portion of the Access Plan Explorer. For example, in
looking at a mini-plan, you might find that the corresponding DB2 for z/OS table is accessed by a table-space scan.
In the details for the mini-plan, you might find that the size of
the table is large and the value of the filter factor for the predicate
is also large. In this case, a large number of rows match the predicate
and increase the cost of the mini-plan.
You can select a subquery
and click the Highlight Relationship of Query Blocks button
to discover which mini-plan in the parent subquery the subquery is
bound to.
For descriptions of the values that appear in the
Access
Type,
Join Method,
Join
Type,
Page Range,
Prefetch,
Primary
Access Type,
Query Block Type,
and
Table Type columns, see the link below
for the version of DB2 for z/OS that you are using.