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

08-21-2008
02:43 AM

08-21-2008
02:43 AM

Query Tuning

Hi all,

I a have an sql query which runs around 1 hr on prod.kindly give me suggestions in tuning the query.i am attaching the explain plan also

select DISTINCT P.Prod_Id,

P.Prod_Start_Dt Created_T,

P.Prod_Start_Dt Mod_t,

P.Prod_Desc,

P.Prod_END_DT,

P.Prod_Name,

P.Provisioning_Tag,

P.Prod_Start_Dt Start_T,

RP.Rate_Plan_Event_Type_Cd,

P.Prod_Class_Cd,

RB.Rate_Bal_Impact_Scaled_Amt Fee,

CPE.Event_Cnt,

CASE

WHEN CPE.EVENT_CNT >= 1 AND CPE.EVENT_CNT < 3 THEN 'Monthly'

WHEN CPE.EVENT_CNT >= 3 AND CPE.EVENT_CNT < 6 THEN 'Quarterly'

WHEN CPE.EVENT_CNT >= 6 AND CPE.EVENT_CNT < 9 THEN 'Semi-Annual'

WHEN CPE.EVENT_CNT >= 9 AND CPE.EVENT_CNT < 21 THEN 'Annual'

WHEN CPE.EVENT_CNT >= 21 AND CPE.EVENT_CNT < 33 THEN 'Two Year'

WHEN CPE.EVENT_CNT = 42 THEN 'Lifetime'

WHEN CPE.EVENT_CNT >= 33 AND CPE.EVENT_CNT <> 42 AND CPE.EVENT_CNT < 45 THEN 'Three Year'

WHEN CPE.EVENT_CNT >= 45 AND CPE.EVENT_CNT < 57 THEN 'Four Year'

WHEN CPE.EVENT_CNT >= 57 AND CPE.EVENT_CNT < 69 THEN 'Five Year' ELSE 'Six Year'

END BILLING_FREQUENCY

FROM dp_vedw_biz.PRODUCT P,

dp_vedw_biz.RATE_PLAN RP,

dp_vedw_biz.RATE R,

dp_vedw_biz.RATE_BAL_IMPACT RB,

dp_vedw_biz.Event_Type CPE

WHERE RP.Prod_Id = P.Prod_Id

AND RP.Rate_Plan_Id = R.Rate_Plan_Id

AND RP.Rate_Plan_Event_Type_Cd = CPE.EVENT_TYPE_name

AND RP.Rate_Plan_Event_Type_Cd LIKE '%cycle_forward%'

AND P.prod_class_cd = 602

AND R.Rate_Id = RB.Rate_Id

AND p.prod_id not in (select prod_id from dp_dedw_rep.RepTbl_RecurProducts)

AND RB.Rate_Bal_Element_Id = 840

AND COALESCE(RB.Bal_Impact_Cat_Cd,'X') <>'Discount'

AND COALESCE(RB.Bal_Impact_Cat_Cd,'X') <>'Linked'

AND RB.Rate_Bal_Rec_Id2 = 0

AND P.prod_name NOT LIKE '%ctivation%Fee%'

AND P.prod_name NOT LIKE '%ancellation%'

AND P.prod_name NOT LIKE '%Product%'

AND P.PROD_ID <> 1402520889

the explain plan is

Explanation

1) First, we lock dp_dedw_rep.RepTbl_RecurProducts for access, we

lock DP_TEDW.EVENT_TYPE for access, we lock DP_TEDW.RATE for

access, we lock DP_TEDW.RATE_BAL_IMPACT for access, we lock

DP_TEDW.RATE_PLAN for access, and we lock DP_TEDW.PRODUCT for

access.

2) Next, we do an all-AMPs SUM step to aggregate from

dp_dedw_rep.RepTbl_RecurProducts by way of an all-rows scan with

no residual conditions. Aggregate Intermediate Results are

computed globally, then placed in Spool 3.

3) We execute the following steps in parallel.

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

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

columns allowed), which is duplicated on all AMPs.

2) We do an all-AMPs JOIN step from DP_TEDW.RATE by way of a

RowHash match scan with a condition of ("NOT

(DP_TEDW.RATE.Rate_Plan_Id IS NULL)"), which is joined to

DP_TEDW.RATE_BAL_IMPACT by way of a RowHash match scan with a

condition of ("(DP_TEDW.RATE_BAL_IMPACT.Rate_Bal_Rec_Id2 = 0.

) AND (((( CASE WHEN (NOT

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd IS NULL )) THEN

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd) ELSE ('X') END

))<> 'Linked') AND (((( CASE WHEN (NOT

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd IS NULL )) THEN

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd) ELSE ('X') END

))<> 'Discount') AND

(DP_TEDW.RATE_BAL_IMPACT.Rate_Bal_Element_Id = 840 )))").

DP_TEDW.RATE and DP_TEDW.RATE_BAL_IMPACT are joined using a

merge join, with a join condition of ("DP_TEDW.RATE.Rate_Id =

DP_TEDW.RATE_BAL_IMPACT.Rate_Id"). The result goes into

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

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 5 by row hash. The size of Spool 5 is estimated

with no confidence to be 223 rows. The estimated time for

this step is 0.01 seconds.

4) We do an all-AMPs JOIN step from DP_TEDW.PRODUCT by way of an

all-rows scan with a condition of ("(DP_TEDW.PRODUCT.Prod_Id <>

1402520889.) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%Product%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ancellation%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ctivation%Fee%')) AND (DP_TEDW.PRODUCT.Prod_Class_Cd = 602 ))))"),

which is joined to dp_dedw_rep.RepTbl_RecurProducts by way of an

all-rows scan with no residual conditions. DP_TEDW.PRODUCT and

dp_dedw_rep.RepTbl_RecurProducts are joined using an exclusion

merge join, with a join condition of ("DP_TEDW.PRODUCT.Prod_Id =

dp_dedw_rep.RepTbl_RecurProducts.Prod_Id"), and null value

information in Spool 2. Skip this join step if null exists. The

result goes into Spool 6 (all_amps), which is redistributed by

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

hash. The size of Spool 6 is estimated with no confidence to be

46 rows. The estimated time for this step is 0.00 seconds.

5) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from DP_TEDW.PRODUCT by way of an

all-rows scan with a condition of ("(DP_TEDW.PRODUCT.Prod_Id

<> 1402520889.) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%Product%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ancellation%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ctivation%Fee%')) AND (DP_TEDW.PRODUCT.Prod_Class_Cd = 602

))))"), which is joined to dp_dedw_rep.RepTbl_RecurProducts

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

DP_TEDW.PRODUCT and dp_dedw_rep.RepTbl_RecurProducts are

joined using an exclusion merge join, with a join condition

of ("DP_TEDW.PRODUCT.Prod_Id =

dp_dedw_rep.RepTbl_RecurProducts.Prod_Id"), and null value

information in Spool 2 (Last Use). Skip this join step if

there is no null. The result goes into Spool 6 (all_amps),

which is redistributed by hash code to all AMPs. Then we do

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

estimated with no confidence to be 46 rows. The estimated

time for this step is 0.00 seconds.

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

a RowHash match scan, which is joined to DP_TEDW.RATE_PLAN by

way of a RowHash match scan with a condition of (

"(DP_TEDW.RATE_PLAN.Rate_Plan_Event_Type_Cd LIKE

'%cycle_forward%') AND ((NOT

(DP_TEDW.RATE_PLAN.Rate_Plan_Event_Type_Cd IS NULL )) AND

(NOT (DP_TEDW.RATE_PLAN.Prod_Id IS NULL )))"). Spool 5 and

DP_TEDW.RATE_PLAN are joined using a merge join, with a join

condition of ("DP_TEDW.RATE_PLAN.Rate_Plan_Id = Rate_Plan_Id").

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

allowed), which is redistributed by hash code to all AMPs.

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

Spool 7 is estimated with no confidence to be 223 rows. The

estimated time for this step is 0.01 seconds.

6) We execute the following steps in parallel.

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

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

by way of a RowHash match scan. Spool 6 and Spool 7 are

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

"Prod_Id = Prod_Id"). The result goes into Spool 8

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. Then we do a SORT to

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

with no confidence to be 17 rows. The estimated time for

this step is 0.00 seconds.

2) We do an all-AMPs RETRIEVE step from DP_TEDW.EVENT_TYPE by

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

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

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 9 by row hash. The size of Spool 9 is estimated

with high confidence to be 226 rows. The estimated time for

this step is 0.00 seconds.

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

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

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

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

Event_Type_Name"). The result goes into Spool 1 (group_amps),

which is redistributed by hash code to all AMPs. Then we do a

SORT to order Spool 1 by the sort key in spool field1 eliminating

duplicate rows. The size of Spool 1 is estimated with no

confidence to be 256 rows. The estimated time for this step is

0.01 seconds.

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

in processing the request.

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

statement 1.

Regards,

Newbie for teradata

I a have an sql query which runs around 1 hr on prod.kindly give me suggestions in tuning the query.i am attaching the explain plan also

select DISTINCT P.Prod_Id,

P.Prod_Start_Dt Created_T,

P.Prod_Start_Dt Mod_t,

P.Prod_Desc,

P.Prod_END_DT,

P.Prod_Name,

P.Provisioning_Tag,

P.Prod_Start_Dt Start_T,

RP.Rate_Plan_Event_Type_Cd,

P.Prod_Class_Cd,

RB.Rate_Bal_Impact_Scaled_Amt Fee,

CPE.Event_Cnt,

CASE

WHEN CPE.EVENT_CNT >= 1 AND CPE.EVENT_CNT < 3 THEN 'Monthly'

WHEN CPE.EVENT_CNT >= 3 AND CPE.EVENT_CNT < 6 THEN 'Quarterly'

WHEN CPE.EVENT_CNT >= 6 AND CPE.EVENT_CNT < 9 THEN 'Semi-Annual'

WHEN CPE.EVENT_CNT >= 9 AND CPE.EVENT_CNT < 21 THEN 'Annual'

WHEN CPE.EVENT_CNT >= 21 AND CPE.EVENT_CNT < 33 THEN 'Two Year'

WHEN CPE.EVENT_CNT = 42 THEN 'Lifetime'

WHEN CPE.EVENT_CNT >= 33 AND CPE.EVENT_CNT <> 42 AND CPE.EVENT_CNT < 45 THEN 'Three Year'

WHEN CPE.EVENT_CNT >= 45 AND CPE.EVENT_CNT < 57 THEN 'Four Year'

WHEN CPE.EVENT_CNT >= 57 AND CPE.EVENT_CNT < 69 THEN 'Five Year' ELSE 'Six Year'

END BILLING_FREQUENCY

FROM dp_vedw_biz.PRODUCT P,

dp_vedw_biz.RATE_PLAN RP,

dp_vedw_biz.RATE R,

dp_vedw_biz.RATE_BAL_IMPACT RB,

dp_vedw_biz.Event_Type CPE

WHERE RP.Prod_Id = P.Prod_Id

AND RP.Rate_Plan_Id = R.Rate_Plan_Id

AND RP.Rate_Plan_Event_Type_Cd = CPE.EVENT_TYPE_name

AND RP.Rate_Plan_Event_Type_Cd LIKE '%cycle_forward%'

AND P.prod_class_cd = 602

AND R.Rate_Id = RB.Rate_Id

AND p.prod_id not in (select prod_id from dp_dedw_rep.RepTbl_RecurProducts)

AND RB.Rate_Bal_Element_Id = 840

AND COALESCE(RB.Bal_Impact_Cat_Cd,'X') <>'Discount'

AND COALESCE(RB.Bal_Impact_Cat_Cd,'X') <>'Linked'

AND RB.Rate_Bal_Rec_Id2 = 0

AND P.prod_name NOT LIKE '%ctivation%Fee%'

AND P.prod_name NOT LIKE '%ancellation%'

AND P.prod_name NOT LIKE '%Product%'

AND P.PROD_ID <> 1402520889

the explain plan is

Explanation

1) First, we lock dp_dedw_rep.RepTbl_RecurProducts for access, we

lock DP_TEDW.EVENT_TYPE for access, we lock DP_TEDW.RATE for

access, we lock DP_TEDW.RATE_BAL_IMPACT for access, we lock

DP_TEDW.RATE_PLAN for access, and we lock DP_TEDW.PRODUCT for

access.

2) Next, we do an all-AMPs SUM step to aggregate from

dp_dedw_rep.RepTbl_RecurProducts by way of an all-rows scan with

no residual conditions. Aggregate Intermediate Results are

computed globally, then placed in Spool 3.

3) We execute the following steps in parallel.

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

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

columns allowed), which is duplicated on all AMPs.

2) We do an all-AMPs JOIN step from DP_TEDW.RATE by way of a

RowHash match scan with a condition of ("NOT

(DP_TEDW.RATE.Rate_Plan_Id IS NULL)"), which is joined to

DP_TEDW.RATE_BAL_IMPACT by way of a RowHash match scan with a

condition of ("(DP_TEDW.RATE_BAL_IMPACT.Rate_Bal_Rec_Id2 = 0.

) AND (((( CASE WHEN (NOT

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd IS NULL )) THEN

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd) ELSE ('X') END

))<> 'Linked') AND (((( CASE WHEN (NOT

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd IS NULL )) THEN

(DP_TEDW.RATE_BAL_IMPACT.Bal_Impact_Cat_Cd) ELSE ('X') END

))<> 'Discount') AND

(DP_TEDW.RATE_BAL_IMPACT.Rate_Bal_Element_Id = 840 )))").

DP_TEDW.RATE and DP_TEDW.RATE_BAL_IMPACT are joined using a

merge join, with a join condition of ("DP_TEDW.RATE.Rate_Id =

DP_TEDW.RATE_BAL_IMPACT.Rate_Id"). The result goes into

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

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 5 by row hash. The size of Spool 5 is estimated

with no confidence to be 223 rows. The estimated time for

this step is 0.01 seconds.

4) We do an all-AMPs JOIN step from DP_TEDW.PRODUCT by way of an

all-rows scan with a condition of ("(DP_TEDW.PRODUCT.Prod_Id <>

1402520889.) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%Product%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ancellation%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ctivation%Fee%')) AND (DP_TEDW.PRODUCT.Prod_Class_Cd = 602 ))))"),

which is joined to dp_dedw_rep.RepTbl_RecurProducts by way of an

all-rows scan with no residual conditions. DP_TEDW.PRODUCT and

dp_dedw_rep.RepTbl_RecurProducts are joined using an exclusion

merge join, with a join condition of ("DP_TEDW.PRODUCT.Prod_Id =

dp_dedw_rep.RepTbl_RecurProducts.Prod_Id"), and null value

information in Spool 2. Skip this join step if null exists. The

result goes into Spool 6 (all_amps), which is redistributed by

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

hash. The size of Spool 6 is estimated with no confidence to be

46 rows. The estimated time for this step is 0.00 seconds.

5) We execute the following steps in parallel.

1) We do an all-AMPs JOIN step from DP_TEDW.PRODUCT by way of an

all-rows scan with a condition of ("(DP_TEDW.PRODUCT.Prod_Id

<> 1402520889.) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%Product%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ancellation%')) AND ((NOT (DP_TEDW.PRODUCT.Prod_Name LIKE

'%ctivation%Fee%')) AND (DP_TEDW.PRODUCT.Prod_Class_Cd = 602

))))"), which is joined to dp_dedw_rep.RepTbl_RecurProducts

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

DP_TEDW.PRODUCT and dp_dedw_rep.RepTbl_RecurProducts are

joined using an exclusion merge join, with a join condition

of ("DP_TEDW.PRODUCT.Prod_Id =

dp_dedw_rep.RepTbl_RecurProducts.Prod_Id"), and null value

information in Spool 2 (Last Use). Skip this join step if

there is no null. The result goes into Spool 6 (all_amps),

which is redistributed by hash code to all AMPs. Then we do

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

estimated with no confidence to be 46 rows. The estimated

time for this step is 0.00 seconds.

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

a RowHash match scan, which is joined to DP_TEDW.RATE_PLAN by

way of a RowHash match scan with a condition of (

"(DP_TEDW.RATE_PLAN.Rate_Plan_Event_Type_Cd LIKE

'%cycle_forward%') AND ((NOT

(DP_TEDW.RATE_PLAN.Rate_Plan_Event_Type_Cd IS NULL )) AND

(NOT (DP_TEDW.RATE_PLAN.Prod_Id IS NULL )))"). Spool 5 and

DP_TEDW.RATE_PLAN are joined using a merge join, with a join

condition of ("DP_TEDW.RATE_PLAN.Rate_Plan_Id = Rate_Plan_Id").

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

allowed), which is redistributed by hash code to all AMPs.

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

Spool 7 is estimated with no confidence to be 223 rows. The

estimated time for this step is 0.01 seconds.

6) We execute the following steps in parallel.

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

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

by way of a RowHash match scan. Spool 6 and Spool 7 are

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

"Prod_Id = Prod_Id"). The result goes into Spool 8

(all_amps) (compressed columns allowed), which is

redistributed by hash code to all AMPs. Then we do a SORT to

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

with no confidence to be 17 rows. The estimated time for

this step is 0.00 seconds.

2) We do an all-AMPs RETRIEVE step from DP_TEDW.EVENT_TYPE by

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

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

redistributed by hash code to all AMPs. Then we do a SORT to

order Spool 9 by row hash. The size of Spool 9 is estimated

with high confidence to be 226 rows. The estimated time for

this step is 0.00 seconds.

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

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

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

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

Event_Type_Name"). The result goes into Spool 1 (group_amps),

which is redistributed by hash code to all AMPs. Then we do a

SORT to order Spool 1 by the sort key in spool field1 eliminating

duplicate rows. The size of Spool 1 is estimated with no

confidence to be 256 rows. The estimated time for this step is

0.01 seconds.

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

in processing the request.

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

statement 1.

Regards,

Newbie for teradata

3 REPLIES 3

Highlighted
##

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

10-01-2008
01:38 AM

10-01-2008
01:38 AM

Re: Query Tuning

Hi,

Please try the following and let me know if the performance has improved or not ..

try to use group by instead of distinct as distinct requires all the rows to be on single amp..

collect stats on all the joining columns and the columns used in the where clause..

try these things and lpet me know...

Thx,

Gaur

Please try the following and let me know if the performance has improved or not ..

try to use group by instead of distinct as distinct requires all the rows to be on single amp..

collect stats on all the joining columns and the columns used in the where clause..

try these things and lpet me know...

Thx,

Gaur

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

10-02-2008
11:10 AM

10-02-2008
11:10 AM

Re: Query Tuning

If there is any way you can get around using the LIKE statements you should.

I agree with the comment on distinct, group by is a better choice.

I agree with the comment on distinct, group by is a better choice.

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

10-02-2008
03:49 PM

10-02-2008
03:49 PM

Re: Query Tuning

1. Replace the NOT IN with a NOT EXISTS, because one of the columns is NULLable:

AND p.prod_id not in (select prod_id from dp_dedw_rep.RepTbl_RecurProducts)

->

AND NOT EXISTS (select * from dp_dedw_rep.RepTbl_RecurProducts where prod_id = p.prod_id)

2. Post some info about the actual size of the tables, e.g. HELP STATS output.

3. submit a "DIAGNOSTIC HELPSTATS ON FOR SESSION;", explain it again and check for missing stats.

4. Post the actual explain from your production system.

5. Before you change DISTINCT to GROUP BY better post the additional info.

Dieter

AND p.prod_id not in (select prod_id from dp_dedw_rep.RepTbl_RecurProducts)

->

AND NOT EXISTS (select * from dp_dedw_rep.RepTbl_RecurProducts where prod_id = p.prod_id)

2. Post some info about the actual size of the tables, e.g. HELP STATS output.

3. submit a "DIAGNOSTIC HELPSTATS ON FOR SESSION;", explain it again and check for missing stats.

4. Post the actual explain from your production system.

5. Before you change DISTINCT to GROUP BY better post the additional info.

Dieter