Query Tuning

Database
Enthusiast

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
3 REPLIES
Fan

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
Enthusiast

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.
Junior Contributor

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