Query Plans

When integrating information using a federated query planner, it is useful to view the query plans created, to understand better how information is being accessed and processed, and troubleshoot problems.

A query plan is a set of instructions created by a query engine for executing a command submitted by a user or application. The purpose of the query plan is to execute the user's query as efficiently as possible.

Getting a Query Plan

For any given query, the query plan can be generated using the EXPLAIN command. Whether the result should contain a DEBUG_LOG can be controlled with the ENABLE_EXPLAIN_LOG_OUTPUT setting;
The SHOW PLAN command will show the plan for PLAN_TEXT, PLAN_XML, DEBUG_LOG, and DATA_LINEAGE_XML for the last query being run in the current session. Whether the result should contain a DEBUG_LOG can be controlled with the setting SHOWPLAN;
For a query present in the SYSLOG.QueryLog table, an XML representation of the plan can be retrieved using the SYSADMIN.getQueryPlan procedure:
SQL
```
SELECT queryPlan FROM (call "SYSLOG.getQueryLogPlan"("logId" => <the id of the query>)) a;;
```

Analyzing a Query Plan

Once a query plan has been obtained, you will most commonly be looking for:

Source pushdown - what parts of the query got pushed to each source;
Join ordering;
Join algorithm used - merge or nested loop;
Presence of federated optimizations, such as dependent joins;
Join criteria type mismatches.

All of these above issues will be presented in the plan's subsections specific to relational queries. If you are executing a procedure or generating an XML document, the overall query plan will contain additional information related to the surrounding procedural execution.

A query plan consists of a set of nodes organized in a tree structure. As with the above example, you will typically be interested in analyzing the textual form of the plan.

In a procedural context, the ordering of child nodes implies the order of execution. In most other situations, child nodes may be executed in any order, even in parallel. Only in specific optimizations, such as dependent join, will the children of a join execute serially.

Relational Plans

Relational plans represent the processing plan composed of nodes that are the basic building blocks of logical relational operations. Physical relational plans differ from logical relational plans in that they will contain additional operations and execution specifics chosen by the optimizer.

The nodes for a relational query plan are the following:

Access - accesses a source. A source query is sent to the connection factory associated with the source. [For a dependent join, this node is called Dependent Select.]
Project - defines the columns returned from the node. This does not alter the number of records returned. [When there is a subquery in the Select clause, this node is called Dependent Project.]
Project Into - like a normal project, but outputs rows into a target table
Select - select is a criteria evaluation filter node (WHERE/HAVING). [When there is a subquery in the criteria, this node is called Dependent Select.]
Join - defines the join type, join criteria, and join strategy (merge or nested loop)
Union - there are no properties for this node; it just passes rows through from its children
Sort - defines the columns to sort on, the sort direction for each column, and whether to remove duplicates or not.
Dup Removal - same properties as for Sort, but the removeDups property is set to TRUE
Group - groups sets of rows into groups and evaluates aggregate functions
Null - a node that produces no rows. Usually replaces a Select node where the criteria are always false (and whatever tree is underneath). There are no properties for this node
Plan Execution - executes another sub plan
Limit - returns a specified number of rows, then stops processing. Also processes an offset if present

Node Statistics

Every node has a set of statistics that are output. These can be used to determine the amount of data flowing through the node.

Statistic	Description	Units
Node Output Rows	Number of records output from the node	count
Node Process Time	Time processing in this node only	millisec
Node Cumulative Process Time	Elapsed time from the beginning of processing to end	millisec
Node Cumulative Next Batch Process Time	Time processing in this node + child nodes	millisec
Node Next Batch Calls	Number of times a node was called for processing	count
Node Blocks	Number of times a blocked exception was thrown by this node or a child	count

In addition to node statistics, some nodes display cost estimates computed at the node.

Cost Estimates	Description	Units
Estimated Node Cardinality	Estimated number of records that will be output from the node; -1 if unknown	count

Reading a Processor Plan

The query processor plan can be obtained in plain text or XML format. The plain text format is typically easier to read, while the XML format is easier to process by tooling. When possible, tooling should be used to examine the plans, as the tree structures can be deeply nested.

Data flows from the leaves of the tree to the root. Sub plans for procedure execution can be shown inline and are differentiated by different indentations. Given a user query of SELECT pm1.g1.e1, pm1.g2.e2, pm1.g3.e3 from pm1.g1 inner join (pm1.g2 left outer join pm1.g3 on pm1.g2.e1=pm1.g3.e1) on pm1.g1.e1=pm1.g3.e1, the text for a processor plan that does not push down the joins would look like this:

CODE

ProjectNode
  + Output Columns:
    0: e1 (string)
    1: e2 (integer)
    2: e3 (boolean)
  + Cost Estimates:Estimated Node Cardinality: -1.0
  + Child 0:
    JoinNode
      + Output Columns:
        0: e1 (string)
        1: e2 (integer)
        2: e3 (boolean)
      + Cost Estimates:Estimated Node Cardinality: -1.0
      + Child 0:
        JoinNode
          + Output Columns:
            0: e1 (string)
            1: e1 (string)
            2: e3 (boolean)
          + Cost Estimates:Estimated Node Cardinality: -1.0
          + Child 0:
            AccessNode
              + Output Columns:e1 (string)
              + Cost Estimates:Estimated Node Cardinality: -1.0
              + Query:SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0
              + Model Name:pm1
          + Child 1:
            AccessNode
              + Output Columns:
                0: e1 (string)
                1: e3 (boolean)
              + Cost Estimates:Estimated Node Cardinality: -1.0
              + Query:SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g3 AS g_0 ORDER BY c_0
              + Model Name:pm1
          + Join Strategy:MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)
          + Join Type:INNER JOIN
          + Join Criteria:pm1.g1.e1=pm1.g3.e1
      + Child 1:
        AccessNode
          + Output Columns:
            0: e1 (string)
            1: e2 (integer)
          + Cost Estimates:Estimated Node Cardinality: -1.0
          + Query:SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0 ORDER BY c_0
          + Model Name:pm1
      + Join Strategy:ENHANCED SORT JOIN (SORT/ALREADY_SORTED)
      + Join Type:INNER JOIN
      + Join Criteria:pm1.g3.e1=pm1.g2.e1
  + Select Columns:
    0: pm1.g1.e1
    1: pm1.g2.e2
    2: pm1.g3.e3

Note that the nested join node uses a merge join and expects the source queries from each side to produce the expected ordering for the join. The parent join is an enhanced sort join which can delay the decision to perform sorting based on the incoming rows. Note that the outer join from the user query has been modified to an inner join since none of the null inner values can be present in the query result.

The same plan in XML form looks like this:

XML

<?xml version="1.0" encoding="UTF-8"?>
<node name="ProjectNode">
    <property name="Output Columns">
        <value>e1 (string)</value>
        <value>e2 (integer)</value>
        <value>e3 (boolean)</value>
    </property>
    <property name="Cost Estimates">
        <value>Estimated Node Cardinality: -1.0</value>
    </property>
    <property name="Child 0">
        <node name="JoinNode">
            <property name="Output Columns">
                <value>e1 (string)</value>
                <value>e2 (integer)</value>
                <value>e3 (boolean)</value>
            </property>
            <property name="Cost Estimates">
                <value>Estimated Node Cardinality: -1.0</value>
            </property>
            <property name="Child 0">
                <node name="JoinNode">
                    <property name="Output Columns">
                        <value>e1 (string)</value>
                        <value>e1 (string)</value>
                        <value>e3 (boolean)</value>
                    </property>
                    <property name="Cost Estimates">
                        <value>Estimated Node Cardinality: -1.0</value>
                    </property>
                    <property name="Child 0">
                        <node name="AccessNode">
                            <property name="Output Columns">
                                <value>e1 (string)</value>
                            </property>
                            <property name="Cost Estimates">
                                <value>Estimated Node Cardinality: -1.0</value>
                            </property>
                            <property name="Query">
                                <value>SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0</value>
                            </property>
                            <property name="Model Name">
                                <value>pm1</value>
                            </property>
                        </node>
                    </property>
                    <property name="Child 1">
                        <node name="AccessNode">
                            <property name="Output Columns">
                                <value>e1 (string)</value>
                                <value>e3 (boolean)</value>
                            </property>
                            <property name="Cost Estimates">
                                <value>Estimated Node Cardinality: -1.0</value>
                            </property>
                            <property name="Query">
                                <value>SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g3 AS g_0
                                    ORDER BY c_0</value>
                            </property>
                            <property name="Model Name">
                                <value>pm1</value>
                            </property>
                        </node>
                    </property>
                    <property name="Join Strategy">
                        <value>MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)</value>
                    </property>
                    <property name="Join Type">
                        <value>INNER JOIN</value>
                    </property>
                    <property name="Join Criteria">
                        <value>pm1.g1.e1=pm1.g3.e1</value>
                    </property>
                </node>
            </property>
            <property name="Child 1">
                <node name="AccessNode">
                    <property name="Output Columns">
                        <value>e1 (string)</value>
                        <value>e2 (integer)</value>
                    </property>
                    <property name="Cost Estimates">
                        <value>Estimated Node Cardinality: -1.0</value>
                    </property>
                    <property name="Query">
                        <value>SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0
                            ORDER BY c_0</value>
                    </property>
                    <property name="Model Name">
                        <value>pm1</value>
                    </property>
                </node>
            </property>
            <property name="Join Strategy">
                <value>ENHANCED SORT JOIN (SORT/ALREADY_SORTED)</value>
            </property>
            <property name="Join Type">
                <value>INNER JOIN</value>
            </property>
            <property name="Join Criteria">
                <value>pm1.g3.e1=pm1.g2.e1</value>
            </property>
        </node>
    </property>
    <property name="Select Columns">
        <value>pm1.g1.e1</value>
        <value>pm1.g2.e2</value>
        <value>pm1.g3.e3</value>
    </property>
</node>

Note that the same information appears in each of the plan forms. In some cases, it can be easier to follow the simplified format of the debug plan in the final processor plan. From the Debug Log, the same plan as above would appear as follows:

SQL

OPTIMIZATION COMPLETE:
PROCESSOR PLAN:
ProjectNode(0) output=[pm1.g1.e1, pm1.g2.e2, pm1.g3.e3] [pm1.g1.e1, pm1.g2.e2, pm1.g3.e3]
  JoinNode(1) [ENHANCED SORT JOIN (SORT/ALREADY_SORTED)] [INNER JOIN] criteria=[pm1.g3.e1=pm1.g2.e1] output=[pm1.g1.e1, pm1.g2.e2, pm1.g3.e3]
    JoinNode(2) [MERGE JOIN (ALREADY_SORTED/ALREADY_SORTED)] [INNER JOIN] criteria=[pm1.g1.e1=pm1.g3.e1] output=[pm1.g3.e1, pm1.g1.e1, pm1.g3.e3]
      AccessNode(3) output=[pm1.g1.e1] SELECT g_0.e1 AS c_0 FROM pm1.g1 AS g_0 ORDER BY c_0
      AccessNode(4) output=[pm1.g3.e1, pm1.g3.e3] SELECT g_0.e1 AS c_0, g_0.e3 AS c_1 FROM pm1.g3 AS g_0 ORDER BY c_0
    AccessNode(5) output=[pm1.g2.e1, pm1.g2.e2] SELECT g_0.e1 AS c_0, g_0.e2 AS c_1 FROM pm1.g2 AS g_0 ORDER BY c_0

Node Properties

Common

Output Columns - what columns make up the tuples returned by this node
Data Bytes Sent - how many data bytes, not including messaging overhead, were sent by this query
Planning Time - the amount of time in milliseconds spent planning the query

Relational

Relational Node ID - matches the node ids seen in the debug log Node(id)
Criteria - the boolean expression used for filtering
Select Columns - the columns that define the projection
Grouping Columns - the columns used for grouping
Grouping Mapping - shows the mapping of aggregate and grouping column internal names to their expression form
Query - the source query
Model Name - the model name
Sharing ID - nodes sharing the same source results will have the same sharing id
Dependent Join - if a dependent join is being used
Join Strategy - the join strategy (Nested Loop, Sort Merge, Enhanced Sort, etc.)
Join Type - the join type (Left Outer Join, Inner Join, Cross Join)
Join Criteria - the join predicates
Execution Plan - the nested execution plan
Into Target - the insertion target
Sort Columns - the columns for sorting
Sort Mode - if the sort performs another function as well, such as distinct removal
Statistics - the processing statistics
Cost Estimates - the cost/cardinality estimates, including dependent join cost estimates
Row Offset - the row offset expression
Row Limit - the row limit expression
With - the with clause
Window Functions - the window functions being computed
Table Function - the table function (XMLTABLE, OBJECTTABLE, TEXTTABLE, etc.)

Procedure

Expression
Result Set
Program
Variable
Then
Else

Other Plans

Procedure execution (including instead of triggers) uses intermediate and final plan forms that include relational plans. Generally, the structure of the XML/procedure plans will closely match their logical forms. The nested relational plans will be of interest when analyzing performance issues.