Estimate and Actual are different and causing PJ.

Database
BPP
Enthusiast

Estimate and Actual are different and causing PJ.

Hello All;  I am looking for your valuable suggestions on this. Query which used to work with out any issues is now giving hard time.  Same query runs fine (<3 min) on prod but goes into product join mode on QA system.  We have made sure that indexes, stats and views are all same (data is one week less on QA).    

Below is the sql and explain.  I think estimate in step 13 is causing a product join in step 16 and this is where query hands. Optimizer is estimating there will be 5 rows after aggregating a 5 million result set from the step 12 (2).  This estimate is wrong as actual is way way more.   With the knowledge I got about tuning, I tried everything that I can to find out why it is generating different plan (I can say bad plan) on QA system but no luck so far. 

Query users are not ready to change the query so I have to make the plan to go back what it was or come up with solid reason so I can convince them to change the query.  Help me out!!
Select  
m1.CLM_HDR_KEY,
m1.CLM_NBR as CLM_NBR,
m1.LOAD_DT,
Cast(Cast(m1.CLM_FIN_PRCS_DT as date format 'yyyy/mm') as char(7)) as CLM_RPT_YR_MNTH_DS,
Trim(m1.Grp_Nbr)||Trim(m1.Subgrp_nbr) as GRP_ID,
m1.TYPE_OF_CLM_CD,
m1.NETPAY_TOT_CLM_AMT as Tot_Netpay_Amt,

(Select Sum(Coalesce(m2.CHG_AMT,0) - Coalesce(m2.CNTRC_PROV_SAV_AMT,0))
from OEAQ2.DW_CLM_LINE m2
where m2.CLM_HDR_KEY=m1.CLM_HDR_KEY
AND m1.LOAD_DT=m2.LOAD_DT
--group by m2.clm_hdr_key, m2.load_dt
) as Tot_Alw_Amt --> (This is what being aggregated in step 13)
from
OEAQ2.V_CLM_HDR_SUBSET_5_YRS m1

where m1.Load_dt between '2011-01-01' and '2011-01-31'
and m1.EXCL_CD = ' ';

Explain:


Explain:

Explain Select  

 m1.CLM_HDR_KEY,

 m1.CLM_NBR as CLM_NBR,

 m1.LOAD_DT,

 Cast(Cast(m1.CLM_FIN_PRCS_DT as date format 'yyyy/mm') as char(7)) as CLM_RPT_YR_MNTH_DS,

 Trim(m1.Grp_Nbr)||Trim(m1.Subgrp_nbr) as GRP_ID,

 m1.TYPE_OF_CLM_CD,

 m1.NETPAY_TOT_CLM_AMT as Tot_Netpay_Amt,

 (Select  Sum(Coalesce(m2.CHG_AMT,0) - Coalesce(m2.CNTRC_PROV_SAV_AMT,0)) 

            from OEAQ2.DW_CLM_LINE m2

           where m2.CLM_HDR_KEY=m1.CLM_HDR_KEY

            AND m1.LOAD_DT=m2.LOAD_DT

            --group by m2.clm_hdr_key, m2.load_dt

            ) as Tot_Alw_Amt

 from 

           OEAQ2.V_CLM_HDR_SUBSET_5_YRS m1

where m1.Load_dt between '2011-01-01' and '2011-01-31' 

           and m1.EXCL_CD = ' ';

  1) First, we lock OEAQ2_T.LN1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS

     for access, we lock OEAQ2_T.HDR2 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS for access, we lock OEAQ2_T.HDR1 in

     view OEAQ2.V_CLM_HDR_SUBSET_5_YRS for access, we lock

     OEAQ2_T.PF_CLM_CodeDesc_LKP in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS

     for access, we lock OEAQ2_T.JI1_CLMLINERMK in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS for access, we lock

     OEAQ2_T.DW_CLM_HDR_MSG in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS for

     access, and we lock OEAQ2_T.DW_MBR_VERS in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS for access. 

  2) Next, we do a single-AMP RETRIEVE step from OEAQ2_T.DW_MBR_VERS in

     view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of the primary index

     "OEAQ2_T.DW_MBR_VERS in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY

     = -1" with no residual conditions into Spool 7 (one-amp), which is

     built locally on that AMP.  The size of Spool 7 is estimated with

     high confidence to be 2 rows (114 bytes).  The estimated time for

     this step is 0.00 seconds. 

  3) We do a single-AMP DISPATCHER RETRIEVE step from Spool 7 (Last

     Use) by way of an all-rows scan and send the rows back to the

     Dispatcher. 

  4) We do a single-AMP RETRIEVE step from OEAQ2_T.DW_MBR_VERS in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of the primary index

     "OEAQ2_T.DW_MBR_VERS in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY

     = -1" with no residual conditions into Spool 8 (one-amp), which is

     built locally on that AMP.  The size of Spool 8 is estimated with

     high confidence to be 2 rows (38 bytes).  The estimated time for

     this step is 0.00 seconds. 

  5) We do a single-AMP DISPATCHER RETRIEVE step from Spool 8 (Last

     Use) by way of an all-rows scan and send the rows back to the

     Dispatcher. 

  6) We do an all-AMPs SUM step to aggregate from 31 partitions of

     OEAQ2_T.LN1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS with a condition

     of ("(OEAQ2_T.LN1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >=

     DATE '2011-01-01') AND ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <= DATE '2011-01-31') AND

     ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '7C3')OR

     ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '7C2') OR

     ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '7C4') OR

     ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '9D2') OR

     ((OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '9D3') OR

     (OEAQ2_T.LN1 in view

     OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_TYPE_OF_SVC_CD = '9L3')))))))")

     , grouping by field1 ( OEAQ2_T.LN1.CLM_HDR_KEY

     ,OEAQ2_T.LN1.LOAD_DT).  Aggregate Intermediate Results are

     computed locally, then placed in Spool 10.  The size of Spool 10

     is estimated with low confidence to be 457,214 rows (18,288,560

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

  7) We execute the following steps in parallel. 

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

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

          view, derived table or table function EXCL_CD) (all_amps)

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

          AMPs.  The size of Spool 5 is estimated with low confidence

          to be 457,214 rows (14,630,848 bytes).  The estimated time

          for this step is 0.03 seconds. 

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

          OEAQ2_T.JI1_CLMLINERMK in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS

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

          , grouping by field1 ( OEAQ2_T.JI1_CLMLINERMK.CLM_HDR_KEY). 

          Aggregate Intermediate Results are computed locally, then

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

          high confidence to be 252,180,992 rows (7,313,248,768 bytes). 

          The estimated time for this step is 1 minute and 27 seconds. 

  8) We execute the following steps in parallel. 

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

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

          view, derived table or table function RMK_CD) (all_amps)

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

          AMPs.  The size of Spool 4 is estimated with high confidence

          to be 252,180,992 rows (8,321,972,736 bytes).  The estimated

          time for this step is 9.21 seconds. 

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

          OEAQ2_T.DW_MBR_VERS in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by

          way of an all-rows scan with a condition of (

          "(OEAQ2_T.DW_MBR_VERS in view

          OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY >= 0) OR

          (OEAQ2_T.DW_MBR_VERS in view

          OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY <= -2)")

          , grouping by field1 ( OEAQ2_T.DW_MBR_VERS.MBR_KEY

          ,OEAQ2_T.DW_MBR_VERS.SUB_ID ,OEAQ2_T.DW_MBR_VERS.DEP_ID). 

          Aggregate Intermediate Results are computed locally, then

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

          high confidence to be 13,266,543 rows (809,259,123 bytes). 

          The estimated time for this step is 6.88 seconds. 

  9) We execute the following steps in parallel. 

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

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

          view, derived table or table function MBR_VERS2) (all_amps)

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

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

          to be 13,266,543 rows (331,663,575 bytes).  The estimated

          time for this step is 0.34 seconds. 

       2) We do an all-AMPs RETRIEVE step from 31 partitions of

          OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS with a

          condition of ("(OEAQ2_T.HDR1 in view

          OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >= DATE '2011-01-01')

          AND ((OEAQ2_T.HDR1 in view

          OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <= DATE '2011-01-31')

          AND (((OEAQ2_T.HDR1 in view

          OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY >= 0) OR (OEAQ2_T.HDR1

          in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY <= -2 )) AND

          (OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_CLM_CD

          = 'A ')))") into Spool 18 (all_amps) (compressed columns

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

          18 is estimated with low confidence to be 63,216 rows (

          1,580,400 bytes).  The estimated time for this step is 0.50

          seconds. 

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

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

     all-rows scan with a condition of ("(MBR_VERS2.MBR_KEY >= 0) OR

     (MBR_VERS2.MBR_KEY <= -2)").  Spool 18 and Spool 1 are joined

     using a single partition hash_ join, with a join condition of (

     "MBR_KEY = MBR_KEY").  The result goes into Spool 3 (all_amps),

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

     estimated with low confidence to be 439 rows (16,243 bytes).  The

     estimated time for this step is 0.14 seconds. 

 11) We execute the following steps in parallel. 

      1) We do an all-AMPs RETRIEVE step from 31 partitions of

         OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS with a

         condition of ("(OEAQ2_T.HDR1 in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.MBR_KEY = -1) AND ((OEAQ2_T.HDR1

         in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >= DATE

         '2011-01-01') AND ((OEAQ2_T.HDR1 in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <= DATE '2011-01-31') AND

         (OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.SRC_CLM_CD

         = 'A ')))") locking for access into Spool 3 (all_amps), which

         is built locally on the AMPs.  The size of Spool 3 is

         estimated with low confidence to be 6,115 rows (226,255 bytes). 

         The estimated time for this step is 0.49 seconds. 

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

         OEAQ2_T.DW_CLM_HDR_MSG in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by

         way of an all-rows scan with no residual conditions

         , grouping by field1 ( OEAQ2_T.DW_CLM_HDR_MSG.CLM_HDR_KEY). 

         Aggregate Intermediate Results are computed locally, then

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

         low confidence to be 1 row (33 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 20 (Last Use) by

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

         view, derived table or table function MESSAGE) (all_amps)

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

         AMPs.  The size of Spool 2 is estimated with low confidence to

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

         seconds. 

      2) We do an all-AMPs JOIN step from 31 partitions of OEAQ2_T.HDR1

         in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of a RowHash match

         scan with a condition of ("(OEAQ2_T.HDR1 in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >= DATE '2011-01-01') AND

         (OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <=

         DATE '2011-01-31')"), which is joined to OEAQ2_T.LN1 by way of

         a RowHash match scan with no residual conditions locking

         OEAQ2_T.HDR1 for access and OEAQ2_T.LN1 for access. 

         OEAQ2_T.HDR1 and OEAQ2_T.LN1 are joined using a rowkey-based

         merge join, with a join condition of ("(OEAQ2_T.HDR1.LOAD_DT =

         OEAQ2_T.LN1.LOAD_DT) AND (OEAQ2_T.LN1.CLM_HDR_KEY =

         OEAQ2_T.HDR1.CLM_HDR_KEY)").  The result goes into Spool 24

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

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

         low confidence to be 5,522,575 rows (198,812,700 bytes).  The

         estimated time for this step is 1.75 seconds. 

 13) We do an all-AMPs SUM step to aggregate from Spool 24 (Last Use)

     by way of an all-rows scan , grouping by field1 (

     OEAQ2_T.HDR1.ROWID).  Aggregate Intermediate Results are computed

     globally, then placed in Spool 25.  The size of Spool 25 is

     estimated with low confidence to be 5 rows (155 bytes).  The

     estimated time for this step is 0.15 seconds. 

 14) We execute the following steps in parallel. 

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

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

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

         size of Spool 6 is estimated with low confidence to be 5 rows

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

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

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

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

         OEAQ2_T.DW_CLM_HDR_MSG.CLM_HDR_KEY) to all AMPs.  Then we do a

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

         estimated with low confidence to be 1 row (29 bytes).  The

         estimated time for this step is 0.01 seconds. 

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

         of an all-rows scan with a condition of ("(X.LOAD_DT <= DATE

         '2011-01-31') AND (X.LOAD_DT >= DATE '2011-01-01')") into

         Spool 28 (all_amps) (compressed columns allowed), which is

         redistributed by the hash code of (CLM_HDR_KEY) to all AMPs. 

         Then we do a SORT to order Spool 28 by row hash.  The size of

         Spool 28 is estimated with low confidence to be 6,115 rows (

         128,415 bytes).  The estimated time for this step is 0.02

         seconds. 

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

         of an all-rows scan with a condition of ("(EXCL_CD.LOAD_DT <=

         DATE '2011-01-31') AND (EXCL_CD.LOAD_DT >= DATE '2011-01-01')")

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

         is redistributed by the rowkey of (OEAQ2_T.LN1.CLM_HDR_KEY,

         OEAQ2_T.LN1.LOAD_DT) to all AMPs.  Then we do a SORT to

         partition Spool 29 by rowkey.  The size of Spool 29 is

         estimated with low confidence to be 457,214 rows (10,973,136

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

 15) We execute the following steps in parallel. 

      1) We do an all-AMPs JOIN step from 31 partitions of OEAQ2_T.HDR1

         in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of a RowHash match

         scan with a condition of ("(OEAQ2_T.HDR1 in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >= DATE '2011-01-01') AND

         (OEAQ2_T.HDR1 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <=

         DATE '2011-01-31')"), which is joined to Spool 29 (Last Use)

         by way of a RowHash match scan locking OEAQ2_T.HDR1 for access. 

         OEAQ2_T.HDR1 and Spool 29 are left outer joined using a

         rowkey-based merge join, with a join condition of (

         "(OEAQ2_T.HDR1.LOAD_DT = LOAD_DT) AND

         (OEAQ2_T.HDR1.CLM_HDR_KEY = CLM_HDR_KEY)").  The result goes

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

         is built locally on the AMPs.  The size of Spool 30 is

         estimated with low confidence to be 2,603,319 rows (

         164,009,097 bytes).  The estimated time for this step is 0.71

         seconds. 

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

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

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

         of Spool 33 is estimated with low confidence to be 720 rows (

         33,120 bytes).  The estimated time for this step is 0.03

         seconds. 

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

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

     all-rows scan.  Spool 30 and Spool 33 are left outer joined using

     a product join, with a join condition of ("Field_6 = Field_3"). 

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

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

     to partition Spool 34 by rowkey.  The size of Spool 34 is

     estimated with low confidence to be 2,603,319 rows (205,662,201

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

 17) We execute the following steps in parallel. 

      1) We do an all-AMPs JOIN step from 31 partitions of OEAQ2_T.HDR2

         in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of a RowHash match

         scan with a condition of ("(OEAQ2_T.HDR2 in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT >= DATE '2011-01-01') AND

         (OEAQ2_T.HDR2 in view OEAQ2.V_CLM_HDR_SUBSET_5_YRS.LOAD_DT <=

         DATE '2011-01-31')"), which is joined to Spool 34 (Last Use)

         by way of a RowHash match scan.  OEAQ2_T.HDR2 and Spool 34 are

         joined using a rowkey-based merge join, with a join condition

         of ("(CLM_HDR_KEY = OEAQ2_T.HDR2.CLM_HDR_KEY) AND

         ((CLM_HDR_KEY = OEAQ2_T.HDR2.CLM_HDR_KEY) AND (LOAD_DT =

         OEAQ2_T.HDR2.LOAD_DT ))").  The result goes into Spool 35

         (all_amps) (compressed columns allowed), which is

         redistributed by the hash code of (OEAQ2_T.HDR1.CLM_HDR_KEY)

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

         The size of Spool 35 is estimated with low confidence to be

         2,549,065 rows (231,964,915 bytes).  The estimated time for

         this step is 1.73 seconds. 

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

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

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

         OEAQ2_T.JI1_CLMLINERMK.CLM_HDR_KEY) to all AMPs.  Then we do a

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

         estimated with high confidence to be 252,180,992 rows (

         6,304,524,800 bytes).  The estimated time for this step is

         32.18 seconds. 

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

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

     of a RowHash match scan.  Spool 35 and Spool 36 are left outer

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

     = CLM_HDR_KEY").  The result goes into Spool 37 (all_amps)

     (compressed columns allowed), which is built locally on the AMPs. 

     Then we do a SORT to order Spool 37 by the hash code of (

     OEAQ2_T.HDR1.CLM_HDR_KEY).  The size of Spool 37 is estimated with

     low confidence to be 2,549,065 rows (252,357,435 bytes).  The

     estimated time for this step is 3.32 seconds. 

 19) We execute the following steps in parallel. 

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

         a RowHash match scan, which is joined to

         OEAQ2_T.PF_CLM_CodeDesc_LKP in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS by way of a RowHash match scan

         with a condition of ("OEAQ2_T.PF_CLM_CodeDesc_LKP in view

         OEAQ2.V_CLM_HDR_SUBSET_5_YRS.CLM_LINE_NBR = 1").  Spool 37 and

         OEAQ2_T.PF_CLM_CodeDesc_LKP are joined using a sliding-window

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

         OEAQ2_T.PF_CLM_CodeDesc_LKP.CLM_HDR_KEY) AND

         (OEAQ2_T.PF_CLM_CodeDesc_LKP.CLM_HDR_KEY = CLM_HDR_KEY)"). 

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

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

         SORT to order Spool 40 by the hash code of (

         OEAQ2_T.HDR1.CLM_HDR_KEY).  The size of Spool 40 is estimated

         with low confidence to be 2,692,144 rows (269,214,400 bytes). 

         The estimated time for this step is 59.62 seconds. 

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

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

         by way of a RowHash match scan.  Spool 27 and Spool 28 are

         right outer joined using a merge join, with condition(s) used

         for non-matching on right table ("NOT (CLM_HDR_KEY IS NULL)"),

         with a join condition of ("CLM_HDR_KEY = CLM_HDR_KEY").  The

         result goes into Spool 41 (all_amps) (compressed columns

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

         SORT to order Spool 41 by the hash code of (CLM_HDR_KEY).  The

         size of Spool 41 is estimated with low confidence to be 6,116

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

         0.07 seconds. 

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

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

     of a RowHash match scan.  Spool 40 and Spool 41 are left outer

     joined using a merge join, with a join condition of ("(LOAD_DT =

     LOAD_DT) AND (CLM_HDR_KEY = CLM_HDR_KEY)").  The result goes into

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

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

     confidence to be 2,692,610 rows (288,109,270 bytes).  The

     estimated time for this step is 0.12 seconds. 

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

     an all-rows scan with a condition of ("(( CASE WHEN (SRC_CLM_CD =

     'M ') THEN (( CASE WHEN (SUBM_PROV_ID = 'PHARMCO') THEN ('P') ELSE

     (( CASE WHEN (((RMK_CD_2 = 'OTHR') OR ((RMK_CD_2 = 'OBCS') OR

     ((RMK_CD_2 = '0THR') OR ((RMK_CD_1 = '0THR') OR ((RMK_CD_1 =

     'OTHR') OR (RMK_CD_1 ='OBCS')))))) OR ((SRC_HDR_RMK_CD = 'OTHR')

     OR ((SRC_HDR_RMK_CD = '0THR') OR (SRC_HDR_RMK_CD = 'OBCS')))) THEN

     (( CASE WHEN (((SVC_CD = '9D3') OR ((SVC_CD = '9L3') OR ((SVC_CD =

     '9D2') OR ((SVC_CD = '7C4') OR ((SVC_CD = '7C3') OR (SVC_CD =

     '7C2')))))) OR ((SUBM_PROV_ID = '54055CA') OR ((SUBM_PROV_ID =

     '54137CA') OR ((SUBM_PROV_ID = '447K9AU') OR (SUBM_PROV_ID =

     '153D8AU'))))) THEN ('C') ELSE ('S') END )) ELSE (' ') END )) END

     )) ELSE (( CASE WHEN (SRC_CLM_CD = 'A ') THEN (( CASE WHEN

     ((MSG_CD3 = 'X674') OR ((MSG_CD3 = 'X673') OR ((MSG_CD2 = 'X673')

     OR ((MSG_CD2 = 'X674') OR ((MSG_CD1 = 'X674') OR (MSG_CD1 =

     'X673')))))) THEN (( CASE WHEN ((SUBM_PROV_ID = '54055CA') OR

     (SUBM_PROV_ID = '54054CA')) THEN('C') ELSE ('S') END )) ELSE (' ')

     END )) ELSE (NULL) END )) END ))= ' '") into Spool 22 (group_amps),

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

     estimated with low confidence to be 2,692,610 rows (215,408,800

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

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

     in processing the request.

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

     of statement 1. 



					
				
			
			
				
			
			
				
4 REPLIES
Enthusiast

Re: Estimate and Actual are different and causing PJ.

It appears that the stats on the table being aggregated in step 13 are out of date. There might be stats on the same columns but those stats do not represent the current contents of the table.  Show stats on that table and see what the stats say. Then recollect the stats on that table and rerun the query.

Junior Contributor

Re: Estimate and Actual are different and causing PJ.

Are you taljking about the same problem as SPOLISETTI?

Teradata 12 and Subqueries

Avoid Correlated Scalar Subqueries and rewrite then to Outer Joins:

Select 
m1.CLM_HDR_KEY,
m1.CLM_NBR as CLM_NBR,
m1.LOAD_DT,
Cast(Cast(m1.CLM_FIN_PRCS_DT as date format 'yyyy/mm') as char(7)) as CLM_RPT_YR_MNTH_DS,
Trim(m1.Grp_Nbr)||Trim(m1.Subgrp_nbr) as GRP_ID,
m1.TYPE_OF_CLM_CD,
m1.NETPAY_TOT_CLM_AMT as Tot_Netpay_Amt,
m2.Tot_Alw_Amt
from
OEAQ2.V_CLM_HDR_SUBSET_5_YRS m1
left join
( Select m2.clm_hdr_key, m2.load_dt,
Sum(Coalesce(m2.CHG_AMT,0) - Coalesce(m2.CNTRC_PROV_SAV_AMT,0)) as Tot_Alw_Amt
from OEAQ2.DW_CLM_LINE m2
group by m2.clm_hdr_key, m2.load_dt
) as m2 --> (This is what being aggregated in step 13)
on m2.CLM_HDR_KEY=m1.CLM_HDR_KEY
AND m1.LOAD_DT=m2.LOAD_DT

where m1.Load_dt between '2011-01-01' and '2011-01-31'
and m1.EXCL_CD = ' ';

Dieter

BPP
Enthusiast

Re: Estimate and Actual are different and causing PJ.

@Dieter:  Yes it is same as SPOLISETTI question. 

When we first got this problem we suggested the same to the users but they are so tough on not changing their query.  They are tied to the idea that why it is working on Prod but changed suddenly on QA.. I cannot find anwser to convince them and I cannot get this query where it was.     I see changing it to outer join is way to go, we will have another chat with our dvlpr's.. 

Meanwhile If SPOLISETTI or I find anyting will let you all know.. 

Bhanu P

BPP
Enthusiast

Re: Estimate and Actual are different and causing PJ.

So just you all know we were able to address this.  One of our DBA had identified bad stats (Parition + highly non unique column) were causing the plan to wayward!!

Thanks 

Bhanu P