2.09 - Explain - Teradata QueryGrid

Teradata® QueryGrid™ 安装和用户指南

prodname
Teradata QueryGrid
vrm_release
2.09
created_date
2019 年 5 月
category
安装
用户指南
管理
配置
featnum
B035-5991-059K-CHS

Explain Select

以下示例在 Oracle 目标连接器支持的 Teradata 系统上使用 EXPLAIN SELECT 和 EXPLAIN INSERT 命令。
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.