All Forums Database
mudit0506 1 post Joined 01/12
19 Sep 2012
Help Needed in Query Optimization

Hi All,
We are doing migration from Netezza to Teradata and similar changes are done in Business Objects to accomodate Reporting change in database.
We used to have a query that used to run in less than a minute in Netezza but is taking more than 10 minutes in Teradata. This table contains around 10 million records and is a aggregated table for Day wise data. We ran collect stats too but still no effect.
Please find below the Query which is taking too long to run on Teradata database.

SELECT
  VW_DIM_TIME.TODAY_DATE,
  VW_CIRCLE.CIRCLE_NAME,
  COALESCE(VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID,' SMS_MO','SMMO','SMS_MT','SMMT','DIAMETER','GPRS' ,VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID),
   count(distinct VW_FACT_MSC_CDR_AGGR_DAILY.CP_ACCOUNT_KEY),
  sum(VW_FACT_MSC_CDR_AGGR_DAILY.CALL_COUNT),
  sum(VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DURATION)/60
FROM
  VW_DIM_TIME,
  VW_CIRCLE,
  VW_DIM_CALL_DIRECTION,
  VW_FACT_MSC_CDR_AGGR_DAILY
WHERE
  ( VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY=VW_FACT_MSC_C DR_AGGR_DAILY.CALL_DIRECTION_KEY  )
  AND  ( VW_CIRCLE.GEOGRAPHY_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CIR CLE_KEY  )
  AND  ( VW_DIM_TIME.TIME_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.DAY_KE Y  )
  AND  ( VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID  NOT IN  ('TRANSIT-O','TRANSIT-I','TRANS','TRANSIT','TRA',' TRAN','-901','-902','0','50','1','2','3')  )
  AND 
  VW_DIM_TIME.TODAY_DATE  BETWEEN  {d '2012-08-01'}  AND  {d '2012-08-02'}
GROUP BY
  1,
  2,
  3

The Explain plan for the below is as provided below:
  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 an all-AMPs RETRIEVE step from 2 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-08-02') AND (PROD_EDW_MIG.DIM_TIME in view
          VW_DIM_TIME.TODAY_DATE >= DATE '2012-08-01')") into Spool 10
          (all_amps), which is duplicated on all AMPs.  Then we do a
          SORT to order Spool 10 by the hash code of (
          PROD_EDW_MIG.DIM_TIME.TIME_KEY).  The size of Spool 10 is
          estimated with high confidence to be 30,456 rows (761,400
          bytes).  The estimated time for this step is 0.00 seconds.
  3) We do an all-AMPs JOIN step from Spool 9 (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 9 and PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY are joined using
     a single partition hash_ join, with a join condition of (
     "GEOGRAPHY_KEY = PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.CIRCLE_KEY").
     The input table PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY will not be
     cached in memory.  The result goes into Spool 11 (all_amps), which
     is built locally on the AMPs.  Then we do a SORT to order Spool 11
     by the hash code of (PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.DAY_KEY).
     The size of Spool 11 is estimated with no confidence to be 154,546
     rows (9,118,214 bytes).  The estimated time for this step is 6
     minutes.
  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 = 9" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
  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 = 8" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
  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 = 10" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
  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 = 17" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
  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 = 18" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
  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 = 19" extracting row ids
     only with no residual conditions into Spool 8 (group_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.
 10) 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 (group_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.
 11) 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 (group_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.
 12) We do a group-AMP SORT to order Spool 8 (group_amps) by row id
     eliminating duplicate rows.  The estimated time for this step is
     0.00 seconds.
 13) We execute the following steps in parallel.
      1) We do a group-AMP RETRIEVE 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 a residual
         condition of ("((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '-901') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '-902')) OR
         ((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '-901') OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '-902') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '0')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '0') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '1')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '1') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '2')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '2') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '3')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '3') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < '50')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > '50') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRA')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > 'TRA') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRAN')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > 'TRAN') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRANS')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > 'TRANS') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRANSIT')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > 'TRANSIT') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRANSIT-I')) OR
         (((PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID > 'TRANSIT-I') AND
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID < 'TRANSIT-O')) OR
         (PROD_EDW_MIG.DIM_CALL_DIRECTION in view
         VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID >
         'TRANSIT-O')))))))))))))") into Spool 12 (all_amps), which is
         duplicated on all AMPs.  Then we do a SORT to order Spool 12
         by the hash code of (
         PROD_EDW_MIG.DIM_CALL_DIRECTION.CALL_DIRECTION_KEY).  The size
         of Spool 12 is estimated with low confidence to be 5,184 rows
         (129,600 bytes).  The estimated time for this step is 0.01
         seconds.
      2) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of
         a RowHash match scan, which is joined to Spool 11 (Last Use)
         by way of a RowHash match scan.  Spool 10 and Spool 11 are
         joined using a merge join, with a join condition of (
         "TIME_KEY = DAY_KEY").  The result goes into Spool 13
         (all_amps), which is built locally on the AMPs.  Then we do a
         SORT to order Spool 13 by the hash code of (
         PROD_EDW_MIG.FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KE Y).  The
         size of Spool 13 is estimated with no confidence to be 67,257
         rows (3,699,135 bytes).  The estimated time for this step is
         0.03 seconds.
 14) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 13 (Last Use) by way
     of a RowHash match scan.  Spool 12 and Spool 13 are joined using a
     merge join, with a join condition of ("CALL_DIRECTION_KEY =
     CALL_DIRECTION_KEY").  The result goes into Spool 7 (all_amps),
     which is built locally on the AMPs.  The size of Spool 7 is
     estimated with no confidence to be 67,257 rows (4,371,705 bytes).
     The estimated time for this step is 0.04 seconds.
 15) 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
     15.  The size of Spool 15 is estimated with no confidence to be
     67,257 rows (9,214,209 bytes).  The estimated time for this step
     is 0.06 seconds.
 16) We do an all-AMPs SUM step to aggregate from Spool 15 (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 17.  The size of Spool 17 is estimated with no confidence
     to be 376 rows (48,504 bytes).  The estimated time for this step
     is 0.05 seconds.
 17) We do an all-AMPs RETRIEVE step from Spool 17 (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 no confidence
     to be 376 rows (25,192 bytes).  The estimated time for this step
     is 0.03 seconds.
 18) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> The contents of Spool 5 are sent back to the user as the result of
     statement 1.  The total estimated time is 6 minutes.
Any help will be appreciated.
Regards,
Mudit

Mudit Sethi
dnoeth 4628 posts Joined 11/04
20 Sep 2012

PI/SI (DDL) and stats information/row counts would be helpfull.
This query accesses views with some additional logic/conditions in it. You should show those views, too.
The COALESCE is stupid, it will never reach past SMS_MO. BO doesn't care about it but human beings get confused by that :-)
Dieter

Dieter

You must sign in to leave a comment.