Cost of the query is more

General

Cost of the query is more

Hi ,

Can anybody help me tuning this query.

Cost is more for this query.

Please find the explain plan.

Explain SELECT

Q1.PRODUCT_KEY                

,Q1.SALES_TERRITORY_KEY          

,Q2.POS_SALES_TERRITORY_KEY

,Q1.DV_FISCAL_YEAR_MTH_NUMBER_INT

,1 TRIANGULATION_TYPE_ID_INT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_NET_PRICE_AMT      DV_COMP_US_NET_PRICE_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_NET_LIST_PRICE_AMT  DV_COMP_US_NET_LIST_PRICE_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_GROSS_LIST_PRICE_AM  DV_COMP_US_GROSS_LIST_PRICE_AM

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_NET_COST_AMT       DV_COMP_US_NET_COST_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_GROSS_REV_AMT       DV_COMP_US_GROSS_REV_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_NET_REV_AMT        DV_COMP_US_NET_REV_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_2TIER_CMDM_AMT     DV_COMP_US_2TIER_CMDM_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_GROSS_COST_AMT     DV_COMP_US_GROSS_COST_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_COMP_US_STANDARD_PRICE_AMT   DV_COMP_US_STANDARD_PRICE_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DD_EXTENDED_NET_QTY           DD_EXTENDED_NET_QTY

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DD_EXTENDED_GROSS_QTY    DD_EXTENDED_GROSS_QTY

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_CREDIT_MEMO_AMT    DV_CREDIT_MEMO_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_DEBIT_MEMO_AMT    DV_DEBIT_MEMO_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_INV_REV_BASE_LIST_AMT    DV_INV_REV_BASE_LIST_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_SHIPPED_REV_AMT    DV_SHIPPED_REV_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_NET_ADJ_AMT    DV_NET_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_REV_STANDARD_COST_AMT    DV_REV_STANDARD_COST_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_DIRECT_REV_ADJ_AMT    DV_DIRECT_REV_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_DIRECT_COST_ADJ_AMT    DV_DIRECT_COST_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_INDIRECT_REV_ADJ_AMT    DV_INDIRECT_REV_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_INDIRECT_COGS_ADJ_AMT    DV_INDIRECT_COGS_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_GMB_COGS_ADJ_AMT    DV_GMB_COGS_ADJ_AMT 

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_EXCESS_OBSOLETE_ADJ_AMT    DV_EXCESS_OBSOLETE_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_OVERHEAD_ADJ_AMT    DV_OVERHEAD_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_VARIANCE_ADJ_AMT    DV_VARIANCE_ADJ_AMT

,Q2.SCMS_POS_ALLOCATION_RATIO*Q3.SCMS_BKG_MIX_RATIO*Q1.DV_WARRANTY_ADJ_AMT    DV_WARRANTY_ADJ_AMT

FROM

STGDB.WI_GL_REV_MSR_WWDISTI   Q1

INNER JOIN ETLVWDB.EL_REV_DISTI_SL3_SL2_MAP MAPPING

ON ( Q1.L3_SALES_TERRITORY_NAME_CODE =MAPPING.L3_SALES_TERRITORY_NAME_CD)

INNER JOIN STGDB.WI_BKG_MSR_ICPM_POS_ALC_VLD Q2

ON(MAPPING.L2_SALES_TERRITORY_NAME_CD =Q2.L2_SALES_TERRITORY_NAME_CODE

AND Q2.ISO_COUNTRY_CODE=Q1.ISO_COUNTRY_CODE

AND MAPPING.L3_SALES_TERRITORY_NAME_CD =Q2.L3_SALES_TERRITORY_NAME_CODE )

INNER JOIN STGDB.WI_REV_MSR_ICPM_BKG_MIX Q3

ON ( Q3.L2_SALES_TERRITORY_NAME_CODE =MAPPING.L2_SALES_TERRITORY_NAME_CD  

AND Q3.ISO_COUNTRY_CODE=Q1.ISO_COUNTRY_CODE

AND Q3.SALES_COVERAGE_CODE=Q2.SALES_COVERAGE_CODE

AND Q3.L3_SALES_TERRITORY_NAME_CODE =MAPPING.L3_SALES_TERRITORY_NAME_CD)

WHERE  Q1.ISO_COUNTRY_CODE <>'UNK'

  1) First, we lock ETLONLYDB.EL_REV_DISTI_SL3_SL2_MAP in view

     ETLVWDB.EL_REV_DISTI_SL3_SL2_MAP for access, we lock STGDB.Q3 for

     access, we lock STGDB.Q2 for access, and we lock STGDB.Q1 for

     access.

  2) Next, we execute the following steps in parallel.

       1) We do an all-AMPs RETRIEVE step from

          ETLONLYDB.EL_REV_DISTI_SL3_SL2_MAP in view

          ETLVWDB.EL_REV_DISTI_SL3_SL2_MAP by way of an all-rows scan

          with no residual conditions into Spool 3 (all_amps)

          (compressed columns allowed), which is redistributed by the

          hash code of (

          ETLONLYDB.EL_REV_DISTI_SL3_SL2_MAP.L3_SALES_TERRITORY_NAME_CD,

          ETLONLYDB.EL_REV_DISTI_SL3_SL2_MAP.L2_SALES_TERRITORY_NAME_CD)

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

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

          391 rows (14,467 bytes).  The estimated time for this step is

          0.01 seconds.

       2) We do an all-AMPs RETRIEVE step from STGDB.Q2 by way of an

          all-rows scan with a condition of ("(NOT

          (STGDB.Q2.SALES_COVERAGE_CODE IS NULL )) AND

          ((STGDB.Q2.ISO_COUNTRY_CODE > 'UNK') OR

          (STGDB.Q2.ISO_COUNTRY_CODE < 'UNK'))") into Spool 4

          (all_amps) (compressed columns allowed), which is

          redistributed by the hash code of (

          STGDB.Q2.L3_SALES_TERRITORY_NAME_CODE,

          STGDB.Q2.L2_SALES_TERRITORY_NAME_CODE) to all AMPs.  Then we

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

          is estimated with high confidence to be 40,267 rows (

          3,825,365 bytes).  The estimated time for this step is 0.08

          seconds.

       3) We do an all-AMPs RETRIEVE step from STGDB.Q3 by way of an

          all-rows scan with a condition of (

          "(STGDB.Q3.ISO_COUNTRY_CODE > 'UNK') OR

          (STGDB.Q3.ISO_COUNTRY_CODE < 'UNK')") into Spool 5 (all_amps)

          (compressed columns allowed), which is redistributed by the

          hash code of (STGDB.Q3.L3_SALES_TERRITORY_NAME_CODE,

          STGDB.Q3.ISO_COUNTRY_CODE) to all AMPs.  The size of Spool 5

          is estimated with low confidence to be 13,274 rows (

          1,207,934 bytes).  The estimated time for this step is 0.02

          seconds.

       4) We do an all-AMPs RETRIEVE step from STGDB.Q1 by way of an

          all-rows scan with a condition of (

          "(STGDB.Q1.ISO_COUNTRY_CODE > 'UNK') OR

          (STGDB.Q1.ISO_COUNTRY_CODE < 'UNK')") into Spool 6 (all_amps)

          (compressed columns allowed), which is redistributed by the

          hash code of (STGDB.Q1.L3_SALES_TERRITORY_NAME_CODE,

          STGDB.Q1.ISO_COUNTRY_CODE) to all AMPs.  The size of Spool 6

          is estimated with high confidence to be 92,661 rows (

          26,686,368 bytes).  The estimated time for this step is 0.94

          seconds.

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

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

     all-rows scanSpool 5 and Spool 6 are joined using a single

     partition hash join, with a join condition of (

     "(L3_SALES_TERRITORY_NAME_CODE = L3_SALES_TERRITORY_NAME_CODE) AND

     (ISO_COUNTRY_CODE = ISO_COUNTRY_CODE)").  The result goes into

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

     locally on the AMPs.  Then we do a SORT to order Spool 7 by the

     hash code of (STGDB.Q3.L3_SALES_TERRITORY_NAME_CODE,

     STGDB.Q3.ISO_COUNTRY_CODE).  The size of Spool 7 is estimated with

     low confidence to be 202,433 rows (74,090,478 bytes).  The

     estimated time for this step is 0.04 seconds.

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

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

     of a RowHash match scan.  Spool 3 and Spool 4 are joined using a

     merge join, with a join condition of (

     "(L2_SALES_TERRITORY_NAME_CD = L2_SALES_TERRITORY_NAME_CODE) AND

     (L3_SALES_TERRITORY_NAME_CD = L3_SALES_TERRITORY_NAME_CODE)").

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

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

     ETLONLYDB.EL_REV_DISTI_SL3_SL2_MAP.L3_SALES_TERRITORY_NAME_CD,

     STGDB.Q2.ISO_COUNTRY_CODE) to all AMPs.  Then we do a SORT to

     order Spool 8 by row hash.  The size of Spool 8 is estimated with

     low confidence to be 24,335 rows (2,895,865 bytes).  The estimated

     time for this step is 0.04 seconds.

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

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

     of a RowHash match scan.  Spool 7 and Spool 8 are joined using a

     merge join, with a join condition of (

     "(L3_SALES_TERRITORY_NAME_CODE = L3_SALES_TERRITORY_NAME_CD) AND

     ((L3_SALES_TERRITORY_NAME_CODE = L3_SALES_TERRITORY_NAME_CODE) AND

     ((ISO_COUNTRY_CODE = ISO_COUNTRY_CODE) AND

     ((L3_SALES_TERRITORY_NAME_CODE = L3_SALES_TERRITORY_NAME_CODE)

     AND((SALES_COVERAGE_CODE = SALES_COVERAGE_CODE) AND

     ((ISO_COUNTRY_CODE = ISO_COUNTRY_CODE) AND

     ((L2_SALES_TERRITORY_NAME_CODE = L2_SALES_TERRITORY_NAME_CODE) AND

     ((L3_SALES_TERRITORY_NAME_CODE = L3_SALES_TERRITORY_NAME_CD) AND

     (L2_SALES_TERRITORY_NAME_CODE = L2_SALES_TERRITORY_NAME_CD

     ))))))))").  The result goes into Spool 2 (group_amps), which is

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

     low confidence to be 693,344 rows (314,778,176 bytes).  The

     estimated time for this step is 0.04 seconds.

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

     in processing the request.

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

     statement 1.  The total estimated time is 1.06 seconds.

2 REPLIES
Supporter

Re: Cost of the query is more

Hi kalyan3in,

do you expect that others do your job for free?

At least I find this kind of approach a bit difficult. 

Beside this with the provided info nobody will be able to help. Query tuning requires all available infos, SLA, DDLs, stats, real row counts etc and a lot of experiance and knowledge.

Enthusiast

Re: Cost of the query is more

Hi Kalyan,

Follow the below steps.

1) diagnostic helpstats on for session;

2) Explain plan of your query.

You can see the recomended stats at the end of explain plan.

Thanks,

Murali Udayagiri