Page History
...
| Anchor | ||||
|---|---|---|---|---|
|
The SQL tab lets you analyze execution plans and explain results so that you can tune statements and achieve optimal results.
...
There can be statements with many execution plans. See “About the Dashboard tab” on page 23, “About the Current tab” on page 30, “About the Activity tab” on page 39, and “About the Objects tab” on page 67.
| Anchor | ||||
|---|---|---|---|---|
|
The following sections explain how to view information and navigate through the SQL tab:
- About viewing the execution plan of a statement
- About interpreting text highlighting on the Highlights tab
- About viewing which objects are referenced by the execution plan
- About viewing information on all operators and predicates in the Execution plan
- About viewing the statement text
- About viewing recommended indexes for a statement
- About viewing additional execution plan details
| Anchor | ||||
|---|---|---|---|---|
|
The Plan Tree area displays the execution plan of a statement and related information, such as statistics, referenced objects, and operations performed in the execution plan. It lets you assess and tune statements based on real information.
...
| Information Tabs | Description | ||
|---|---|---|---|
| Highlights | The default tab, it displays the statement’s Optimized text. When opening a new plan tree or when clicking on the root of the tree, the original text is displayed (Text tab). When an operator is clicked, the text will be switched to the optimized text of the statement. | ||
| Objects | Displays information on all referenced objects in the execution plan, including their tables, indexes, and columns. Statistical and general details are displayed for each object. | ||
| Operators | Displays information on all operators in the Execution plan tree. When you sort a table, you can locate operators by their total cost or by any other column in the table. | ||
| Text | Displays the Original text of the statement. | ||
| Recommendations | Displays new index recommendations for the statement. These are additional indexes that can improve the statement.
|
About the Execution Plan tree
The Execution Plan tree displays both the Virtual execution plan and the Estimated execution plan, of the specified statement.
The execution plan's text appears both in the Text View area (Text tab) at the bottom of the execution plan tree and at the top of the Details area (also a Text tab). The text that relates to the selected step in the Plan tree is highlighted. This lets you view the text of the statement, the execution plan, and additional information, such as the objects referenced by the statement, all at once. In the Text View area you can also view two additional tabs displaying Optimized Text and Remote Query Text.
Actions that can be performed on the tree
You can perform the following actions on the execution plan tree:
...
| Info |
|---|
When launching to the SQL tab, the Highlights tab in the Details area and the Text tab in the Text View area are selected by default. |
| Anchor | ||||
|---|---|---|---|---|
|
The Highlights tab displays the statement’s Optimized text as generated by the DB2 optimizer. Depending upon the operator selected on the Tree, the text will have different colors highlighting relevant tables, columns, and predicates.
...
| Info |
|---|
Not all operators include color fonts. Color fonts are used only when relevant for performance tuning. |
| Anchor | ||||
|---|---|---|---|---|
|
The Objects tab displays three tables (Tables used in Plan, Indexes on Table, Columns in Table) that list all referenced objects in the execution plan, including their indexes and columns. Statistical details and general details are displayed for each object and its sub-entities.
...
| Column | Description |
|---|---|
 | Click to launch to Objects tab—with the selected table in context—to get additional catalog information about the object. |
 | The column definition in the chosen index (Ascending, Descending, or Included). |
| Column | Column name. |
| Type | Data type of the column. |
| Length | The length of the column. |
| Key Number | The position within the chosen index. |
| Anchor | ||||
|---|---|---|---|---|
|
The Operators tab displays information on all operators in the Execution plan tree. When you sort a table, you can locate operators by their total cost or by any other column in the table.
...
| Column | Description |
|---|---|
| Text | Predicate text. |
| Filter Factor | The estimated filter factor of the predicate. |
| How Applied | How predicate is being used by the specified operator. |
| Sub Select | Indicates when the subquery used in this predicate is evaluated. |
| Anchor | ||||
|---|---|---|---|---|
|
The Text tab displays the statement’s original text.
| Anchor | ||||
|---|---|---|---|---|
|
The Recommendations tab displays the output of the Recommend algorithm. The Recommendations tab is divided into three areas:
...
The Details area displays statistical information of the recommended index. The DDL area displays the create index statement.
| Anchor | ||||
|---|---|---|---|---|
|
You can view additional execution plan details by hovering over a specific execution plan row with the mouse. A ToolTip is displayed showing some of the following details, depending upon which row you are hovering over:
- Operator Type
- Description
- Estimated Total Cost
- First row cost
- Estimated I/O cost
- Estimated CPU cost
- Estimated communication cost
- Contribution Ratio
- Estimated total cost
- Operator Arguments such as:
- MAXPAGES=ALL
- PREFETCH=NONE
- ROWLOCK=EXCLUSIVE
- SCANDIR=FORWARD
- TABLOCK=INTENT EXCLUSIVE
| Anchor | ||||
|---|---|---|---|---|
|
Use the Actions menu to perform the following operations:
- Creating a new statement
- Opening an existing statement
- Editing an existing statement
- Re-explaining an access plan
- Generating recommendations
| Anchor | ||||
|---|---|---|---|---|
|
You can create a new statement and save it in the PMDB in a logical cabinet and folder hierarchy. You can also rewrite statements and view their access plans.
...
The new statement is saved in the PMDB in a logical cabinet and folder hierarchy.
| Anchor | ||||
|---|---|---|---|---|
|
You can load a previously saved statement. To open an existing statement
...
| Info |
|---|
If the SQL tab is opened with no statement in-context, the following error message is displayed:
To open a statement you can close the error message dialog and click the Open button displayed on the upper right-hand side of the Details area or click Actions>Open. To open a new statement click Actions>New. |
| Anchor | ||||
|---|---|---|---|---|
|
You can edit a statement that was saved in the PMDB. The statement that was collected by the Collector is saved as a new statement in a logical cabinet and folder hierarchy.
...
The edited statement is saved in a logical cabinet and folder hierarchy.
| Anchor | ||||
|---|---|---|---|---|
|
You can refresh a statement plan by generating a new estimated access plan and saving it, if it is different from the most recent access plan. You can then display the most recent access plan.
To re-explain a statement, go to Actions>Re-Explain or, alternatively, click the Re-Explain button.
| Anchor | ||||
|---|---|---|---|---|
|
You can generate new index recommendations for the statement. These are additional indexes that can improve the statement.
...