Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

07-19-2016
04:13 AM

07-19-2016
04:13 AM

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.