Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 21 Feb 2012 @ 21:12:25 GMT


     
  <Prev Next>  
<<First
<Prev
Next> Last>>  


Subj:   Duplicate to all amps
 
From:   filipsil

How Can I avoid the duplication data on all AMPs, because, this situation is destroying the performance of my query. I tried different modifications, but none solved.

Following the execution plan :

This query is optimized using type 2 profile DR146709, profileid 10001.

Explanation
--------------------------------------------------
 
  1)First, we lock DWP_TDM.TB_PESSOA in view DWP_WDM.VW_TB_PESSOA_NF for access, we lock DWP_TDM.TB_LINHA_NF_CR in view DWP_VTDM.TB_LINHA_NF_CR for access, we lock DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR in view DWP_VTDM.TB_INST_PROD_EQUIP_LINHA_NF_CR for access, we lock DWP_TDM.TB_INST_PROD_EQUIP in view DWP_VTDM.TB_INST_PROD_EQUIP for access, we lock DWP_TDM.TB_PESSOA_NF in view DWP_WDM.VW_TB_PESSOA_NF for access, and we lock DWP_TDM.TB_NF in view DWP_VTDM.TB_NF for access.  
  2)Next, we do an all-AMPs JOIN step from DWP_TDM.TB_NF in view DWP_VTDM.TB_NF by way of a RowHash match scan with a condition of ("(DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.NU_TIPO_NF_CR = 1) AND ((((TRANSLATE(((( CASE WHEN (TRIM(BOTH FROM DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.DT_NF (VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC)IS NULL) THEN ('') ELSE (TRIM(BOTH FROM DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.DT_NF (VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(VARCHAR(64), CHARACTER SET LATIN, NOT CASESPECIFIC)) END ))||' 00:00:00')USING LATIN_TO_UNICODE))(TIMESTAMP(6)))<= TIMESTAMP '2012-02-13 00:00:00.000000') AND (NOT (DWP_TDM.TB_NF in view DWP_VTDM.TB_NF.NU_NF IS NULL )))"), which is joined to DWP_TDM.TB_PESSOA_NF in view DWP_WDM.VW_TB_PESSOA_NF by way of a RowHash match scan with a condition of ("DWP_TDM.TB_PESSOA_NF in view DWP_WDM.VW_TB_PESSOA_NF.NU_TIPO_PAPEL_PESSOA_NF = 2"). DWP_TDM.TB_NF and DWP_TDM.TB_PESSOA_NF are left outer joined using a merge join, with a join condition of ("DWP_TDM.TB_NF.NU_NF = DWP_TDM.TB_PESSOA_NF.NU_NF"). The result goes into Spool 9 (all_amps) (compressed columns allowed), which is built locally on the AMPs. The size of Spool 9 is estimated with no confidence to be 962,466 rows (67,372,620 bytes). The estimated time for this step is 0.21 seconds.  
  3)We execute the following steps in parallel.  
   
  1) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of a RowHash match scan, which is joined to DWP_TDM.TB_LINHA_NF_CR in view DWP_VTDM.TB_LINHA_NF_CR by way of a RowHash match scan with no residual conditions. Spool 9 and DWP_TDM.TB_LINHA_NF_CR are left outer joined using a merge join, with a join condition of ("NU_NF = DWP_TDM.TB_LINHA_NF_CR.NU_NF_CR"). The result goes into Spool 10 (all_amps) (compressed columns allowed), which is redistributed by the hash code of (DWP_TDM.TB_LINHA_NF_CR.NU_NF_CR) to all AMPs. Then we do a SORT to order Spool 10 by row hash. The size of Spool 10 is estimated with no confidence to be 1,762,150 rows (230,841,650 bytes). The estimated time for this step is 0.50 seconds.
 
   
  2) We do an all-AMPs RETRIEVE step from DWP_TDM.TB_PESSOA in view DWP_WDM.VW_TB_PESSOA_NF by way of an all-rows scan with no residual conditions into Spool 11 (all_amps) (compressed columns allowed), which is duplicated on all AMPs. Then we do a SORT to order Spool 11 by the hash code of (DWP_TDM.TB_PESSOA.NU_PESSOA). The result spool file will not be cached in memory. The size of Spool 11 is estimated with high confidence to be 73,647,616,954 rows (1,988,485,657,758 bytes). The estimated time for this step is 1 hour and 49 minutes.
 
  4)We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of a RowHash match scan, which is joined to DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR in view DWP_VTDM.TB_INST_PROD_EQUIP_LINHA_NF_CR by way of a RowHash match can. Spool 10 and DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR are eft outer joined using a merge join, with a join condition of ("(NU_NF_CR = DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_NF_CR) AND (NU_LINHA_NF_CR = DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_LINHA_NF_CR)"). The result goes into Spool 12 (all_amps) (compressed columns allowed), which is built locally on the AMPs. Then we do a SORT to order Spool 12 by the hash code of (DWP_TDM.TB_PESSOA_NF.NU_PESSOA_NF). The size of Spool 12 is estimated with no confidence to be 98,717,772 rows (13,326,899,220 bytes). The estimated time for this step is 16.30 seconds.  
  5)We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of a RowHash match scan, which is joined to Spool 12 (Last Use) by way of a RowHash match scan. Spool 11 and Spool 12 are right outer joined using a merge join, with a join condition of ("NU_PESSOA_NF = NU_PESSOA"). The result goes into Spool 13 (all_amps) (compressed columns allowed), which is redistributed by the hash code of (DWP_TDM.TB_INST_PROD_EQUIP_LINHA_NF_CR.NU_INST_PROD_EQUIP) to all AMPs. Then we do a SORT to order Spool 13 by row hash. The result spool file will not be cached in memory. The size of Spool 13 is estimated with no confidence to be 98,717,772 rows (14,708,948,028 bytes). The estimated time for this step is 2 minutes and 12 seconds.  
  6)We do an all-AMPs JOIN step from Spool 13 (Last Use) by way of a RowHash match scan, which is joined to DWP_TDM.TB_INST_PROD_EQUIP in view DWP_VTDM.TB_INST_PROD_EQUIP by way of a RowHash match scan with no residual conditions. Spool 13 and DWP_TDM.TB_INST_PROD_EQUIP are left outer joined using a merge join, with a join condition of ("NU_INST_PROD_EQUIP = DWP_TDM.TB_INST_PROD_EQUIP.NU_INST_PROD_EQUIP"). The input table DWP_TDM.TB_INST_PROD_EQUIP will not be cached in memory. The result goes into Spool 8 (group_amps), which is built locally on the AMPs. The result spool file will not be cached in memory. The size of Spool 8 is estimated with no confidence to be 98,717,772 rows (21,816,627,612 bytes). The estimated time for this step is 11.57 seconds.  
  7)Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.  
  ->The contents of Spool 8 are sent back to the user as the result of statement 1. The total estimated time is 1 hour and 51 minutes.  


att,

Filipe da Silva
IBM Brasil - GBS / AS



     
  <Prev Next>  
<<First
<Prev
Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016