Help Needed in Query Optimization

Database

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_CDR_AGGR_DAILY.CALL_DIRECTION_KEY  )
  AND  ( VW_CIRCLE.GEOGRAPHY_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CIRCLE_KEY  )
  AND  ( VW_DIM_TIME.TIME_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.DAY_KEY  )
  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_KEY).  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

Tags (1)
1 REPLY
Senior Apprentice

Re: Help Needed in Query Optimization

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