Performance Tuning

Database
Enthusiast

Performance Tuning

Hi,

 

query which should take couple of minutes is taking more than 4hrs to run, please provide suggestions to tune it. Thank you.

 Expalin Plan for the Query:

1) First, we lock ED_TST_DATA.EDWT955_CURR_PART_D_TBL for access, we

     lock ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL for access, we lock

     ED_TST_DATA.EDWT010_NATIONAL_COMPANY_TBL for access, we lock

     ED_TST_DATA.EDWT320_PLANT_DTL_TBL for access, we lock

     ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL for access, we lock

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

     ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW for access, we lock

     ED_TST_DATA.EDWT069_POSITION_TBL for access, we lock

     ED_TST_SUPT_DATA.stg for access, we lock

     ED_DATA.EDWC080_EXCH_CURRENCY_TBL for access, we lock

     ED_DATA.EDWT020_SUPPLIER_SITE_TBL for access, and we lock

     ED_DATA.L009 in view ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW

     for access.

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

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

     ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an all-rows

     scan with no residual conditions into Spool 17 (all_amps), which

     is redistributed by the hash code of (

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to all

     AMPs.  Then we do a SORT to order Spool 17 by the sort key in

     spool field1 to retrieve the maximum value where null values are

     ignored.  The size of Spool 17 is estimated with high confidence

     to be 79 rows (1,501 bytes).  The estimated time for this step is

     0.02 seconds.

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

     an all-rows scan into Spool 18 (all_amps) (compressed columns

     allowed), which is redistributed by the hash code of (

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C,

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y)

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

     The size of Spool 18 is estimated with no confidence to be 79 rows

     (1,501 bytes).  The estimated time for this step is 0.03 seconds.

  4) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of a

          RowHash match scan with no residual conditions, which is

          joined to Spool 18 (Last Use) by way of a RowHash match scan

          locking ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL for access.

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL and Spool 18 are

          joined using a merge join, with a join condition of (

          "(ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C =

          EDWT072_COUNTRY_C) AND

          (ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y

          = EDWT072_PURCH_CTRY_EFF_IN_Y)").  The result goes into Spool

          19 (all_amps) (compressed columns allowed), which is

          redistributed by the hash code of (

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

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

          The size of Spool 19 is estimated with no confidence to be 79

          rows (6,478 bytes).  The estimated time for this step is 0.03

          seconds.

       2) We do an all-AMPs RETRIEVE step from ED_DATA.L009 in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

          all-rows scan with no residual conditions into Spool 20

          (all_amps), which is redistributed by the hash code of (

          ED_DATA.L009.EDWL009_COUNTRY_ISO2_C) to all AMPs.  Then we

          do a SORT to order Spool 20 by row hash and the sort key in

          spool field1 eliminating duplicate rows.  The size of Spool

          20 is estimated with high confidence to be 243 rows (4,617

          bytes).  The estimated time for this step is 0.06 seconds.

  5) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from Spool 19 (Last Use) by way

          of an all-rows scan, which is joined to Spool 20 by way of an

          all-rows scan.  Spool 19 and Spool 20 are joined using an

          exclusion merge join, with a join condition of (

          "EDWT072_COUNTRY_C = EDWL009_COUNTRY_ISO2_C") where unknown

          comparison will be ignored.  The result goes into Spool 15

          (all_amps), which is redistributed by the hash code of (

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_LAST_UPDT_S,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_LAST_UPDT_PROC_R,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_CREATE_S,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_CREATE_PROC_R,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_SRC_SYS_C,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_EFF_OUT_Y,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_NAME_X,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_UCC_C,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

          all AMPs.  The size of Spool 15 is estimated with no

          confidence to be 79 rows (17,696 bytes).  The estimated time

          for this step is 0.05 seconds.

       2) We do an all-AMPs RETRIEVE step from

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

          all-rows scan with a condition of (

          "ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW.EDWT072_EFF_OUT_Y

          = DATE '9999-12-31'") locking for access into Spool 22

          (all_amps), which is redistributed by the hash code of (

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

          all AMPs.  Then we do a SORT to order Spool 22 by the sort

          key in spool field1 to retrieve the maximum value where null

          values are ignored.  The size of Spool 22 is estimated with

          high confidence to be 78 rows (1,482 bytes).  The estimated

          time for this step is 0.02 seconds.

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

     an all-rows scan into Spool 23 (all_amps) (compressed columns

     allowed), which is redistributed by the hash code of (

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C,

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y)

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

     The size of Spool 23 is estimated with no confidence to be 78 rows

     (1,482 bytes).  The estimated time for this step is 0.03 seconds.

  7) We do an all-AMPs JOIN step from

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

     ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of a RowHash

     match scan with no residual conditions, which is joined to Spool

     23 (Last Use) by way of a RowHash match scan locking

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL for access.

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL and Spool 23 are joined

     using a merge join, with a join condition of (

     "(ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C =

     EDWT072_COUNTRY_C) AND

     (ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y

     = EDWT072_PURCH_CTRY_EFF_IN_Y)").  The result goes into Spool 24

     (all_amps) (compressed columns allowed), which is redistributed by

     the hash code of (

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to all

     AMPs.  Then we do a SORT to order Spool 24 by row hash.  The size

     of Spool 24 is estimated with no confidence to be 78 rows (6,396

     bytes).  The estimated time for this step is 0.03 seconds.

  8) We execute the following steps in parallel.

       1) We do an all-AMPs JOIN step from Spool 24 (Last Use) by way

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

          by way of an all-rows scan.  Spool 24 and Spool 20 are joined

          using an inclusion merge join, with a join condition of (

          "(NOT (EDWT072_COUNTRY_C IS NULL )) AND (EDWT072_COUNTRY_C =

          EDWL009_COUNTRY_ISO2_C)").  The result goes into Spool 15

          (all_amps), which is redistributed by the hash code of (

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_LAST_UPDT_S,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_LAST_UPDT_PROC_R,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_CREATE_S,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_CREATE_PROC_R,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_SRC_SYS_C,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_EFF_OUT_Y,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_NAME_X,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_UCC_C,

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

          all AMPs.  The size of Spool 15 is estimated with no

          confidence to be 157 rows (35,168 bytes).  The estimated time

          for this step is 0.05 seconds.

       2) We do an all-AMPs RETRIEVE step from

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

          all-rows scan with a condition of (

          "ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW.EDWT072_EFF_OUT_Y

          <= DATE '9999-12-30'") locking for access into Spool 27

          (all_amps), which is redistributed by the hash code of (

          ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

          all AMPs.  Then we do a SORT to order Spool 27 by the sort

          key in spool field1 to retrieve the maximum value where null

          values are ignored.  The size of Spool 27 is estimated with

          high confidence to be 1 row (19 bytes).  The estimated time

          for this step is 0.02 seconds.

  9) We execute the following steps in parallel.

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

          way of an all-rows scan into Spool 28 (all_amps) (compressed

          columns allowed), which is redistributed by the hash code of

          (ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C) to

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

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

          1 row (19 bytes).  The estimated time for this step is 0.03

          seconds.

       2) We do an all-AMPs RETRIEVE step from ED_DATA.L009 in view

          ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

          all-rows scan with no residual conditions locking for access

          into Spool 29 (all_amps) (compressed columns allowed), which

          is redistributed by the hash code of (

          ED_DATA.L009.EDWL009_COUNTRY_ISO2_C) to all AMPs.  Then we

          do a SORT to order Spool 29 by row hash.  The size of Spool

          29 is estimated with high confidence to be 244 rows (27,084

          bytes).  The estimated time for this step is 0.02 seconds.

 10) We do an all-AMPs JOIN step from Spool 28 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 29 (Last Use) by way

     of a RowHash match scan.  Spool 28 and Spool 29 are joined using a

     merge join, with a join condition of ("(EDWT072_COUNTRY_C =

     EDWL009_COUNTRY_ISO2_C) AND (NOT (EDWL009_COUNTRY_ISO2_C IS NULL

     ))").  The result goes into Spool 30 (all_amps) (compressed

     columns allowed), which is redistributed by the hash code of (

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y,

     ED_DATA.L009.EDWL009_COUNTRY_ISO2_C) to few AMPs.  Then we do a

     SORT to order Spool 30 by row hash.  The size of Spool 30 is

     estimated with low confidence to be 2 rows (234 bytes).  The

     estimated time for this step is 0.02 seconds.

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

     all-rows scan, which is joined to

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

     ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an all-rows

     scan with no residual conditions locking

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL for access.  Spool 30 and

     ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL are joined using an

     inclusion merge join, with a join condition of (

     "(ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C =

     EDWL009_COUNTRY_ISO2_C) AND

     ((ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C =

     EDWT072_COUNTRY_C) AND

     (ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_PURCH_CTRY_EFF_IN_Y

     = EDWT072_PURCH_CTRY_EFF_IN_Y ))").  The result goes into Spool 32

     (all_amps), which is redistributed by the hash code of (

     ED_DATA.L009.ROWID) to all AMPs.  Then we do a SORT to order

     Spool 32 by the sort key in spool field1 eliminating duplicate

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

     2 rows (222 bytes).  The estimated time for this step is 0.03

     seconds.

 12) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 15 (all_amps), which is

         redistributed by the hash code of ((TRANSLATE((((TRIM(BOTH

         FROM ED_DATA.L009.EDWL009_LAST_UPDT_Y (CHAR(10), CHARACTER

         SET LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(CHAR(10),

         CHARACTER SET LATIN, NOT CASESPECIFIC))||' ')||(TRIM(BOTH FROM

         TRANSLATE(((ED_DATA.L009.EDWL009_LAST_UPDT_M )(CHAR(8),

         CHARACTER SET UNICODE, NOT CASESPECIFIC))USING

         UNICODE_TO_LATIN))))USING LATIN_TO_UNICODE))(TIMESTAMP(0)), 0,

         (TRANSLATE((((TRIM(BOTH FROM ED_DATA.L009.EDWL009_CREATE_Y

         (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT

         'YYYY-MM-DD'))(CHAR(10), CHARACTER SET LATIN, NOT

         CASESPECIFIC))||' ')||(TRIM(BOTH FROM

         TRANSLATE(((ED_DATA.L009.EDWL009_CREATE_M )(CHAR(8),

         CHARACTER SET UNICODE, NOT CASESPECIFIC))USING

         UNICODE_TO_LATIN))))USING LATIN_TO_UNICODE))(TIMESTAMP(0)), 0,

         'L009', DATE '9999-12-31', ED_DATA.L009.EDWL009_EFFECTIVE_Y,

         ED_DATA.L009.EDWL009_COUNTRY_NAME_X,

         ED_DATA.L009.EDWL009_COUNTRY_UCC_C,

         ED_DATA.L009.EDWL009_COUNTRY_ISO2_C) to all AMPs.  The size

         of Spool 15 is estimated with no confidence to be 159 rows (

         35,616 bytes).

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

         ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL in view

         ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

         all-rows scan with no residual conditions

         , grouping by field1 (

         ED_TST_DATA.EDWT072_PURCH_COUNTRY_TBL.EDWT072_COUNTRY_C)

         locking for access.  Aggregate Intermediate Results are

         computed globally, then placed in Spool 35.  The size of Spool

         35 is estimated with high confidence to be 79 rows (1,501

         bytes).  The estimated time for this step is 0.05 seconds.

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

     an all-rows scan into Spool 34 (all_amps) (compressed columns

     allowed), which is duplicated on all AMPs.  The size of Spool 34

     is estimated with high confidence to be 26,544 rows (398,160

     bytes).

 14) We execute the following steps in parallel.

      1) We do an all-AMPs JOIN step from ED_DATA.L009 in view

         ED_TST_VIEW.EDWT072_PURCH_COUNTRY_COMB_VW by way of an

         all-rows scan with no residual conditions, which is joined to

         Spool 34 (Last Use) by way of an all-rows scan locking

         ED_DATA.L009 for access.  ED_DATA.L009 and Spool 34 are

         joined using an exclusion product join, with a join condition

         of ("EDWT072_COUNTRY_C = ED_DATA.L009.EDWL009_COUNTRY_ISO2_C")

         where unknown comparison will be ignored.  The result goes

         into Spool 15 (all_amps), which is redistributed by the hash

         code of ((TRANSLATE((((TRIM(BOTH FROM

         ED_DATA.L009.EDWL009_LAST_UPDT_Y (CHAR(10), CHARACTER SET

         LATIN, NOT CASESPECIFIC, FORMAT 'YYYY-MM-DD'))(CHAR(10),

         CHARACTER SET LATIN, NOT CASESPECIFIC))||' ')||(TRIM(BOTH FROM

         TRANSLATE(((ED_DATA.L009.EDWL009_LAST_UPDT_M )(CHAR(8),

         CHARACTER SET UNICODE, NOT CASESPECIFIC))USING

         UNICODE_TO_LATIN))))USING LATIN_TO_UNICODE))(TIMESTAMP(0)), 0,

         (TRANSLATE((((TRIM(BOTH FROM ED_DATA.L009.EDWL009_CREATE_Y

         (CHAR(10), CHARACTER SET LATIN, NOT CASESPECIFIC, FORMAT

         'YYYY-MM-DD'))(CHAR(10), CHARACTER SET LATIN, NOT

         CASESPECIFIC))||' ')||(TRIM(BOTH FROM

         TRANSLATE(((ED_DATA.L009.EDWL009_CREATE_M )(CHAR(8),

         CHARACTER SET UNICODE, NOT CASESPECIFIC))USING

         UNICODE_TO_LATIN))))USING LATIN_TO_UNICODE))(TIMESTAMP(0)), 0,

         'L009', DATE '9999-12-31', ED_DATA.L009.EDWL009_EFFECTIVE_Y,

         ED_DATA.L009.EDWL009_COUNTRY_NAME_X,

         ED_DATA.L009.EDWL009_COUNTRY_UCC_C,

         ED_DATA.L009.EDWL009_COUNTRY_ISO2_C) to all AMPs.  Then we do

         a SORT to order Spool 15 by the sort key in spool field1

         eliminating duplicate rows.  The size of Spool 15 is estimated

         with no confidence to be 243 rows (54,432 bytes).  The

         estimated time for this step is 0.04 seconds.

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

         ED_DATA.EDWT020_SUPPLIER_SITE_TBL by way of an all-rows scan

         with no residual conditions , grouping by field1 (

         ED_DATA.EDWT020_SUPPLIER_SITE_TBL.EDWT020_SUPPLIER_SITE_C).

         Aggregate Intermediate Results are computed globally, then

         placed in Spool 38.  The size of Spool 38 is estimated with

         high confidence to be 682,212 rows (15,690,876 bytes).  The

         estimated time for this step is 0.16 seconds.

 

Accepted Solutions
Junior Contributor

Re: Performance Tuning

The Explain is useless, you need to check dbc.QryLogStepsV which step(s) has long runtime.

 

And you might compare dbc.QryLogExplainV to see if there are differences between previous and current plan.

1 ACCEPTED SOLUTION
6 REPLIES
Enthusiast

Re: Performance Tuning

the other half of the EXpalin is as follows:

 

15) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 5 (used to materialize

         view, derived table, table function or table operator t020)

         (all_amps) (compressed columns allowed), which is built

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

         high confidence to be 682,212 rows (19,101,936 bytes).  The

         estimated time for this step is 0.03 seconds.

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

         ED_TST_DATA.EDWT320_PLANT_DTL_TBL by way of an all-rows scan

         with no residual conditions , grouping by field1 (

         ED_TST_DATA.EDWT320_PLANT_DTL_TBL.EDWT010_NATL_COMPANY_C

         ,ED_TST_DATA.EDWT320_PLANT_DTL_TBL.EDWT320_REAL_PLANT_CODE_C).

         Aggregate Intermediate Results are computed globally, then

         placed in Spool 41.  The size of Spool 41 is estimated with

         low confidence to be 7,849 rows (502,336 bytes).  The

         estimated time for this step is 0.05 seconds.

 16) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 7 (used to materialize

         view, derived table, table function or table operator t320)

         (all_amps) (compressed columns allowed), which is built

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

         low confidence to be 7,849 rows (298,262 bytes).  The

         estimated time for this step is 0.03 seconds.

      2) We do an all-AMPs RETRIEVE step from

         ED_DATA.EDWC080_EXCH_CURRENCY_TBL by way of an all-rows scan

         with no residual conditions into Spool 44 (all_amps), which is

         redistributed by the hash code of (

         ED_DATA.EDWC080_EXCH_CURRENCY_TBL.EDWC080_CURRENCY_ALPHA_C)

         to all AMPs.  Then we do a SORT to order Spool 44 by the sort

         key in spool field1 to retrieve the maximum value where null

         values are ignored.  The size of Spool 44 is estimated with

         high confidence to be 150 rows (3,000 bytes).  The estimated

         time for this step is 0.02 seconds.

 17) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 59 (all_amps) (compressed

         columns allowed), which is duplicated on all AMPs.  The size

         of Spool 59 is estimated with no confidence to be 50,400 rows

         (1,058,400 bytes).  The estimated time for this step is 0.03

         seconds.

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

         ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL by way of an all-rows

         scan with no residual conditions , grouping by field1 (

         ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL.EDWT075_UNIT_OF_MEASURE_C

         ,ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL.EDWT075_UOM_SOURCE_C).

         Aggregate Intermediate Results are computed globally, then

         placed in Spool 46.  The size of Spool 46 is estimated with

         high confidence to be 174 rows (8,874 bytes).  The estimated

         time for this step is 0.05 seconds.

 18) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 11 (used to materialize

         view, derived table, table function or table operator t075)

         (all_amps) (compressed columns allowed), which is built

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

         high confidence to be 174 rows (6,090 bytes).  The estimated

         time for this step is 0.03 seconds.

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

         ED_TST_DATA.EDWT069_POSITION_TBL by way of an all-rows scan

         with no residual conditions , grouping by field1 (

         ED_TST_DATA.EDWT069_POSITION_TBL.EDWT069_JOB_C).  Aggregate

         Intermediate Results are computed globally, then placed in

         Spool 49.  The size of Spool 49 is estimated with high

         confidence to be 17,555 rows (403,765 bytes).  The estimated

         time for this step is 0.07 seconds.

 19) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 10 (used to materialize

         view, derived table, table function or table operator t069)

         (all_amps) (compressed columns allowed), which is built

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

         high confidence to be 17,555 rows (491,540 bytes).  The

         estimated time for this step is 0.03 seconds.

      2) We do an all-AMPs RETRIEVE step from

         ED_TST_DATA.EDWT955_CURR_PART_D_TBL by way of an all-rows

         scan with a condition of (

         "(ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_SEG4_R =

         '~!@') AND

         ((ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC002_PART_OWNER_R =

         ' 1') AND

         ((ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_SEG6_R =

         '~!@') AND

         (ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_SEG5_R =

         '~!@')))") into Spool 79 (all_amps) (compressed columns

         allowed), which is redistributed by the hash code of (

         ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_BASE_R,

         ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_SUFFIX_R,

         ED_TST_DATA.EDWT955_CURR_PART_D_TBL.EDWC020_PART_PREFIX_R) to

         all AMPs.  The size of Spool 79 is estimated with high

         confidence to be 11,801,367 rows (460,253,313 bytes).  The

         estimated time for this step is 0.63 seconds.

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

         ED_TST_DATA.EDWT010_NATIONAL_COMPANY_TBL by way of an

         all-rows scan with no residual conditions

         , grouping by field1 (

         ED_TST_DATA.EDWT010_NATIONAL_COMPANY_TBL.EDWT010_NATL_COMPANY_C).

         Aggregate Intermediate Results are computed globally, then

         placed in Spool 53.  The size of Spool 53 is estimated with

         high confidence to be 91 rows (2,457 bytes).  The estimated

         time for this step is 0.05 seconds.

 20) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 6 (used to materialize

         view, derived table, table function or table operator t010)

         (all_amps) (compressed columns allowed), which is built

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

         high confidence to be 91 rows (2,730 bytes).  The estimated

         time for this step is 0.03 seconds.

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

         ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL by way of an all-rows

         scan with no residual conditions , grouping by field1 (

         ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWC020_BUS_ID_K

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT020_OSITE_SITE_C

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT010_NATL_COMPANY_C

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT320_REAL_PLANT_CODE_C

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT056_REC_RECEIPT_Y

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT056_REC_SEQ_R

         ,ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT056_REC_ENTRY_R).

         Aggregate Intermediate Results are computed globally, then

         placed in Spool 56.  The size of Spool 56 is estimated with

         high confidence to be 47,448,735 rows (5,741,296,935 bytes).

         The estimated time for this step is 20.14 seconds.

 21) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 4 (used to materialize

         view, derived table, table function or table operator tgt)

         (all_amps) (compressed columns allowed), which is built

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

         high confidence to be 47,448,735 rows (2,704,577,895 bytes).

         The estimated time for this step is 1.50 seconds.

      2) We do an all-AMPs RETRIEVE step from Spool 11 (Last Use) by

         way of an all-rows scan into Spool 60 (all_amps) (compressed

         columns allowed), which is duplicated on all AMPs.  Then we do

         a SORT to order Spool 60 by the hash code of (

         ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL.EDWT075_UNIT_OF_MEASURE_C,

         ED_TST_DATA.EDWT075_PRODPURCH_UOM_TBL.EDWT075_UOM_SOURCE_C).

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

         58,464 rows (1,636,992 bytes).  The estimated time for this

         step is 0.02 seconds.

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

     all-rows scan, which is joined to ED_TST_SUPT_DATA.stg by way of

     an all-rows scan with no residual conditions.  Spool 59 and

     ED_TST_SUPT_DATA.stg are right outer joined using a dynamic hash

     join, with a join condition of (

     "ED_TST_SUPT_DATA.stg.EDWC080_CURRENCY_ALPHA_C =

     (EDWC080_CURRENCY_ALPHA_C)").  The result goes into Spool 61

     (all_amps) (compressed columns allowed), which is built locally on

     the AMPs.  Then we do a SORT to order Spool 61 by the hash code of

     (ED_TST_SUPT_DATA.stg.EDWT075_UOM_SOURCE_C,

     ED_TST_SUPT_DATA.stg.EDWT075_UNIT_OF_MEASURE_C).  The size of

     Spool 61 is estimated with no confidence to be 206,034 rows (

     82,413,600 bytes).  The estimated time for this step is 0.07

     seconds.

 23) We execute the following steps in parallel.

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

         way of an all-rows scan into Spool 64 (all_amps) (compressed

         columns allowed), which is duplicated on all AMPs.  Then we do

         a SORT to order Spool 64 by the hash code of (

         EDWT072_COUNTRY_C).  The size of Spool 64 is estimated with no

         confidence to be 81,648 rows (1,632,960 bytes).  The estimated

         time for this step is 0.02 seconds.

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

         a RowHash match scan, which is joined to Spool 61 (Last Use)

         by way of a RowHash match scan.  Spool 60 and Spool 61 are

         right outer joined using a merge join, with a join condition

         of ("(EDWT075_UOM_SOURCE_C = (EDWT075_UOM_SOURCE_C )) AND

         (EDWT075_UNIT_OF_MEASURE_C = (EDWT075_UNIT_OF_MEASURE_C ))").

         The result goes into Spool 65 (all_amps) (compressed columns

         allowed), which is built locally on the AMPs.  Then we do a

         SORT to order Spool 65 by the hash code of (

         ED_TST_SUPT_DATA.stg.EDWT072_COUNTRY_C).  The size of Spool

         65 is estimated with no confidence to be 206,034 rows (

         83,237,736 bytes).  The estimated time for this step is 0.07

         seconds.

 24) We execute the following steps in parallel.

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

         a RowHash match scan, which is joined to Spool 65 (Last Use)

         by way of a RowHash match scan.  Spool 64 and Spool 65 are

         right outer joined using a merge join, with a join condition

         of ("EDWT072_COUNTRY_C = (EDWT072_COUNTRY_C)").  The result

         goes into Spool 68 (all_amps) (compressed columns allowed),

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

         estimated with no confidence to be 206,034 rows (84,061,872

         bytes).  The estimated time for this step is 0.08 seconds.

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

         of an all-rows scan into Spool 71 (all_amps) (compressed

         columns allowed), which is duplicated on all AMPs.  The size

         of Spool 71 is estimated with low confidence to be 2,637,264

         rows (81,755,184 bytes).  The estimated time for this step is

         0.06 seconds.

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

     all-rows scan, which is joined to Spool 71 by way of an all-rows

     scan.  Spool 68 and Spool 71 are left outer joined using a single

     partition hash join, with a join condition of (

     "(EDWT010_NATL_COMPANY_C = (EDWT010_NATL_COMPANY_C )) AND

     (EDWT320_REAL_PLANT_CODE_C = (EDWT320_REAL_PLANT_CODE_C ))").  The

     result goes into Spool 72 (all_amps) (compressed columns allowed),

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

     estimated with no confidence to be 206,034 rows (84,886,008 bytes).

     The estimated time for this step is 0.09 seconds.

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

     an all-rows scan into Spool 74 (all_amps) (compressed columns

     allowed), which is duplicated on all AMPs.  Then we do a SORT to

     order Spool 74 by the hash code of (

     ED_TST_DATA.EDWT010_NATIONAL_COMPANY_TBL.EDWT010_NATL_COMPANY_C).

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

     30,576 rows (703,248 bytes).  The estimated time for this step is

     0.02 seconds.

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

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

     all-rows scan.  Spool 72 and Spool 71 are left outer joined using

     a single partition hash join, with a join condition of (

     "(EDWT010_NATL_COMPANY_C = (EDWT010_NATL_COMPANY_C )) AND

     (EDWT320_PR_REAL_PLANT_CODE_C = (EDWT320_REAL_PLANT_CODE_C ))").

     The result goes into Spool 75 (all_amps) (compressed columns

     allowed), which is built locally on the AMPs.  Then we do a SORT

     to order Spool 75 by the hash code of (

     ED_TST_SUPT_DATA.stg.EDWT010_NATL_COMPANY_C).  The size of Spool

     75 is estimated with no confidence to be 206,034 rows (85,710,144

     bytes).  The estimated time for this step is 0.08 seconds.

 28) We execute the following steps in parallel.

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

         a RowHash match scan, which is joined to Spool 75 (Last Use)

         by way of a RowHash match scan.  Spool 74 and Spool 75 are

         right outer joined using a merge join, with a join condition

         of ("EDWT010_NATL_COMPANY_C = (EDWT010_NATL_COMPANY_C)").  The

         result goes into Spool 76 (all_amps) (compressed columns

         allowed), which is redistributed by the hash code of (

         ED_TST_SUPT_DATA.stg.EDWC020_PART_PREFIX_R,

         ED_TST_SUPT_DATA.stg.EDWC020_PART_BASE_R,

         ED_TST_SUPT_DATA.stg.EDWC020_PART_SUFFIX_R) to all AMPs.  The

         size of Spool 76 is estimated with no confidence to be 214,269

         rows (89,992,980 bytes).  The estimated time for this step is

         0.16 seconds.

      2) We do an all-AMPs RETRIEVE step from Spool 5 by way of an

         all-rows scan into Spool 80 (all_amps) (compressed columns

         allowed), which is redistributed by the hash code of (

         ED_DATA.EDWT020_SUPPLIER_SITE_TBL.EDWT020_SUPPLIER_SITE_C) to

         all AMPs.  The size of Spool 80 is estimated with high

         confidence to be 682,212 rows (14,326,452 bytes).  The

         estimated time for this step is 0.08 seconds.

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

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

     all-rows scan.  Spool 76 and Spool 79 are left outer joined using

     a single partition hash join, with a join condition of (

     "(EDWC020_PART_PREFIX_R = (EDWC020_PART_PREFIX_R )) AND

     ((EDWC020_PART_BASE_R = (EDWC020_PART_BASE_R )) AND

     (EDWC020_PART_SUFFIX_R = (EDWC020_PART_SUFFIX_R )))").  The result

     goes into Spool 81 (all_amps) (compressed columns allowed), which

     is redistributed by the hash code of (

     ED_TST_SUPT_DATA.stg.EDWT020_REC_SITE_C) to all AMPs.  The size

     of Spool 81 is estimated with no confidence to be 214,269 rows (

     91,707,132 bytes).  The estimated time for this step is 1.61

     seconds.

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

     an all-rows scan into Spool 82 (all_amps) (compressed columns

     allowed), which is redistributed by the hash code of (

     ED_DATA.EDWT020_SUPPLIER_SITE_TBL.EDWT020_SUPPLIER_SITE_C) to all

     AMPs.  Then we do a SORT to order Spool 82 by row hash.  The size

     of Spool 82 is estimated with high confidence to be 682,212 rows (

     13,644,240 bytes).  The estimated time for this step is 0.07

     seconds.

 31) We do an all-AMPs JOIN step from Spool 80 by way of an all-rows

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

     scan.  Spool 80 and Spool 81 are right outer joined using a single

     partition hash join, with a join condition of (

     "EDWT020_REC_SITE_C = (EDWT020_SUPPLIER_SITE_C)").  The result

     goes into Spool 83 (all_amps) (compressed columns allowed), which

     is built locally on the AMPs.  Then we do a SORT to order Spool 83

     by the hash code of (ED_TST_SUPT_DATA.stg.EDWT020_REC_SITE_C).

     The size of Spool 83 is estimated with no confidence to be 214,269

     rows (92,564,208 bytes).  The estimated time for this step is 0.11

     seconds.

 32) We do an all-AMPs JOIN step from Spool 82 (Last Use) by way of a

     RowHash match scan, which is joined to Spool 83 (Last Use) by way

     of a RowHash match scan.  Spool 82 and Spool 83 are right outer

     joined using a merge join, with a join condition of (

     "EDWT020_REC_SITE_C = (EDWT020_SUPPLIER_SITE_C)").  The result

     goes into Spool 87 (all_amps) (compressed columns allowed), which

     is redistributed by the hash code of (

     ED_TST_SUPT_DATA.stg.EDWT020_OSITE_SITE_C) to all AMPs.  The size

     of Spool 87 is estimated with no confidence to be 214,269 rows (

     93,421,284 bytes).  The estimated time for this step is 1.55

     seconds.

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

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

     all-rows scan.  Spool 80 and Spool 87 are right outer joined using

     a single partition hash join, with a join condition of (

     "EDWT020_OSITE_SITE_C = (EDWT020_SUPPLIER_SITE_C)").  The result

     goes into Spool 90 (all_amps) (compressed columns allowed), which

     is built locally on the AMPs.  The size of Spool 90 is estimated

     with no confidence to be 214,269 rows (94,278,360 bytes).  The

     estimated time for this step is 0.11 seconds.

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

     an all-rows scan into Spool 93 (all_amps) (compressed columns

     allowed), which is duplicated on all AMPs.  The size of Spool 93

     is estimated with high confidence to be 5,898,480 rows (

     123,868,080 bytes).  The estimated time for this step is 0.09

     seconds.

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

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

     all-rows scan.  Spool 90 and Spool 93 are left outer joined using

     a single partition hash join, with a join condition of (

     "EDWT069_JOB_C = (EDWT069_JOB_C)").  The result goes into Spool 94

     (all_amps) (compressed columns allowed) fanned out into 2 hash

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

     Spool 94 is estimated with no confidence to be 410,983 rows (

     182,476,452 bytes).  The estimated time for this step is 0.16

     seconds.

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

     an all-rows scan into Spool 95 (all_amps) (compressed columns

     allowed) fanned out into 2 hash join partitions, which is

     redistributed by the hash code of (

     ED_TST_DATA.EDWT056_ERES_PRICE_RCPT_TBL.EDWT020_OSITE_SITE_C) to

     all AMPs.  The size of Spool 95 is estimated with high confidence

     to be 47,448,735 rows (2,324,988,015 bytes).  The estimated time

     for this step is 2.21 seconds.

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

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

     all-rows scan.  Spool 94 and Spool 95 are left outer joined using

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

     non-matching on left table ("NOT (EDWC020_BUS_ID_K IS NULL)"),

     with a join condition of ("(EDWT020_OSITE_SITE_C =

     (EDWT020_OSITE_SITE_C )) AND ((EDWT010_NATL_COMPANY_C =

     (EDWT010_NATL_COMPANY_C )) AND ((EDWT320_REAL_PLANT_CODE_C =

     (EDWT320_REAL_PLANT_CODE_C )) AND ((EDWT056_REC_RECEIPT_Y =

     EDWT056_REC_RECEIPT_Y) AND ((EDWT056_REC_SEQ_R = EDWT056_REC_SEQ_R)

     AND ((EDWT056_REC_ENTRY_R = EDWT056_REC_ENTRY_R) AND

     (EDWC020_BUS_ID_K = EDWC020_BUS_ID_K ))))))").  The result goes

     into Spool 58 (group_amps), which is built locally on the AMPs.

     The size of Spool 58 is estimated with no confidence to be 410,983

     rows (201,381,670 bytes).  The estimated time for this step is

     0.64 seconds.

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

     in processing the request.

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

     of statement 1.

 

Junior Contributor

Re: Performance Tuning

The Explain is useless, you need to check dbc.QryLogStepsV which step(s) has long runtime.

 

And you might compare dbc.QryLogExplainV to see if there are differences between previous and current plan.

Enthusiast

Re: Performance Tuning

Thank you, for the suggestion but our system is not enabled to store data in those objects.

 

I was able to capture the expalin plan from view point and could see the estimated and actual time in that and i dont see any huge difference in th etime which can impcat the performcance of the query.

 

Your suggestions will be much appreciated. 

Junior Contributor

Re: Performance Tuning

Well, every estimated time in the Explain is sub-second, if there's no difference between estimated and actual you should not be able to find this query in MyQueries because it finished in a few seconds :-)

Enthusiast

Re: Performance Tuning

SyntaxEditor Code Snippet

Following is the query which is taking more time thann usual as said.

SELECT
stg.ETT056_REC_ERES_TYP_C,STG.ETT056_REC_ERES_EFF_IN_Y,COALESCE(tgt.ETT056_REC_ERES_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT056_PREV_EFF_IN_Y,stg.ETT072_COUNTRY_C,COALESCE(t072.ETT072_PURCH_CTRY_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT072_PURCH_CTRY_EFF_IN_Y,stg.ETT020_OSITE_SITE_C,COALESCE(t020.ETT020_SITE_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT020_OSITE_SITE_EFF_IN_Y,stg.ETT056_REC_RECEIPT_Y,stg.ETT320_REAL_PLANT_CODE_C,COALESCE(t320.ETT320_PLANT_K,0) ETT320_PLANT_K,stg.ETT056_REC_SUPP_PART_R,stg.ETT056_REC_SEQ_R,stg.ETT056_REC_INV_SBI_R,stg.ETT056_REC_INV_SBI_Y,stg.ETT056_REC_RETRO_IND_C,stg.ETT056_REC_TAX_IND_C,stg.ETT056_REC_TAX_C,stg.ETT056_REC_UNIT_PRICE_A,stg.ETT056_REC_FNC_UNIT_PRICE_A,stg.ETT056_REC_NETTO_A,stg.ETT056_REC_FNC_NETTO_A,stg.ETT056_REC_VATC_NETTO_A,stg.ETT056_VAT_A,stg.ETT056_REC_FNC_VAT_A,stg.ETT056_REC_VATC_VAT_A,stg.ETT056_REC_VAT_P,stg.ETT056_REC_DISC_A ,stg.ETT056_REC_FNC_DISC_A,stg.ETT056_REC_VATC_DISC_A,stg.ETT056_REC_DISC_P,stg.ETT056_REC_UNIT_STD_COST_A,stg.ETT056_REC_EXT_STD_COST_A,stg.ETC080_CURRENCY_ALPHA_C,COALESCE(c080.ETC080_EXCHCURR_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETC080_EXCHCURR_EFF_IN_Y,stg.ETT075_UNIT_OF_MEASURE_C,stg.ETT075_UOM_SOURCE_C,COALESCE( t075.ETT075_UOM_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT075_UOM_EFF_IN_Y,stg.ETT056_REC_Q,stg.ETT069_JOB_C,COALESCE(t069.ETT069_JOB_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT069_JOB_EFF_IN_Y,stg.ETT056_REC_PERF_R,stg.ETT056_REC_PURC_ORD_R,stg.ETT056_REC_PSO_FINIS_R,stg.ETT020_REC_SITE_C,COALESCE(t020a.ETT020_SITE_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT020_REC_SITE_EFF_IN_Y,stg.ETT056_REC_SHPMT_Y,stg.ETT056_REC_REAL_SYS_ID_C,stg.ETT056_REC_TRANS_C,stg.ETT056_REC_ENTRY_TMESTP_S,stg.ETT056_REC_ENTRY_R,COALESCE(t955.ETC020_BUS_ID_K,0) ETC020_BUS_ID_K,COALESCE(t955.ETC020_PART_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETC020_PART_EFF_IN_Y,stg.ETC020_PART_PREFIX_R,stg.ETC020_PART_BASE_R,stg.ETC020_PART_SUFFIX_R,stg.ETC020_PART_SEG4_R ,stg.ETC020_PART_SEG5_R ,stg.ETC020_PART_SEG6_R ,stg.ETC002_PART_OWNER_R,stg.ETT056_REC_PO_VERS_LAST_C,stg.ETT056_REC_REV_LAST_C,stg.ETT056_REC_UNIT_PRIC_LST_A,stg.ETT056_REC_WIPS_FI_SQ_LST_A,stg.ETT056_REC_WIPS_RE_SQ_LST_A,stg.ETT056_REC_BILLING_METHOD_C,stg.ETT320_PR_REAL_PLANT_CODE_C,COALESCE(t320a.ETT320_PLANT_K,0) ETT320_PR_PLANT_K,stg.ETT020_PRICE_SITE_C,COALESCE(t020b.ETT020_SITE_EFF_IN_Y,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT020_PRICE_SITE_EFF_IN_Y,stg.ETT056_REC_ADVICE_NOTE_C,stg.ETT056_REC_FREIGHT_COND_C,stg.ETT010_NATL_COMPANY_C,COALESCE(t010.ETT010_NTLCO_EFF_IN_Y ,cast('0001-01-01' as date format 'YYYY-MM-DD')) ETT010_NTLCO_EFF_IN_Y,stg.ETT056_REC_BLANKET_ORD_C,stg.ETT056_REC_SUPP_REF_X,stg.ETT056_REC_PROTOTYPE_IND_C,stg.ETT056_EFF_OUT_Y,stg.ETT056_SRC_SYS_C,stg.ETT056_CREATE_PROC_R,stg.ETT056_CREATE_S,stg.ETT056_LAST_UPDT_PROC_R,stg.ETT056_LAST_UPDT_S FROM ET_TST_SUPT_DATA.ETT056_ST_ERES_PRICE_RCPT_TBL stg LEFT OUTER jOIN ET_TST_VIEW.ETT072_PURCH_COUNTRY_COMB_VW t072 ON (stg.ETT072_COUNTRY_C = t072.ETT072_COUNTRY_C)LEFT OUTER jOIN (SELECT ETT020_SUPPLIER_SITE_C, MAX(ETT020_SITE_EFF_IN_Y)ETT020_SITE_EFF_IN_Y --Rows 682231 FROM ET_VIEW.ETT020_SUPPLIER_SITE_VW GROUP BY ETT020_SUPPLIER_SITE_C) t020 ON (stg.ETT020_OSITE_SITE_C = t020.ETT020_SUPPLIER_SITE_C)LEFT OUTER JOIN (SELECT ETT010_NATL_COMPANY_C, --Rows 918 ETT320_REAL_PLANT_CODE_C, MAX(ETT320_PLANT_K) ETT320_PLANT_K, MAX(ETT010_NTLCO_EFF_IN_Y) ETT010_NTLCO_EFF_IN_Y FROM ET_TST_VIEW.ETT320_PLANT_DTL_VW GROUP BY ETT010_NATL_COMPANY_C, ETT320_REAL_PLANT_CODE_C )t320 ON (stg.ETT010_NATL_COMPANY_C = t320.ETT010_NATL_COMPANY_C AND stg.ETT320_REAL_PLANT_CODE_C = t320.ETT320_REAL_PLANT_CODE_C)LEFT OUTER JOIN (SELECT ETC080_CURRENCY_ALPHA_C, --Rows 150 MAX(ETC080_EXCHCURR_EFF_IN_Y) ETC080_EXCHCURR_EFF_IN_Y FROM ET_VIEW.ETC080_EXCH_CURRENCY_VW GROUP BY ETC080_CURRENCY_ALPHA_C) c080 ON (stg.ETC080_CURRENCY_ALPHA_C = c080 .ETC080_CURRENCY_ALPHA_C)LEFT OUTER JOIN (SELECT ETT075_UNIT_OF_MEASURE_C,ETT075_UOM_SOURCE_C, --Rows 174 MAX(ETT075_UOM_EFF_IN_Y) ETT075_UOM_EFF_IN_Y FROM ET_TST_VIEW.ETT075_PRODPURCH_UOM_VW GROUP BY ETT075_UNIT_OF_MEASURE_C,ETT075_UOM_SOURCE_C) t075 ON (stg.ETT075_UOM_SOURCE_C = t075.ETT075_UOM_SOURCE_C AND stg.ETT075_UNIT_OF_MEASURE_C = t075.ETT075_UNIT_OF_MEASURE_C)LEFT OUTER JOIN (SELECT ETT069_JOB_C, --Rows 17558 MAX(ETT069_JOB_EFF_IN_Y) ETT069_JOB_EFF_IN_Y FROM ET_TST_VIEW.ETT069_POSITION_VW GROUP BY ETT069_JOB_C) t069 ON ( stg.ETT069_JOB_C = t069.ETT069_JOB_C)LEFT OUTER JOIN (SELECT ETT020_SUPPLIER_SITE_C, --Rows 682231 MAX(ETT020_SITE_EFF_IN_Y) ETT020_SITE_EFF_IN_Y FROM ET_VIEW.ETT020_SUPPLIER_SITE_VW GROUP BY ETT020_SUPPLIER_SITE_C) t020a ON (stg.ETT020_REC_SITE_C = t020a.ETT020_SUPPLIER_SITE_C)LEFT OUTER JOIN (SELECT ETC020_PART_PREFIX_R, --Rows 11803314 ETC020_PART_BASE_R, ETC020_PART_SUFFIX_R, ETC020_BUS_ID_K, MAX(ETC020_PART_EFF_IN_Y) ETC020_PART_EFF_IN_Y FROM ET_TST_VIEW.ETT955_CURR_PART_D_VW WHERE ETC020_PART_SEG4_R = '~!@' AND ETC020_PART_SEG5_R = '~!@' AND ETC020_PART_SEG6_R = '~!@' AND ETC002_PART_OWNER_R = ' 1' GROUP BY ETC020_PART_PREFIX_R, ETC020_PART_BASE_R, ETC020_PART_SUFFIX_R, ETC020_BUS_ID_K) t955 ON (stg.ETC020_PART_PREFIX_R = t955.ETC020_PART_PREFIX_R AND stg.ETC020_PART_BASE_R = t955.ETC020_PART_BASE_R AND stg.ETC020_PART_SUFFIX_R = t955.ETC020_PART_SUFFIX_R)LEFT OUTER JOIN (SELECT ETT010_NATL_COMPANY_C, --Rows 918 ETT320_REAL_PLANT_CODE_C, MAX(ETT320_PLANT_K) ETT320_PLANT_K FROM ET_TST_VIEW.ETT320_PLANT_DTL_VW GROUP BY ETT010_NATL_COMPANY_C, ETT320_REAL_PLANT_CODE_C ) t320a ON (stg.ETT010_NATL_COMPANY_C = t320a.ETT010_NATL_COMPANY_C AND stg.ETT320_PR_REAL_PLANT_CODE_C = t320a.ETT320_REAL_PLANT_CODE_C)LEFT OUTER JOIN (SELECT ETT010_NATL_COMPANY_C, --Rows 91 MAX(ETT010_NTLCO_EFF_IN_Y) ETT010_NTLCO_EFF_IN_Y FROM ET_TST_VIEW.ETT010_NATIONAL_COMPANY_VW GROUP BY ETT010_NATL_COMPANY_C ) t010 ON (stg.ETT010_NATL_COMPANY_C = t010.ETT010_NATL_COMPANY_C) LEFT OUTER JOIN (SELECT ETT020_SUPPLIER_SITE_C, --Rows 682231 MAX(ETT020_SITE_EFF_IN_Y) ETT020_SITE_EFF_IN_Y FROM ET_VIEW.ETT020_SUPPLIER_SITE_VW GROUP BY ETT020_SUPPLIER_SITE_C) t020b ON (stg.ETT020_REC_SITE_C = t020b.ETT020_SUPPLIER_SITE_C)LEFT OUTER JOIN (SELECT ETC020_BUS_ID_K, --Rows 47544183 ETT020_OSITE_SITE_C, ETT010_NATL_COMPANY_C, ETT320_REAL_PLANT_CODE_C, ETT056_REC_RECEIPT_Y , ETT056_REC_SEQ_R , ETT056_REC_ENTRY_R , MAX(ETT056_REC_ERES_EFF_IN_Y) ETT056_REC_ERES_EFF_IN_Y FROM ET_TST_DATA.ETT056_ERES_PRICE_RCPT_TBL GROUP BY ETC020_BUS_ID_K, ETT020_OSITE_SITE_C, ETT010_NATL_COMPANY_C,ETT320_REAL_PLANT_CODE_C, ETT056_REC_RECEIPT_Y , ETT056_REC_SEQ_R ,ETT056_REC_ENTRY_R) tgt ON (stg.ETT020_OSITE_SITE_C = tgt.ETT020_OSITE_SITE_C AND stg.ETT010_NATL_COMPANY_C = tgt.ETT010_NATL_COMPANY_C AND stg.ETT320_REAL_PLANT_CODE_C = tgt.ETT320_REAL_PLANT_CODE_C AND stg.ETT056_REC_RECEIPT_Y = tgt.ETT056_REC_RECEIPT_Y AND stg.ETT056_REC_SEQ_R = tgt.ETT056_REC_SEQ_R AND stg.ETT056_REC_ENTRY_R = tgt.ETT056_REC_ENTRY_R AND t955.ETC020_BUS_ID_K = tgt.ETC020_BUS_ID_K);

 

 

Junior Contributor

Re: Performance Tuning

If this is the query plus the matching explain and the estimated (each sub-second) vs.actual timing from QueryMonitor are close there's no problem running it. Which step(s) got a long runtime?