16.10 - STATIC EXPLAIN - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

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, Teradata Database 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 Teradata Database uses a static plan or summary information from static plan to evaluate workload filters, throttles, and classification criteria.

    In this case, Teradata Database 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, Teradata Database reports the static query plan for the request without explaining why it did not report a dynamic plan.

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 system-controlled setting for EXPLAIN request modifier is STATIC, so you will generate a static EXPLAIN report at most sites whether you precede EXPLAIN with STATIC or not.

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

See “EXPLAIN Request Modifier” in SQL Data Manipulation Language for more information about the EXPLAIN request modifier.

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, Teradata Database 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.

The only change to this report is the boldface sentence before the first step in the plan.
     STATIC EXPLAIN SELECT *
                    FROM t1, t2
                    WHERE t1.a1 < (SELECT MAX(t3.a3)
                                   FROM t3)
                    AND   t1.a1 = t2.a2;
*** Help information returned. 36 rows.
*** Total elapsed time was 1 second.
Explanation
------------------------------------------------------------------
 This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request. 
1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
   a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
   a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
   RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
   read, and we lock OPTBASE_PCTDB.t1 for read.
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.
9) Finally, we send out an END TRANSACTION step to all AMPs involved
   in processing the request.
-> The contents of Spool 5 are sent back to the user as the result of
   statement 1.

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, Teradata Database 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.

The only change to this report is the boldface sentence before the first step in the plan.
     STATIC EXPLAIN SELECT *
                    FROM t1, t2
                    WHERE t1.a1 < (SELECT MAX(t3.a3)
                                   FROM t3)
                    AND   t1.a1 = t2.a2;
*** Help information returned. 36 rows.
*** Total elapsed time was 1 second.
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. 
1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
   a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
   a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
   RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
   read, and we lock OPTBASE_PCTDB.t1 for read.
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.
9) Finally, we send out an END TRANSACTION step to all AMPs involved
   in processing the request.
-> The contents of Spool 5 are sent back to the user as the result of
   statement 1.

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 Teradata Database 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;
*** Help information returned. 12 rows.
*** Total elapsed time was 1 second.
Explanation
-----------------------------------------------------------------
1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
   RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
2) Next, we lock PLS.t1 for read.
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.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
   in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
   statement 1. The total estimated time is 0.03 seconds.