Viewing access plans with the Access Plan Explorer

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.

Before you begin

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:

  1. On the Run Single-Query Advisors and Analysis Tools page, follow either of these steps:
    1. Click the Run Default Advisors and Tools button.
    2. 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.
  2. 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.

Feedback