 |
 |
Archives of the TeradataForum
Message Posted: Thu, 20 Sep 2012 @ 11:01:18 GMT
| Subj: | | Re: Query performance issue - running long |
| |
| From: | | lav.gupta |
Hi,
Version 13.10
I tried first with having multi column stats as they were recommended by optimizer query took 20 mins to give output..
Have created T@YS incident too, they gave recommendation for collecting multi column stats but even after collecting stats query performance
doesn't improve.
collect stats on PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY column (CIRCLE_KEY);
collect stats on PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY column (CALL_DIRECTION_KEY,CIRCLE_KEY);
FYI..
Skewfactor for FACT_MSC_CDR_AGGR_DAILY table having 3 months data is 1.
TableName tblsize
SkewFactor FACT_MSC_CDR_AGGR_DAILY 3118421879808.00 1.00
For PI columns , collected stats as below and not on individual column..
collect STATS ON FACT_MSC_CDR_AGGR_DAILY INDEX ( DAY_KEY ,CIRCLE_KEY ,CALL_SCENARIO_KEY
,CALL_DIRECTION_KEY ,CALL_TYPE_KEY ,CP_ACCOUNT_KEY )
After dropping multi column stats , collecting stats on PI, partition column, below is the explain plan and query took 10-15 mins to
execute
Expectation are high, as same data select in Netezza takes max 2-3 mins and here expectation from Teradata is somewhat similar...
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
("(PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.CODE_TYPE = 'Circle') AND (((PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.FINAL_CIRCLE_NAME >
'NATIONAL') AND (PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.FINAL_CIRCLE_NAME < 'NOT APPLICABLE')) OR ((PROD_EDW_MIG.DIM_GEOGRAPHY in view
VW_CIRCLE.FINAL_CIRCLE_NAME < 'NATIONAL') OR (((PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.FINAL_CIRCLE_NAME > 'NOT APPLICABLE') AND
(PROD_EDW_MIG.DIM_GEOGRAPHY in view VW_CIRCLE.FINAL_CIRCLE_NAME < 'NOT AVAILABLE')) OR (PROD_EDW_MIG.DIM_GEOGRAPHY in view
VW_CIRCLE.FINAL_CIRCLE_NAME > 'NOT AVAILABLE'))))") 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,301,836,151 rows (84,619,349,815 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 ,( CASE WHEN (PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME IS NULL) THEN ('NA') ELSE
(PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME) END) ,( CASE WHEN (NOT (PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID IS NULL )) THEN
(PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID) WHEN (NOT ('SMS_MO'IS NULL )) THEN ('SMS_MO') WHEN (NOT ('SMMO'IS NULL )) THEN ('SMMO') WHEN
(NOT ('SMS_MT'IS NULL )) THEN ('SMS_MT') WHEN (NOT ('SMMT'IS NULL )) THEN ('SMMT') WHEN (NOT ('DIAMETER'IS NULL )) THEN ('DIAMETER') WHEN (NOT
('GPRS'IS NULL )) THEN ('GPRS') ELSE (PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID)
END),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,377,114 rows (133,763,664,618 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 ,( CASE WHEN (PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME IS NULL) THEN ('NA') ELSE
(PROD_EDW_MIG.DIM_GEOGRAPHY.FINAL_CIRCLE_NAME) END) ,( CASE WHEN (NOT (PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID IS NULL )) THEN
(PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID) WHEN (NOT ('SMS_MO'IS NULL )) THEN ('SMS_MO') WHEN (NOT ('SMMO'IS NULL )) THEN ('SMMO') WHEN
(NOT ('SMS_MT'IS NULL )) THEN ('SMS_MT') WHEN (NOT ('SMMT'IS NULL )) THEN ('SMMT') WHEN (NOT ('DIAMETER'IS NULL )) THEN ('DIAMETER') WHEN (NOT
('GPRS'IS NULL )) THEN ('GPRS') ELSE (PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_ID) END)). 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.38 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
| |
