Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 21 Sep 2012 @ 09:42:26 GMT


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


Subj:   Re: Query performance issue - running long
 
From:   lav.gupta

This is the explain plan

Explanation

Explanation
--------------------------------------------------
 
  1)First, we lock PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY for access, we lock PROD_EDW_MIG.DIM_TIME in view VW_DIM_TIME for access, we lock PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE for access, and we lock PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION for access.  
  2)Next, we execute the following steps in parallel.  
   
  1) We do an all-AMPs RETRIEVE step from PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE by way of an all-rows scan with a condition of ("(NOT (PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.FINAL_CIRCLE_NAME IS NULL )) AND (PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.CODE_TYPE = 'Circle')") into Spool 9 (all_amps), which is duplicated on all AMPs. The size of Spool 9 is estimated with low confidence to be 648 rows (18,792 bytes). The estimated time for this step is 0.03 seconds.
 
   
  2) We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 9" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 1 row. The estimated time for this step is 0.00 seconds.
 
  3)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 8" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 2 rows. The estimated time for this step is 0.00 seconds.  
  4)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 10" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 3 rows. The estimated time for this step is 0.00 seconds.  
  5)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 17" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 4 rows. The estimated time for this step is 0.00 seconds.  
  6)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 18" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 5 rows. The estimated time for this step is 0.00 seconds.  
  7)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 19" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 6 rows. The estimated time for this step is 0.00 seconds.  
  8)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 78" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 7 rows. The estimated time for this step is 0.00 seconds.  
  9)We do a single-AMP RETRIEVE step from all partitions of PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of the primary index "PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY = 79" extracting row ids only with no residual conditions into Spool 8 (all_amps), which is built locally on that AMP. The size of Spool 8 is estimated with high confidence to be 8 rows. The estimated time for this step is 0.00 seconds.  
  10)We execute the following steps in parallel.  
   
  1) We do an all-AMPs SORT to order Spool 8 (all_amps) by row id eliminating duplicate rows. The estimated time for this step is 0.00 seconds.
 
   
  2) We do an all-AMPs JOIN step from Spool 9 (Last Use) by way of an all-rows scan, which is joined to 7 partitions of PROD_EDW_MIG.DIM_TIME in view VW_DIM_TIME with a condition of ("(PROD_EDW_MIG.DIM_TIME in view VW_DIM_TIME.TODAY_DATE <= DATE '2012-07-07') AND (PROD_EDW_MIG.DIM_TIME in view VW_DIM_TIME.TODAY_DATE >= DATE '2012-07-01')"). Spool 9 and PROD_EDW_MIG.DIM_TIME are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 10 (all_amps), which is duplicated on all AMPs. The size of Spool 10 is estimated with low confidence to be 103,032 rows ( 4,224,312 bytes). The estimated time for this step is 0.04 seconds.
 
  11)We do an all-AMPs JOIN step from PROD_EDW_MIG.DIM_CALL_DIRECTION in view VW_DIM_CALL_DIRECTION by way of row ids from Spool 8 (Last Use) with no residual conditions, which is joined to Spool 10 (Last Use) by way of an all-rows scan. PROD_EDW_MIG.DIM_CALL_DIRECTION and Spool 10 are joined using a product join, with a join condition of ("(1=1)"). The result goes into Spool 11 (all_amps), which is duplicated on all AMPs. The size of Spool 11 is estimated with low confidence to be 824,256 rows (43,685,568 bytes). The estimated time for this step is 0.14 seconds.  
  12)We do an all-AMPs JOIN step from Spool 11 (Last Use) by way of an all-rows scan, which is joined to PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY by way of an all-rows scan with a condition of ("(PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 9) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 8) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 10) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 17) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 18) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 19) OR ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 78) OR (PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY in view VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = 79 )))))))"). Spool 11 and PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY are joined using a single partition hash_ join, with a join condition of ("(PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.CIRCLE_KEY = GEOGRAPHY_KEY) AND ((PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.DAY_KEY = TIME_KEY) AND (PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY = CALL_DIRECTION_KEY ))"). The input table PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY will not be cached in memory. The result goes into Spool 7 (all_amps), which is built locally on the AMPs. The size of Spool 7 is estimated with low confidence to be 1,302,649,137 rows (84,672,193,905 bytes). The estimated time for this step is 6 minutes and 20 seconds.  
  13)We do an all-AMPs SUM step to aggregate from Spool 7 (Last Use) by way of an all-rows scan, grouping by field1 ( PROD_EDW_MIG.DIM_TIME.TODAY_DATE ,PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME ,PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID ,PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.CP_ACCOUNT_KEY). Aggregate Intermediate Results are computed globally, then placed in Spool 13. The size of Spool 13 is estimated with low confidence to be 976,986,853 rows (133,847,198,861 bytes). The estimated time for this step is 3 minutes and 1 second.  
  14)We do an all-AMPs SUM step to aggregate from Spool 13 (Last Use) by way of an all-rows scan, grouping by field1 ( PROD_EDW_MIG.DIM_TIME.TODAY_DATE ,PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME ,PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID). Aggregate Intermediate Results are computed globally, then placed in Spool 15. The size of Spool 15 is estimated with low confidence to be 1,272 rows (164,088 bytes). The estimated time for this step is 15.39 seconds.  
  15)We do an all-AMPs RETRIEVE step from Spool 15 (Last Use) by way of an all-rows scan into Spool 5 (all_amps), which is built locally on the AMPs. The size of Spool 5 is estimated with low confidence to be 1,272 rows (92,856 bytes). The estimated time for this step is 0.03 seconds.  
  16)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. The contents of Spool 5 are sent back to the user as the result of statement 2. The total estimated time is 9 minutes and 37 seconds.  

BEGIN RECOMMENDED STATS ->

Explanation
--------------------------------------------------
 
  17)"COLLECT STATISTICS PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY COLUMN (CIRCLE_KEY, CALL_DIRECTION_KEY)". (HighConf)  
  18)"COLLECT STATISTICS PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY COLUMN (DAY_KEY, CIRCLE_KEY)". (LowConf)  
  19)"COLLECT STATISTICS PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY COLUMN (DAY_KEY, CALL_DIRECTION_KEY)". (LowConf)  

<- END RECOMMENDED STATS



     
  <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: 23 Jun 2019