All Forums Database
nimish_123 15 posts Joined 08/14
19 Jul 2016
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.
 
 
 

You must sign in to leave a comment.