STATIC EXPLAIN | Interpreting EXPLAIN Output | Teradata Vantage - 17.10 - STATIC EXPLAIN - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

About Static Query Plans

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

Sometimes the Optimizer generates a static query plan for a request that is eligible for IPE. The following circumstances can cause a static query plan to be generated when the request being explained is eligible for IPE.
  • The request is eligible for IPE, but does not satisfy certain system-controlled threshold criteria.

    In this case, Vantage precedes the reported steps with the following phrases: “This request is eligible for incremental planning and execution (IPE) but doesn't meet thresholds. The following is the static plan for the request.”

  • The request is eligible for IPE, and if it is executed, the Optimizer might generate a dynamic plan instead of the reported static plan, but Vantage uses a static plan or summary information from static plan to evaluate workload filters, throttles, and classification criteria.

    In this case, Vantage precedes the reported steps with the following phrase: “This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request.”

  • The request is eligible for IPE, but IPE is disabled at the site.
    In this case, Vantage reports the static query plan with the following phrase:
    This request is eligible for incremental planning and execution (IPE). IPE is disabled. The following is the static plan for the request.

Requesting a Static EXPLAIN Report

To ask for a static EXPLAIN report for a request, specify the keyword STATIC preceding the keyword EXPLAIN.

The default for an EXPLAIN request modifier is STATIC, so you will generate a static EXPLAIN report at most sites, regardless of whether you precede EXPLAIN with the keyword STATIC.

You can request that the report be returned in XML format by specifying the keywords IN XML following the EXPLAIN keyword.

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

STATIC EXPLAIN Report When the Request Is Eligible for Incremental Planning and Execution

This example assumes that your system has been set never to report a dynamic plan. When this is the case, Vantage the static plan with an indication that the request is eligible for IPE. In this case, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

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

This shows a portion of the EXPLAIN output:

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.
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.
...

STATIC 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.

The example assumes that your system has been set never to report a dynamic plan. When this is the case, Vantage reports the static plan with an indication that the request is eligible for IPE. In this case, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

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

This shows a portion of the EXPLAIN output:

Explanation
----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE) but doesn't 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.
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.
...

STATIC EXPLAIN Where the Request is Eligible for IPE But IPE is Disabled

When you submit a STATIC EXPLAIN for a request that is eligible for IPE but IPE is disabled, the reported text is identical to that returned if you had just submitted EXPLAIN except for the additional sentences that appear in boldface before the first step.

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

This shows a portion of the EXPLAIN output:

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.
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.
...

STATIC EXPLAIN Where the Request Is Not Eligible for Incremental Planning and Execution

This example demonstrates an EXPLAIN report where the request being explained is not eligible for IPE.

The example assumes that your system has been set never to report a dynamic plan. For this example, the request is not eligible for IPE, so Vantage does not report anything about the eligibility of the request for IPE. For this example, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

Because this request is not eligible for IPE, the report is identical to the report generated for this query when IPE is disabled.
STATIC 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.
...