Performance issue

Database
Teradata Employee

Performance issue

Hello

I got a performance issue to be more accurate with IO skew whenever I ran the following query it hits the max so I do abort the query,

below is the query and its explain:

Explain INSERT INTO  DEVDB.TST_AY_daily_agg (OPERATOR_NUM ,OPERATOR_PARTY_ID ,SECOND_OPERATOR_PARTY_ID ,FIRST_CALL_DTTM ,SECOND_CALL_DTTM ,THIRD_CALL_DTTM ,LAST_CALL_DTTM )

    SELECT A.OPERATOR_NUM,

        OPERATOR_PARTY_ID,

        SECOND_OPERATOR_PARTY_ID,

        MIN((

        CASE

            WHEN (RANKING =  1 ) THEN (CALL_START_DTTM ) ELSE (NULL )

        END ))(NAMED FIRST_CALL_DTTM ),

            MIN((

        CASE

            WHEN (RANKING =  2 ) THEN (CALL_START_DTTM ) ELSE (NULL )

        END ))(NAMED SECOND_CALL_DTTM ),

            MIN((

        CASE

            WHEN (RANKING =  3 ) THEN (CALL_START_DTTM ) ELSE (NULL )

        END ))(NAMED THIRD_CALL_DTTM ),

            MAX(CALL_START_DTTM )(NAMED last_Call_dttm )

        FROM  (

        SELECT OPERATOR_NUM,

            CALL_START_DTTM,

            OPERATOR_PARTY_ID,

            SECOND_OPERATOR_PARTY_ID,

            RANK() OVER (PARTITION BY OPERATOR_NUM ,OPERATOR_PARTY_ID ,SECOND_OPERATOR_PARTY_ID

            ORDER BY CALL_START_DTTM ASC )(NAMED RANKING )

            FROM  (

            SELECT *

                FROM twm_result.MKTshare_daily

                GROUP BY 1,

                    2,

                    3,

                    4 ) A ) A

        GROUP BY 1,

            2,

            3;

  1) First, we lock a distinct DEVDB."pseudo table" for write on a

     RowHash to prevent global deadlock for DEVDB.TST_AY_daily_agg. 

  2) Next, we lock a distinct twm_result."pseudo table" for read on a

     RowHash to prevent global deadlock for twm_result.MKTshare_daily. 

  3) We lock DEVDB.TST_AY_daily_agg for write, and we lock

     twm_result.MKTshare_daily for read. 

  4) We do an all-AMPs SUM step to aggregate from

     twm_result.MKTshare_daily by way of an all-rows scan with no

     residual conditions , grouping by field1 (

     twm_result.MKTshare_daily.Operator_Num

     ,twm_result.MKTshare_daily.CALL_START_DTTM

     ,twm_result.MKTshare_daily.OPERATOR_PARTY_ID

     ,twm_result.MKTshare_daily.SECOND_OPERATOR_PARTY_ID).  Aggregate

     Intermediate Results are computed locally, then placed in Spool 4. 

     The size of Spool 4 is estimated with high confidence to be

     491,284,438 rows (61,901,839,188 bytes).  The estimated time for

     this step is 2 minutes. 

  5) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of

     an all-rows scan into Spool 1 (used to materialize view, derived

     table or table function A) (all_amps), which is built locally on

     the AMPs.  The size of Spool 1 is estimated with high confidence

     to be 491,284,438 rows (25,546,790,776 bytes).  The estimated time

     for this step is 11.19 seconds. 

  6) We do an all-AMPs STAT FUNCTION step from Spool 1 (Last Use) by

     way of an all-rows scan into Spool 9 (Last Use), which is

     redistributed by hash code to all AMPs.  The result rows are put

     into Spool 7 (all_amps), which is built locally on the AMPs.  The

     size is estimated with high confidence to be 491,284,438 rows (

     63,866,976,940 bytes). 

  7) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of

     an all-rows scan into Spool 2 (used to materialize view, derived

     table or table function A) (all_amps), which is built locally on

     the AMPs.  The size of Spool 2 is estimated with high confidence

     to be 491,284,438 rows (27,511,928,528 bytes).  The estimated time

     for this step is 12.02 seconds. 

  8) We do an all-AMPs SUM step to aggregate from Spool 2 (Last Use) by

     way of an all-rows scan , grouping by field1 (

     twm_result.MKTshare_daily.OPERATOR_NUM

     ,twm_result.MKTshare_daily.OPERATOR_PARTY_ID

     ,twm_result.MKTshare_daily.SECOND_OPERATOR_PARTY_ID).  Aggregate

     Intermediate Results are computed globally, then placed in Spool

     14.  The size of Spool 14 is estimated with low confidence to be

     368,491,476 rows (53,062,772,544 bytes).  The estimated time for

     this step is 3 minutes and 55 seconds. 

  9) We do an all-AMPs RETRIEVE step from Spool 14 (Last Use) by way of

     an all-rows scan into Spool 12 (all_amps), which is redistributed

     by the hash code of (twm_result.MKTshare_daily.OPERATOR_NUM) to

     all AMPs.  Then we do a SORT to order Spool 12 by row hash.  The

     size of Spool 12 is estimated with low confidence to be

     368,491,476 rows (27,268,369,224 bytes).  The estimated time for

     this step is 1 minute and 8 seconds. 

 10) We do an all-AMPs MERGE into DEVDB.TST_AY_daily_agg from Spool 12

     (Last Use).  The size is estimated with low confidence to be

     368,491,476 rows.  The estimated time for this step is 1 second. 

 11) We spoil the parser's dictionary cache for the table. 

 12) 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. 

Hopefully you can advice on a way of replacing the STATE function I am using.