2.10 - Explain - Teradata QueryGrid

Teradata® QueryGrid™ Installation and User Guide

prodname
Teradata QueryGrid
vrm_release
2.10
created_date
September 2019
category
Administration
Configuration
Installation
User Guide
featnum
B035-5991-099K
The following examples use the EXPLAIN SELECT and EXPLAIN INSERT commands on the Teradata system that are supported by the Oracle target connector.

Explain Select

explain select * from SIMBATERADATA.T_BASIC@oraclefs_active;
 
*** Help information returned. 34 rows.
*** Total elapsed time was 15 seconds.
 
Explanation
---------------------------------------------------------------------------
  1) First, we do an all-AMPs RETRIEVE step executing table operator
     TD_SYSFNLIB.QGINITIATORIMPORT in TD_MAP1 with a condition of (
     "(1=1)").
     < BEGIN EXPLAIN FOR REMOTE QUERY -->
     Remote Queries:
     1. EXPLAIN PLAN SET STATEMENT_ID = '00000000006b' FOR SELECT id
     ,c1 FROM SIMBATERADATA.T_BASIC
     2. [Statement_ID=00000000006b, Plan_ID=375, Timestamp=26-APR-18,
     Remarks=, Operation=SELECT STATEMENT, Options=, Object_Node=,
     Object_Owner=, Object_Name=, Object_Alias=, Object_Instance=,
     Object_Type=, Optimizer=ALL_ROWS, Search_Columns=, ID=0,
     Parent_ID=, Depth=0, Position=3, Cost=3, Cardinality=9, Bytes=117,
     Other_tag=, Partition_Start=, Partition_Stop=, Partition_ID=,
     Distribution=, Cpu_Cost=37137, IO_Cost=3, Temp_Space=,
     Access_Predicates=, Filter_Predicates=, Projection=, Time=1,
     Qblock_name=, Statement_ID=00000000006b, Plan_ID=375,
     Timestamp=26-APR-18, Remarks=, Operation=TABLE ACCESS,
     Options=FULL, Object_Node=, Object_Owner=SIMBATERADATA,
     Object_Name=T_BASIC, Object_Alias=T_BASIC@SEL$1,
     Object_Instance=1, Object_Type=TABLE, Optimizer=ANALYZED,
     Search_Columns=, ID=1, Parent_ID=0, Depth=1, Position=1, Cost=3,
     Cardinality=9, Bytes=117, Other_tag=, Partition_Start=,
     Partition_Stop=, Partition_ID=, Distribution=, Cpu_Cost=37137,
     IO_Cost=3, Temp_Space=, Access_Predicates=, Filter_Predicates=,
     Projection="ID"[NUMBER,22], "C1"[VARCHAR2,100], Time=1,
     Qblock_name=SEL$1]
     <-- END EXPLAIN FOR REMOTE QUERY >
     The size of Spool 2 is estimated with no confidence to be 40,000
     rows (6,440,000 bytes).  The estimated time for this step is 0.07
     seconds.
  2) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 2 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.07 seconds.

Explain Insert

EXPLAIN INSERT INTO SIMBATERADATA.T_BASIC@oraclefs_active(100, 'againthruQG');


*** Help information returned. 34 rows.
*** Total elapsed time was 15 seconds.


Explanation
---------------------------------------------------------------------------
  1) First, we do an INSERT step into Spool 5.
  2) Next, we do a single-AMP RETRIEVE step from Spool 5 (Last Use) by
     way of an all-rows scan executing table operator
     TD_SYSFNLIB.QGINITIATOREXPORT in TD_MAP1 with a condition of (
     "(1=1)") into Spool 3 (used to materialize view, derived table,
     table function or table operator T_BASIC) (all_amps), which is
     built locally on that AMP.
     < BEGIN EXPLAIN FOR REMOTE QUERY -->
     Remote Queries:
     1. EXPLAIN PLAN SET STATEMENT_ID = '00000000002c' FOR INSERT INTO
     T_BASIC VALUES (?,?)
     2. [Statement_ID=00000000002c, Plan_ID=401, Timestamp=14-MAY-18,
     Remarks=, Operation=INSERT STATEMENT, Options=, Object_Node=,
     Object_Owner=, Object_Name=, Object_Alias=, Object_Instance=,
     Object_Type=, Optimizer=ALL_ROWS, Search_Columns=, ID=0,
     Parent_ID=, Depth=0, Position=1, Cost=1, Cardinality=1, Bytes=13,
     Other_tag=, Partition_Start=, Partition_Stop=, Partition_ID=,
     Distribution=, Cpu_Cost=0, IO_Cost=1, Temp_Space=,
     Access_Predicates=, Filter_Predicates=, Projection=, Time=1,
     Qblock_name=,Statement_ID=00000000002c, Plan_ID=401,
     Timestamp=14-MAY-18, Remarks=, Operation=LOAD TABLE CONVENTIONAL,
     Options=, Object_Node=, Object_Owner=SIMBATERADATA,
     Object_Name=T_BASIC, Object_Alias=, Object_Instance=,
     Object_Type=, Optimizer=, Search_Columns=, ID=1, Parent_ID=0,
     Depth=1, Position=1, Cost=, Cardinality=, Bytes=, Other_tag=,
     Partition_Start=, Partition_Stop=, Partition_ID=, Distribution=,
     Cpu_Cost=, IO_Cost=, Temp_Space=, Access_Predicates=,
     Filter_Predicates=, Projection=, Time=, Qblock_name=INS$1]
     <-- END EXPLAIN FOR REMOTE QUERY >
     The size of Spool 3 is estimated with high confidence to be 1 row
     (71 bytes).  The estimated time for this step is 0.03 seconds.
  3) We do an all-AMPs RETRIEVE step in TD_Map1 from Spool 3 (Last Use)
     by way of an all-rows scan into Spool 6 (Last Use), which is built
     locally on the AMPs.  The size of Spool 6 (Last Use) is estimated
     with high confidence to be 1 row (64,029 bytes).  The estimated
     time for this step is 0.08 seconds.
  4) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.