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 view access plans with the Access Plan Explorer:
- On the Run Single-Query Advisors and Analysis
Tools page, follow either of these steps:
- Click the Run Default Advisors and Tools button.
- Click the Select What to Run button
and select Access Plan Explorer.
The workflow assistant for query tuning 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 correlated
subquery and click the Highlight Relationship of Query
Blocks button to discover which mini-plan in the parent
subquery the correlated 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.