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

04-27-2013
06:29 PM

04-27-2013
06:29 PM

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

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

04-28-2013
03:07 PM

04-28-2013
03:07 PM

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.

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

04-29-2013
02:54 AM

04-29-2013
02:54 AM

Are you taljking about the same problem as SPOLISETTI?

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

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

04-29-2013
07:41 AM

04-29-2013
07:41 AM

@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

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

04-30-2013
06:54 PM

04-30-2013
06:54 PM

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