SQL Performance issue

Database
Enthusiast

SQL Performance issue

Hi All,

I need your input for performance improvement of below SQL (with EXPLAIN PLAN).

This SQL was running fine previously but suddenly having performance issue. We have refreshed the STATS but still problem persist.

explain SELECT

     y.PlntIdCd,

     y.PlngCtryCd,

     y.DivCd,

     y.AtOncePromoFutsCd,

     y.ProdtCd,

     y.GlblProdtCatCd,

     y.GlblProdtSubcatCd,

     y.SeasnYrRlvncDt,

     y.PlngCRD_BusMoRlvncDt,

     y.ShrtLeadTmInd,

     y.MAD_MondayWkDt,

     y.MAD_MondayWkSeqNbr,

     y.RptgCnfrmdQty,

     y.FutsFailATP_BkgsQty,

     y.PerMoToWkCrvSO_Qty,

     y.MstrDataPerMoToWkCrvRtio,

     CASE

          WHEN y.RatioRnk = 1      -- Identify the highest value

          THEN 'Y'

          ELSE 'N'

     END AS MstrDataPeakInd,

     y.OrigPerMoToWkCrvRtio,

     y.AdjSO_PerMoToWkCrvRtio,

     y.AdjPeakInd,

     'Y' AS ActvInd,

     -1 AS RowInsAdtFK,

     -1 AS RowLastUpdtFK,

     -1 AS OrigPhysSrcSysSK,

     Current_Timestamp(0) AS SrcTmstmp,

     'NOT USED' AS RcrdChkSum

FROM

(

     SELECT

          x.PlntIdCd,

          x.PseudoPlngCtryCd AS PlngCtryCd,

          x.DivCd,

          x.GlblProdtCatCd,

          x.GlblProdtSubcatCd,

          x.AtOncePromoFutsCd,

          x.SeasnYrRlvncDt,

          x.PlngCRD_BusMoRlvncDt,

          x.ProdtCd,

          x.ShrtLeadTmInd,

          x.MAD_MondayWkDt,

          x.MAD_MondayWkSeqNbr,

          x.RptgCnfrmdQty,

          x.FutsFailATP_BkgsQty,

          x.PerMoToWkCrvSO_Qty,

          x.MstrDataPerMoToWkCrvRtio,

          x.OrigPerMoToWkCrvRtio,

          x.AdjSO_PerMoToWkCrvRtio,

          x.AdjPeakInd,

          RANK() OVER (PARTITION BY x.PlntIdCd, x.DivCd, x.AtOncePromoFutsCd, x.SeasnYrRlvncDt, x.PlngCRD_BusMoRlvncDt, x.ProdtCd, ShrtLeadTmInd ORDER BY x.MstrDataPerMoToWkCrvRtio DESC) AS RatioRnk

FROM

(

          SELECT

               so.PlntIdCd,

               CASE

                    WHEN so.AtOncePromoFutsCd IN ('AT-ONCE', 'PROMO', 'FUTURES')

                     THEN so.PlntIdCd

                      ELSE so.PlngCtryCd

               END AS PseudoPlngCtryCd,

               so.DivCd,

               so.GlblProdtCatCd,

               so.GlblProdtSubcatCd,

               so.AtOncePromoFutsCd,

               so.SeasnYrRlvncDt,

               so.PlngCRD_BusMoRlvncDt,

               so.ProdtCd,

               so.ShrtLeadTmInd,

               so.MAD_MondayWkDt,

               so.MAD_MondayWkSeqNbr,

               so.RptgCnfrmdQty,

               so.FutsFailATP_BkgsQty,

               so.PerMoToWkCrvSO_Qty,

               CAST((rule.PerDistribRtioNbr * 100) AS DECIMAL(38,6)) AS MstrDataPerMoToWkCrvRtio,

               so.OrigPerMoToWkCrvRtio,

               so.AdjSO_PerMoToWkCrvRtio,

               so.AdjPeakInd,

               CASE EXTRACT(MONTH FROM so.PlngCRD_BusMoRlvncDt)   -- The master data table contains three letter month values. This is for joining.

                    WHEN 1 THEN 'Jan'

                    WHEN 2 THEN 'Feb'

                    WHEN 3 THEN 'Mar'

                    WHEN 4 THEN 'Apr'

                    WHEN 5 THEN 'May'

                    WHEN 6 THEN 'Jun'

                    WHEN 7 THEN 'Jul'

                    WHEN 8 THEN 'Aug'

                    WHEN 9 THEN 'Sep'

                    WHEN 10 THEN 'Oct'

                    WHEN 11 THEN 'Nov'

                    WHEN 12 THEN 'Dec'

               END AS PlngCRD_BusMoRlvncMo

          FROM

               PLN_ETL.NK_PerMoToWkCrvExcptnSO AS so              -- This is the SO data that was prepared for this report.

          INNER JOIN

               PLN_ETL.AtOncePromoFuts AS aopf                    -- This is used to get the right code for joining to master data.

          ON

               so.AtOncePromoFutsCd = aopf.AtOncePromoFutsCd

          LEFT OUTER JOIN

               PLN_ETL.PerDistribPrflDefn_Norm_v AS rule          -- This is the master data profile definition table.

          ON

(              so.DivCd = rule.DivCd

          OR

               rule.DivCd = '*')

          AND

(              so.PlntIdCd = rule.PlntIdCd

          OR

               rule.PlntIdCd = '*')

          AND

(              aopf.AtOncePromoFutsAltCd = rule.DmndStreamNm

          OR

               rule.DmndStreamNm = '*')

          AND

(              PlngCRD_BusMoRlvncMo = rule.CRD_BusMoOfYrShrtNm

          OR

               rule.CRD_BusMoOfYrShrtNm = '*')

          AND

               so.MAD_MondayWkSeqNbr = rule.PerOfstNbr

          AND

               rule.PerOfstNbr BETWEEN -5 AND 6                    -- This identifies the range of weeks we're interested in.

     QUALIFY ROW_NUMBER() OVER (PARTITION BY so.DivCd, PseudoPlngCtryCd, so.AtOncePromoFutsCd, so.ProdtCd, PlngCRD_BusMoRlvncMo, so.ShrtLeadTmInd, so.MAD_MondayWkDt ORDER BY rule.DmndStreamNm DESC,  rule.CRD_BusMoOfYrShrtNm DESC, rule.DivCd DESC,  rule.PlntIdCd DESC) = 1

) AS x

) AS y;

  1) First, we lock PLN_T.NK_PerMoToWkCrvExcptnSO for access, we lock

     PLN_T.PerDistribPrflDefn for access, and we lock

     PLN_T.AtOncePromoFuts for access.

  2) Next, we do an all-AMPs RETRIEVE step from

     PLN_T.PerDistribPrflDefn by way of an all-rows scan with no

     residual conditions into Spool 1 (all_amps), which is built

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

     confidence to be 2,144 rows (132,928 bytes).  The estimated time

     for this step is 0.02 seconds.

  3) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

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

     rows (265,856 bytes).  The estimated time for this step is 0.02

     seconds.

  4) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 6,432

     rows (398,784 bytes).  The estimated time for this step is 0.02

     seconds.

  5) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 8,576

     rows (531,712 bytes).  The estimated time for this step is 0.02

     seconds.

  6) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 10,720

     rows (664,640 bytes).  The estimated time for this step is 0.02

     seconds.

  7) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 12,864

     rows (797,568 bytes).  The estimated time for this step is 0.02

     seconds.

  8) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 15,008

     rows (930,496 bytes).  The estimated time for this step is 0.02

     seconds.

  9) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 17,152

     rows (1,063,424 bytes).  The estimated time for this step is 0.02

     seconds.

 10) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 19,296

     rows (1,196,352 bytes).  The estimated time for this step is 0.02

     seconds.

 11) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 21,440

     rows (1,329,280 bytes).  The estimated time for this step is 0.02

     seconds.

 12) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn by

     way of an all-rows scan with no residual conditions locking for

     access into Spool 1 (all_amps), which is built locally on the AMPs.

     The size of Spool 1 is estimated with high confidence to be 23,584

     rows (1,462,208 bytes).  The estimated time for this step is 0.02

     seconds.

 13) We execute the following steps in parallel.

      1) We do an all-AMPs RETRIEVE step from PLN_T.PerDistribPrflDefn

         by way of an all-rows scan with no residual conditions locking

         for access into Spool 1 (all_amps), which is built locally on

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

         confidence to be 25,728 rows (1,595,136 bytes).  The estimated

         time for this step is 0.02 seconds.

      2) We do an all-AMPs RETRIEVE step from PLN_T.AtOncePromoFuts by

         way of an all-rows scan with no residual conditions into Spool

         5 (all_amps) (compressed columns allowed), which is duplicated

         on all AMPs.  The size of Spool 5 is estimated with high

         confidence to be 1,440 rows (44,640 bytes).  The estimated

         time for this step is 0.02 seconds.

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

     an all-rows scan with a condition of ("(rule.PEROFSTNBR <= 6) AND

     (rule.PEROFSTNBR >= -5)") into Spool 6 (all_amps) (compressed

     columns allowed) fanned out into 50 hash join partitions, which is

     duplicated on all AMPs.  The size of Spool 6 is estimated with

     high confidence to be 9,262,080 rows (574,248,960 bytes).  The

     estimated time for this step is 0.26 seconds.

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

     all-rows scan, which is joined to PLN_T.NK_PerMoToWkCrvExcptnSO by

     way of an all-rows scan with no residual conditions.  Spool 5 and

     PLN_T.NK_PerMoToWkCrvExcptnSO are joined using a dynamic hash join,

     with a join condition of (

     "PLN_T.NK_PerMoToWkCrvExcptnSO.AtOncePromoFutsCd =

     AtOncePromoFutsCd").  The result goes into Spool 7 (all_amps)

     (compressed columns allowed) fanned out into 50 hash join

     partitions, which is built locally on the AMPs.  The size of Spool

     7 is estimated with low confidence to be 46,605,984 rows (

     8,342,471,136 bytes).  The estimated time for this step is 3.42

     seconds.

 16) We do an all-AMPs JOIN step from Spool 6 (Last Use) by way of an

     all-rows scan, which is joined to Spool 7 (Last Use) by way of an

     all-rows scan.  Spool 6 and Spool 7 are right outer joined using a

     hash join of 50 partitions, with condition(s) used for

     non-matching on right table ("(MAD_MondayWkSeqNbr <= 6) AND

     (MAD_MondayWkSeqNbr >= -5)"), with a join condition of (

     "(MAD_MondayWkSeqNbr = PEROFSTNBR) AND ((((( CASE WHEN

     ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 1) THEN ('Jan')

     WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 2) THEN

     ('Feb') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 3)

     THEN ('Mar') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=

     4) THEN ('Apr') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt

     )))= 5) THEN ('May') WHEN ((EXTRACT(MONTH FROM

     (PlngCRD_BusMoRlvncDt )))= 6) THEN ('Jun') WHEN ((EXTRACT(MONTH

     FROM (PlngCRD_BusMoRlvncDt )))= 7) THEN ('Jul') WHEN

     ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 8) THEN ('Aug')

     WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 9) THEN

     ('Sep') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))= 10)

     THEN ('Oct') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt )))=

     11) THEN ('Nov') WHEN ((EXTRACT(MONTH FROM (PlngCRD_BusMoRlvncDt

     )))= 12) THEN ('Dec') ELSE (NULL) END ))= CRD_BUSMOOFYRSHRTNM) OR

     (CRD_BUSMOOFYRSHRTNM = '* ')) AND (((PlntIdCd =PLNTIDCD) OR

     (PLNTIDCD = '* ')) AND (((DivCd = DIVCD) OR (DIVCD = '* ')) AND

     ((AtOncePromoFutsAltCd = DMNDSTREAMNM) OR (DMNDSTREAMNM = '*

     ')))))").  The result goes into Spool 4 (all_amps) (compressed

     columns allowed), which is built locally on the AMPs.  The result

     spool file will not be cached in memory.  The size of Spool 4 is

     estimated with low confidence to be 99,923,229,696 rows (

     22,482,726,681,600 bytes).  The estimated time for this step is 1

     hour and 51 minutes.

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

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

     to be redistributed by value to all AMPs.  The result rows are put

     into Spool 10 (all_amps) (compressed columns allowed), which is

     built locally on the AMPs.  The size is estimated with low

     confidence to be 99,923,229,696 rows (33,873,974,866,944 bytes).

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

     an all-rows scan with a condition of ("Field_21 = 1") into Spool 2

     (used to materialize view, derived table, table function or table

     operator x) (all_amps) (compressed columns allowed), which is

     built locally on the AMPs.  The result spool file will not be

     cached in memory.  The size of Spool 2 is estimated with low

     confidence to be 99,923,229,696 rows (19,884,722,709,504 bytes).

     The estimated time for this step is 1 hour and 56 minutes.

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

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

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

     into Spool 16 (all_amps) (compressed columns allowed), which is

     built locally on the AMPs.  The size is estimated with low

     confidence to be 99,923,229,696 rows (30,876,277,976,064 bytes).

     The estimated time for this step is 2 hours and 3 minutes.

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

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

     table, table function or table operator y) (all_amps) (compressed

     columns allowed), which is built locally on the AMPs.  The result

     spool file will not be cached in memory.  The size of Spool 3 is

     estimated with low confidence to be 99,923,229,696 rows (

     20,284,415,628,288 bytes).  The estimated time for this step is 2

     hours and 6 minutes.

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

     an all-rows scan into Spool 21 (group_amps), which is built

     locally on the AMPs.  The result spool file will not be cached in

     memory.  The size of Spool 21 is estimated with low confidence to

     be 99,923,229,696 rows (30,576,508,286,976 bytes).  The estimated

     time for this step is 2 hours and 6 minutes.

 22) Finally, we send out an END TRANSACTION step to all AMPs involved

     in processing the request.

  -> The contents of Spool 21 are sent back to the user as the result

     of statement 1.