DYNAMIC EXPLAIN | Interpreting EXPLAIN Output | Teradata Vantage - DYNAMIC EXPLAIN - Analytics Database - Teradata Vantage

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. 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 USING request modifier before the explained request makes the request ineligible for IPE. Therefore, 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 specifies no single-row access or scalar subquery operations, and is therefore ineligible for IPE. The static plan for the request is displayed.

There are no changes in the EXPLAIN text report to reported if IPE is 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.
...