DYNAMIC EXPLAIN | Interpreting EXPLAIN Output | Teradata Vantage - DYNAMIC EXPLAIN - Advanced SQL Engine

A query plan generated by IPE is referred to as a dynamic query plan. Dynamic query plans are not the query plans that are traditionally returned for an explained request.

IPE provides feedback of statistical information and results from the execution of a plan fragment. This enables many optimizations such as partition elimination (see Row Partition Elimination and Column Partition Elimination) and sparse join index qualification, and also enables many query rewrites such as transitive closure, predicate simplification, and unsatisfiability (see Query Rewrite, Statistics, and Optimization for more information about the various query rewrite methods).

Requesting a Dynamic EXPLAIN Report

To ask for a dynamic EXPLAIN report for a request, specify the keyword DYNAMIC preceding the keyword EXPLAIN. The default for an EXPLAIN request modifier is STATIC, so you must specify DYNAMIC at most sites to return a dynamic EXPLAIN report.

You can request that the report be returned in XML format by specifying the keywords IN XML following the EXPLAIN keyword. The actual plan used when the query is executed may differ from the EXPLAIN output due to changes in the data from the time the EXPLAIN was run.

For more information about the EXPLAIN request modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

Alternatively, use DBQL EXPLAIN plan logging, including DBQL XMLPlan logging. This provides the actual plan used for execution of a query. DBQL does not log requests preceded by any form of the EXPLAIN request modifier, though it does log additional information for requests that are optimized to use a dynamic query plan, such as the number of plan fragments and the plan fragment to which the current step belongs.

Security Issues for Dynamic Query Plan EXPLAIN Reports

IPE produces a dynamic query plan by inserting the intermediate results from the execution of the plan fragments into the main query. A dynamic query plan processes the inserted request fragment constants as elements of the query predicates. You might not want to reveal intermediate results in the EXPLAIN report to end users who might not have the authorization to view them. For example, an end user might have SELECT access to a view that can retrieve data from many other views or tables for which the user does not have SELECT access.

If your system is set to mask intermediate dynamic query plan results (this is the default), Vantage masks the intermediate result values in EXPLAIN reports by replacing them with the character string :*. See EXPLAIN Request Modifier Phrase Terminology for further information about the :* character string and its meaning.

DYNAMIC EXPLAIN for an Unmasked Dynamic Query Plan

This example assumes that your system has been set to allow an unmasked dynamic plan to be displayed. For unmasked dynamic plans, the word unmasked signifies that text reporting intermediate results has not been removed from the report. In this report, the unmasked text is the values 10000 and 9999 (appearing in boldface) in the conditions ("OPTBASE_PCTDB.t1.a1 < 10000") and ("OPTBASE_PCTDB.t2.a2 <= 9999") in step 9.

DYNAMIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

Explanation
----------------------------------------------------------------------
The following is the dynamic explain for the request. 
...
5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   (19 bytes). The estimated time for this step is 0.02 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.00 seconds.
7) We do an all-AMPs FEEDBACK RETRIEVE step from Spool 1 (Last Use)
   The size is estimated with high confidence to be 1 row (25 bytes).
   The estimated time for this step is 0.01 seconds. The actual size
   of Spool 1 is 1 row (25 bytes.)
8) We send an END PLAN FRAGMENT step for plan fragment 1. 
9) We do an all-AMPs JOIN step from 9999 partitions of
   OPTBASE_PCTDB.t1 by way of a RowHash match scan with a condition
   of ("OPTBASE_PCTDB.t1.a1 < 10000"), which is joined to
   OPTBASE_PCTDB.t2 by way of a RowHash match scan with a condition
   of ("OPTBASE_PCTDB.t2.a2 <= 9999"). OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
   with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The input tables OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 will not be cached in memory, but
   OPTBASE_PCTDB.t1 is eligible for synchronized scanning. The
   result goes into Spool 1 (group_amps), which is built locally on
   the AMPs. The size of Spool 1 is estimated with low confidence to
   be 9,999 rows (649,935 bytes). The estimated time for this step
   is 8.24 seconds.
...

DYNAMIC EXPLAIN for a Masked Dynamic Query Plan

This example assumes the default is to allow a masked dynamic plan to be reported. For masked dynamic plans, the word masked signifies that text reporting intermediate results has been removed from the report. This is controlled by DBS Control field settings.

Masked values are indicated by the character string :* in the EXPLAIN text, as you can see in Step 9 of this dynamic plan. This character string replaces the masked values 10000 and 9999 seen in the previous example.

DYNAMIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3) 
               FROM t3)
AND   t1.a1 = t2.a2;

Explanation
----------------------------------------------------------------------
The following is the dynamic explain for the request. 
...
5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   (19 bytes). The estimated time for this step is 0.02 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.00 seconds. 
7) We do an all-AMPs FEEDBACK RETRIEVE step from Spool 1 (Last Use)
   The size is estimated with high confidence to be 1 row (25 bytes).
   The estimated time for this step is 0.01 seconds. The actual size
   of Spool 1 is 1 row (25 bytes). 
8) We send an END PLAN FRAGMENT step for plan fragment 1. 
9) We do an all-AMPs JOIN step from 9999 partitions of
   OPTBASE_PCTDB.t1 by way of a RowHash match scan with a condition
   of ("OPTBASE_PCTDB.t1.a1 < :*"), which is joined to
   OPTBASE_PCTDB.t2 by way of a RowHash match scan with a condition
   of ("OPTBASE_PCTDB.t2.a2 <= :*"). OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
   with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The input tables OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 will not be cached in memory, but
   OPTBASE_PCTDB.t1 is eligible for synchronized scanning. The
   result goes into Spool 1 (group_amps), which is built locally on
   the AMPs. The size of Spool 1 is estimated with low confidence to
   be 9,999 rows (649,935 bytes). The estimated time for this step
   is 8.24 seconds.
...

DYNAMIC EXPLAIN When the Display of a Dynamic Plan is Not Allowed But the Request is Eligible for IPE

This example demonstrates a DYNAMIC EXPLAIN report when the DBS Control field setting for displaying dynamic plans has been set not to allow dynamic plans to be reported. In this case, Vantage reports the static plan for the request.

DYNAMIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

Explanation
-----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request. 
...
5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   19 bytes). The estimated time for this step is 0.02 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.00 seconds.
7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
   by way of an all-rows scan and send the rows back to the Dispatcher.
   The size is estimated with high confidence to be 1 row (25 bytes).
   The estimated time for this step is 0.01 seconds.
8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t1 by way of a
   RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
   :%SSQ20"), which is joined to OPTBASE_PCTDB.t2 by way of a RowHash
   match scan with no residual conditions. OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
   with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The input tables OPTBASE_PCTDB.t1 and
   OPTBASE_PCTDB.t2 will not be cached in memory, but
   OPTBASE_PCTDB.t1 is eligible for synchronized scanning. The
   result goes into Spool 5 (group_amps), which is built locally on
   the AMPs. The size of Spool 5 is estimated with no confidence to
   be 33,333,334 rows (2,166,666,710 bytes). The estimated time for
   this step is 1 minute and 10 seconds.
...

DYNAMIC EXPLAIN for an IPE-Eligible Request Preceded by a USING Request Modifier

The explained request for this example is not eligible for IPE because it is preceded by a USING request modifier. As a result, the DYNAMIC EXPLAIN request returns the static plan for the request.

DYNAMIC EXPLAIN
USING (x INTEGER)
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND t1.a1 = t2.a2
AND t1.a1 = :x;

Explanation
-----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE) but dynamic plan does not support USING request modifier. The following is the static plan for the request. 
...
3) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   (19 bytes). The estimated time for this step is 0.05 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.02 seconds.
5) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
   by way of an all-rows scan and send the rows back to the
   Dispatcher. The size is estimated with high confidence to be 1
   row. The estimated time for this step is 0.02 seconds.
6) We do a single-AMP JOIN step from OPTBASE_PCTDB.t2 by way of the
   unique primary index "OPTBASE_PCTDB.t2.a2 = :x", which is joined
   to OPTBASE_PCTDB.t1 by way of the unique primary index
   "OPTBASE_PCTDB.t1.a1 = :x" with a residual condition of (
   "(OPTBASE_PCTDB.t1.a1 < :%SSQ21) AND (OPTBASE_PCTDB.t1.a1 = :x)").
   OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using a merge
   join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The result goes into Spool 5 (one-amp),
   which is built locally on that AMP. The size of Spool 5 is
   estimated with high confidence to be 1 row (87 bytes). The
   estimated time for this step is 0.02 seconds.
...

DYNAMIC EXPLAIN When a Request is Not Eligible for IPE

The explained request for this example is not eligible for IPE because it specifies no single-row access or scalar subquery operations. The static plan for the request is displayed.

There are no changes in the EXPLAIN text report from would be reported if IPE were disabled.

DYNAMIC EXPLAIN 
SELECT *
FROM t1
WHERE a3 > 10;

Explanation
-----------------------------------------------------------------------
...
3) We do an all-AMPs RETRIEVE step from OPTBASE_PCTDB.t1 by way of an
   all-rows scan with a condition of ("OPTBASE_PCTDB.t1.a3 > 10") into
   Spool 1 (all_amps), which is built locally on the AMPs. The size of
   Spool 1 is estimated with no confidence to be 1 row (54 bytes). The
   estimated time for this step is 0.03 seconds.
...

DYNAMIC EXPLAIN Where the Request Is Eligible for Incremental Planning and Execution But Does Not Meet Thresholds

This example demonstrates an EXPLAIN report where the request being explained is eligible for IPE, but does not meet certain system-determined thresholds for execution costs.

Because system-determined thresholds for IPE are not met, Vantage displays the static plan for the request instead of the dynamic plan.

DYNAMIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

Explanation
----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE) but does not meet thresholds. The following is the static plan for the request. 
...
5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   (19 bytes). The estimated time for this step is 0.02 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.00 seconds.
7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
   by way of an all-rows scan and send the rows back to the
   Dispatcher. The size is estimated with high confidence to be 1
   row. The estimated time for this step is 0.02 seconds.
8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
   RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
   a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
   :%SSQ20"). OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
   a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The result goes into Spool 5 (group_amps),
   which is built locally on the AMPs. The size of Spool 5 is
   estimated with no confidence to be 4 rows (260 bytes). The
   estimated time for this step is 0.02 seconds.
...

DYNAMIC EXPLAIN Where the Request is Eligible for IPE, But IPE is Disabled

When you submit a DYNAMIC EXPLAIN for a request that is eligible for IPE, but IPE is disabled for your system, Vantage displays the static plan for the request. The first sentences of EXPLAIN text, highlighted in boldface type in the example, explain that the request is eligible for IPE, but IPE is disabled.

DYNAMIC EXPLAIN
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

Explanation
----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE). IPE is disabled. The following is the static plan for the request. 
...
5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
   way of an all-rows scan with no residual conditions. Aggregate
   Intermediate Results are computed globally, then placed in Spool 3.
   The size of Spool 3 is estimated with high confidence to be 1 row
   (19 bytes). The estimated time for this step is 0.02 seconds.
6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
   an all-rows scan into Spool 1 (all_amps), which is built locally
   on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 1 row (25 bytes). The estimated time for this
   step is 0.00 seconds.
7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
   by way of an all-rows scan and send the rows back to the
   Dispatcher. The size is estimated with high confidence to be 1
   row. The estimated time for this step is 0.02 seconds.
8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
   RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
   a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
   :%SSQ20"). OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
   a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
   OPTBASE_PCTDB.t2.a2"). The result goes into Spool 5 (group_amps),
   which is built locally on the AMPs. The size of Spool 5 is
   estimated with no confidence to be 4 rows (260 bytes). The
   estimated time for this step is 0.02 seconds.
...

DYNAMIC EXPLAIN | Interpreting EXPLAIN Output | Teradata Vantage - DYNAMIC EXPLAIN - Advanced SQL Engine - Teradata Database

SQL Request and Transaction Processing