Long running query issue

Database
nsi
Teradata Employee

Long running query issue

Hi,

The below query is taking around an hour to run. The left table:PRD_EDW.EVNT_DETL is very huge. It has around 53 billion records. The table with which it is being joined: PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP has only 84 records. From what I understand from the EXPLAIN plan is that the smaller table is being duplicated on all AMP's for the join. So I don't think join is creating the problem. I collected stats on the PARTITION and also on the columns involved in the query, still it doesn't seem of much use. Is the group by clause the culprit in my case? Can someone please suggest how can I bring down the query execution time?

Query:

REPLACE VIEW PRD_EDW.SUBS_LST_ACTVY_DAY_CNT_V AS 
SELECT edra.served_subs_id
, (SELECT current_integration_date FROM PRD_edw.current_integration_date_v) AS integration_dt
, (CAST((integration_dt/ 100)*100+1 AS DATE)) AS mth_start
, MAX(CASE WHEN edra.srvc_type_prod_id = 2197 THEN edra.actvy_start_dt end) AS psd_lst_actvy_dt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2197 AND edra.bundl_categ_cd = 'IN' THEN edra.actvy_start_dt end) AS psd_lst_actvy_inbundl_dt
, COALESCE(integration_dt - psd_lst_actvy_dt, -1) AS psd_lst_actvy_days_cnt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2198 THEN edra.actvy_start_dt end) AS mms_lst_actvy_dt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2198 AND edra.bundl_categ_cd = 'IN' THEN edra.actvy_start_dt end) AS mms_lst_actvy_inbundl_dt
, COALESCE(integration_dt - psd_lst_actvy_dt, -1) AS mms_lst_actvy_days_cnt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2100 THEN edra.actvy_start_dt end)AS sms_lst_actvy_dt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2100 AND edra.bundl_categ_cd = 'IN' THEN edra.actvy_start_dt end) AS sms_lst_actvy_inbundl_dt
, COALESCE(integration_dt - sms_lst_actvy_dt, -1) AS sms_lst_actvy_days_cnt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2199 THEN edra.actvy_start_dt end) AS voice_lst_actvy_dt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2199 AND edra.bundl_categ_cd = 'IN' THEN edra.actvy_start_dt end) AS voice_lst_actvy_inbundl_dt
, COALESCE(integration_dt - voice_lst_actvy_dt, -1) AS voice_lst_actvy_days_cnt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2195 THEN edra.actvy_start_dt end) AS vc_lst_actvy_dt
, MAX(CASE WHEN edra.srvc_type_prod_id = 2195 AND edra.bundl_categ_cd = 'IN' THEN edra.actvy_start_dt end) AS vc_lst_actvy_inbundl_dt
, COALESCE(integration_dt - vc_lst_actvy_dt, -1) AS vc_lst_actvy_days_cnt
, integration_dt - lst_actvy_dt AS lst_actvy_days_cnt
, MAX( edra.actvy_start_dt ) AS lst_actvy_dt
, COALESCE(SUM(CASE WHEN l2pwt.prev_wk_id = 1 AND edra.srvc_type_prod_id = 2199 AND TRIM(BOTH FROM edra.call_trnsctn_type_cd) = 'MOC' THEN edra.actvy_durtn_amt ELSE 0 end) / 7, 0) AS Tel_Mo_Durtn_Avg_L7day_Qty
, COALESCE(SUM(CASE WHEN l2pwt.prev_wk_id = 1 AND edra.srvc_type_prod_id = 2100 AND edra.call_trnsctn_type_cd = 'SMO' THEN edra.events_qty ELSE 0 end) / 7,0) AS Sms_Mo_Evnt_Avg_L7day_Cnt
, COALESCE((SUM(CASE WHEN l2pwt.prev_wk_id = 1 AND edra.srvc_type_prod_id = 2197 THEN edra.data_vol_tot_amt ELSE 0 end) / 7 ) /1048576,0) AS Psd_Vol_Mb_Avg_L7day_Qty
, COALESCE(SUM(CASE WHEN l2pwt.Prev_28day_Pred_Id = 1 AND edra.srvc_type_prod_id = 2199 AND edra.call_trnsctn_type_cd = 'MOC' THEN edra.actvy_durtn_amt ELSE 0 end) / 28,0) AS Tel_Mo_Durtn_Avg_L28day_Qty
, COALESCE(SUM(CASE WHEN l2pwt.Prev_28day_Pred_Id = 1 AND edra.srvc_type_prod_id = 2100 AND edra.call_trnsctn_type_cd = 'SMO' THEN edra.events_qty ELSE 0 end) / 28,0) AS Sms_Mo_Evnt_Avg_L28day_Cnt
, COALESCE((SUM(CASE WHEN l2pwt.Prev_28day_Pred_Id = 1 AND edra.srvc_type_prod_id = 2197 THEN edra.data_vol_tot_amt ELSE 0 end) / 28 ) /1048576,0) AS Psd_Vol_Mb_Avg_L28day_Qty
, NULL  AS Tel_Bundl_Durtn_Alloc_Tot_Qty
, NULL  AS Tel_Bundl_Durtn_Remng_Tot_Qty
, NULL AS Sms_Bundl_Evnt_Alloc_Tot_Cnt
, NULL AS Sms_Bundl_Evnt_Remng_Tot_Cnt
, NULL AS Psd_Bundl_Vol_Mb_Alloc_Tot_Qty
, NULL AS Psd_Bundl_Vol_Mb_Remng_Tot_Qty

FROM PRD_EDW.EVNT_DETL edra
LEFT JOIN (
SELECT *
FROM PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP
WHERE Prev_28day_Pred_Id = 1
) AS l2pwt ON l2pwt.cal_dt = edra.actvy_start_dt
WHERE edra.actvy_start_dt <= integration_dt 
GROUP BY edra.served_subs_id
, integration_dt
, mth_start;

 

EXPLAIN plan:

This request is eligible for incremental planning and execution (IPE).
The following is the static plan for the request.
1) First, we lock PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP for access,
and we lock PRD_EDW.edra for access.
2) Next, we do a single-AMP RETRIEVE step from
PRD_TEC_CTL.TC_HIGH_WATER_MARK by way of the unique primary index
"PRD_TEC_CTL.TC_HIGH_WATER_MARK.HIGH_WATER_MARK =
'NEXT_INTEGRATION_DAY'" with no residual conditions locking row
for access into Spool 1 (one-amp), which is built locally on that
AMP. The size of Spool 1 is estimated with high confidence to be
1 row (25 bytes). The estimated time for this step is 0.00
seconds.
3) We do a single-AMP DISPATCHER RETRIEVE step from Spool 1 (Last
Use) by way of an all-rows scan and send the rows back to the
Dispatcher. The size is estimated with high confidence to be 1
row. The estimated time for this step is 0.01 seconds.
4) We do an all-AMPs RETRIEVE step from
PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP by way of an all-rows
scan with a condition of (
"PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP.PREV_28DAY_PRED_ID = 1")
into Spool 5 (all_amps) (compressed columns allowed), which is
duplicated on all AMPs. The size of Spool 5 is estimated with low
confidence to be 20,160 rows (504,000 bytes). The estimated time
for this step is 0.02 seconds.
5) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to PRD_EDW.edra by way of an
all-rows scan with a condition of ("PRD_EDW.edra.actvy_start_dt <=
:%SSQ20"). Spool 5 and PRD_EDW.edra are right outer joined using
a dynamic hash join, with condition(s) used for non-matching on
right table ("NOT (PRD_EDW.edra.actvy_start_dt IS NULL)"), with a
join condition of ("CAL_DT = PRD_EDW.edra.actvy_start_dt"). The
input table PRD_EDW.edra will not be cached in memory. The result
goes into Spool 4 (all_amps) (compressed columns allowed), which
is built locally on the AMPs. The result spool file will not be
cached in memory. The size of Spool 4 is estimated with no
confidence to be 53,775,997,691 rows (5,323,823,771,409 bytes).
The estimated time for this step is 14 minutes and 6 seconds.
6) We do an all-AMPs SUM step to aggregate from Spool 4 (Last Use) by
way of an all-rows scan , grouping by field1 (
PRD_EDW.edra.served_subs_id ,:%SSQ20 ,((:%SSQ20 / 100 )* 100 )+ 1
(DATE)). Aggregate Intermediate Results are computed locally,
then placed in Spool 7. The size of Spool 7 is estimated with no
confidence to be 117,777,603 rows (16,135,531,611 bytes). The
estimated time for this step is 1 hour and 1 minute.
7) We do an all-AMPs RETRIEVE step from Spool 7 (Last Use) by way of
an all-rows scan into Spool 2 (group_amps), which is built locally
on the AMPs. The size of Spool 2 is estimated with no confidence
to be 117,777,603 rows (35,215,503,297 bytes). The estimated time
for this step is 3.74 seconds.
8) 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 hour and 15 minutes.

 

show table PRD_EDW.EVNT_DETL;

CREATE MULTISET TABLE PRD_EDW.EVNT_DETL ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
actvy_start_dt DATE FORMAT 'YYYY-MM-DD',
served_subs_id INTEGER,
srvc_type_prod_id INTEGER COMPRESS (0 ,212894 ,212895 ,212896 ,212897 ,212898 ,212899 ,212900 ,212901 ,212902 ),
bundl_categ_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('EXCD ','IN ','NA ','OUT ','UNKN '),
call_trnsctn_type_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('CFD ','MO ','MOC ','MTC ','NA ','PBO ','SMO '),
call_rate_tm_zone_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('24H ','CM ','HPYHR ','NA ','OPK ','OPKNO ','OPKPL ','PK ','PKNO ','PKPL ','STD '),
actvy_path_type_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('FROMNET ','NA ','OFFNET ','ONNET ','TONET ','UNK ','UNKNWN '),
intl_ind CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('I ','L ','U '),
roaming_ind CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('NR','R '),
revnu_strm_type_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ('CS ','POSTP ','PREP ','TOPUP '),
revnu_expns_type_cd CHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS 'REV ',
events_qty DECIMAL(18,4) COMPRESS (0.0000 ,112.0000 ,224.0000 ,80.0000 ,192.0000 ,48.0000 ,160.0000 ,16.0000 ,128.0000 ,240.0000 ,96.0000 ,208.0000 ,64.0000 ,176.0000 ,32.0000 ,144.0000 ,177.0000 ,33.0000 ,145.0000 ,1.0000 ,113.0000 ,225.0000 ,81.0000 ,193.0000 ,49.0000 ,161.0000 ,17.0000 ,129.0000 ,241.0000 ,97.0000 ,209.0000 ,65.0000 ,210.0000 ,66.0000 ,178.0000 ,34.0000 ,146.0000 ,2.0000 ,114.0000 ,226.0000 ,82.0000 ,194.0000 ,50.0000 ,162.0000 ,18.0000 ,130.0000 ,242.0000 ,98.0000 ,243.0000 ,99.0000 ,211.0000 ,67.0000 ,179.0000 ,35.0000 ,147.0000 ,3.0000 ,115.0000 ,227.0000 ,83.0000 ,195.0000 ,51.0000 ,163.0000 ,19.0000 ,131.0000 ,164.0000 ,20.0000 ,132.0000 ,244.0000 ,100.0000 ,212.0000 ,68.0000 ,180.0000 ,36.0000 ,148.0000 ,4.0000 ,116.0000 ,228.0000 ,84.0000 ,196.0000 ,52.0000 ,197.0000 ,53.0000 ,165.0000 ,21.0000 ,133.0000 ,245.0000 ,101.0000 ,213.0000 ,69.0000 ,181.0000 ,37.0000 ,149.0000 ,5.0000 ,117.0000 ,229.0000 ,85.0000 ,118.0000 ,230.0000 ,86.0000 ,198.0000 ,54.0000 ,166.0000 ,22.0000 ,134.0000 ,246.0000 ,102.0000 ,214.0000 ,70.0000 ,182.0000 ,38.0000 ,150.0000 ,6.0000 ,151.0000 ,7.0000 ,119.0000 ,231.0000 ,87.0000 ,199.0000 ,55.0000 ,167.0000 ,23.0000 ,135.0000 ,247.0000 ,103.0000 ,215.0000 ,71.0000 ,183.0000 ,39.0000 ,184.0000 ,40.0000 ,152.0000 ,8.0000 ,120.0000 ,232.0000 ,88.0000 ,200.0000 ,56.0000 ,168.0000 ,24.0000 ,136.0000 ,248.0000 ,104.0000 ,216.0000 ,72.0000 ,105.0000 ,217.0000 ,73.0000 ,185.0000 ,41.0000 ,153.0000 ,9.0000 ,121.0000 ,233.0000 ,89.0000 ,201.0000 ,57.0000 ,169.0000 ,25.0000 ,137.0000 ,249.0000 ,138.0000 ,250.0000 ,106.0000 ,218.0000 ,74.0000 ,186.0000 ,42.0000 ,154.0000 ,10.0000 ,122.0000 ,234.0000 ,90.0000 ,202.0000 ,58.0000 ,170.0000 ,26.0000 ,59.0000 ,171.0000 ,27.0000 ,139.0000 ,251.0000 ,107.0000 ,219.0000 ,75.0000 ,187.0000 ,43.0000 ,155.0000 ,11.0000 ,123.0000 ,235.0000 ,91.0000 ,203.0000 ,236.0000 ,92.0000 ,204.0000 ,60.0000 ,172.0000 ,28.0000 ,140.0000 ,252.0000 ,108.0000 ,220.0000 ,76.0000 ,188.0000 ,44.0000 ,156.0000 ,12.0000 ,124.0000 ,125.0000 ,237.0000 ,93.0000 ,205.0000 ,61.0000 ,173.0000 ,29.0000 ,141.0000 ,253.0000 ,109.0000 ,221.0000 ,77.0000 ,189.0000 ,45.0000 ,157.0000 ,13.0000 ,46.0000 ,158.0000 ,14.0000 ,126.0000 ,238.0000 ,94.0000 ,206.0000 ,62.0000 ,174.0000 ,30.0000 ,142.0000 ,254.0000 ,110.0000 ,222.0000 ,78.0000 ,190.0000 ,223.0000 ,79.0000 ,191.0000 ,47.0000 ,159.0000 ,15.0000 ,127.0000 ,239.0000 ,95.0000 ,207.0000 ,63.0000 ,175.0000 ,31.0000 ,143.0000 ,111.0000 ),
chrg_excl_vat_amt DECIMAL(18,4) COMPRESS (0.0000 ,1.2544 ,0.9474 ,0.6404 ,0.3333 ,1.5877 ,0.0263 ,1.2807 ,0.0264 ,2.1000 ,0.9737 ,0.6667 ,0.3596 ,1.6140 ,0.0526 ,1.3070 ,1.0000 ,3.5088 ,0.6930 ,3.5090 ,2.8947 ,0.3860 ,2.8948 ,0.0789 ,1.3333 ,1.0263 ,2.2807 ,0.7193 ,1.9737 ,0.4123 ,0.3100 ,1.3596 ,0.1053 ,1.0526 ,2.3070 ,0.7456 ,0.4386 ,1.6930 ,0.1316 ,1.3860 ,0.7719 ,1.5400 ,0.4649 ,1.7193 ,0.1579 ,1.4123 ,0.0300 ,1.1053 ,0.7982 ,4.5614 ,0.4912 ,1.7456 ,0.1842 ,1.4386 ,3.9474 ,1.1316 ,0.8246 ,0.5175 ,0.6200 ,0.2105 ,1.4649 ,0.8509 ,2.1054 ,3.0526 ,0.5439 ,0.2368 ,1.4912 ,1.1842 ,0.8771 ,0.8772 ,2.1316 ,6.8421 ,0.5702 ,0.2632 ,1.2105 ,0.9035 ,0.5965 ,1.8509 ,0.2895 ,1.5439 ,1.2368 ,0.9298 ,0.6228 ,0.9300 ,4.3860 ,0.3158 ,0.0088 ,0.0600 ,0.7000 ,1.2632 ,0.9561 ,0.6491 ,3.1579 ,0.3421 ,1.5965 ,0.0351 ,1.2895 ,0.0352 ,2.8000 ,0.9825 ,0.6754 ,1.9298 ,0.0100 ,0.3684 ,1.6228 ,0.0614 ,1.3158 ,1.0088 ,0.7017 ,0.7018 ,0.3947 ,1.6491 ,0.1900 ,0.0877 ,1.3421 ,0.0878 ,1.0351 ,1.2400 ,0.7281 ,1.6754 ,0.4211 ,0.1140 ,1.3684 ,1.0614 ,0.7544 ,4.2105 ,0.4474 ,1.7018 ,1.3947 ,0.1404 ,4.2108 ,4.3900 ,1.0877 ,0.7807 ,0.4737 ,0.1667 ,2.3684 ,0.8070 ,2.0614 ,0.5000 ,1.7544 ,0.1930 ,1.4474 ,1.1404 ,0.8333 ,0.5263 ,1.7807 ,0.0400 ,0.2193 ,1.4737 ,1.1667 ,0.8596 ,2.3700 ,0.5526 ,0.2456 ,5.2632 ,1.1930 ,0.8860 ,0.5789 ,0.2719 ,1.5263 ,1.2193 ,0.9123 ,0.6053 ,0.2982 ,1.2456 ,2.8072 ,0.9386 ,2.1930 ,0.6316 ,1.5789 ,0.3246 ,1.5790 ,0.0175 ,1.2719 ,0.0176 ,1.4000 ,0.9649 ,0.6579 ,0.3508 ,0.3509 ,1.2982 ,0.0439 ,0.9912 ,3.5000 ,0.6842 ,0.0700 ,0.3772 ,0.0702 ,0.7105 ,0.4035 ,1.6579 ,1.5300 ,0.0965 ,1.3509 ,0.0200 ,0.7368 ,0.4298 ,1.6842 ,0.1228 ,1.3772 ,2.6316 ,1.0702 ,0.7632 ,0.4561 ,0.1491 ,1.4035 ,1.4036 ,1.0965 ,0.7895 ,0.3800 ,1.7368 ,6.1400 ,0.4825 ,0.1754 ,0.1755 ,1.1228 ,6.1404 ,0.8158 ,0.5088 ,0.2018 ,1.1491 ,0.8421 ,2.0965 ,4.9126 ,0.5351 ,1.7895 ,0.2281 ,1.1754 ,0.8684 ,0.5614 ,1.8158 ,0.2544 ,1.5088 ,2.4560 ,1.2018 ,2.1490 ,0.8947 ,0.0500 ,1.8420 ,0.5877 ,1.8421 ,1.5350 ,1.8422 ,0.2807 ,1.2280 ,1.2281 ,0.9210 ,2.1754 ,0.9211 ,0.6140 ,1.8684 ,4.3772 ,0.3070 ,1.5614 ),
data_vol_tot_amt DECIMAL(18,4) COMPRESS (0.0000 ,3145728.0000 ,139264.0000 ,102400.0000 ,241664.0000 ,65536.0000 ,204800.0000 ,28672.0000 ,167936.0000 ,131072.0000 ,94208.0000 ,233472.0000 ,57344.0000 ,196608.0000 ,20480.0000 ,159744.0000 ,122880.0000 ,262144.0000 ,86016.0000 ,225280.0000 ,49152.0000 ,188416.0000 ,12288.0000 ,151552.0000 ,114688.0000 ,77824.0000 ,217088.0000 ,40960.0000 ,180224.0000 ,4096.0000 ,143360.0000 ,106496.0000 ,245760.0000 ,69632.0000 ,208896.0000 ,32768.0000 ,172032.0000 ,135168.0000 ,98304.0000 ,237568.0000 ,61440.0000 ,200704.0000 ,24576.0000 ,163840.0000 ,126976.0000 ,90112.0000 ,229376.0000 ,53248.0000 ,192512.0000 ,16384.0000 ,155648.0000 ,118784.0000 ,81920.0000 ,221184.0000 ,45056.0000 ,184320.0000 ,8192.0000 ,147456.0000 ,110592.0000 ,73728.0000 ,212992.0000 ,36864.0000 ,176128.0000 ,218112.0000 ,41984.0000 ,181248.0000 ,5120.0000 ,144384.0000 ,107520.0000 ,70656.0000 ,209920.0000 ,33792.0000 ,173056.0000 ,136192.0000 ,99328.0000 ,238592.0000 ,62464.0000 ,201728.0000 ,25600.0000 ,164864.0000 ,128000.0000 ,267264.0000 ,91136.0000 ,230400.0000 ,54272.0000 ,193536.0000 ,17408.0000 ,156672.0000 ,119808.0000 ,82944.0000 ,222208.0000 ,46080.0000 ,185344.0000 ,9216.0000 ,148480.0000 ,111616.0000 ,74752.0000 ,214016.0000 ,37888.0000 ,177152.0000 ,1024.0000 ,140288.0000 ,103424.0000 ,242688.0000 ,66560.0000 ,205824.0000 ,29696.0000 ,168960.0000 ,132096.0000 ,95232.0000 ,234496.0000 ,58368.0000 ,197632.0000 ,21504.0000 ,160768.0000 ,123904.0000 ,87040.0000 ,226304.0000 ,50176.0000 ,189440.0000 ,13312.0000 ,152576.0000 ,115712.0000 ,78848.0000 ,120832.0000 ,83968.0000 ,223232.0000 ,47104.0000 ,186368.0000 ,10240.0000 ,149504.0000 ,112640.0000 ,251904.0000 ,75776.0000 ,215040.0000 ,38912.0000 ,178176.0000 ,2048.0000 ,141312.0000 ,104448.0000 ,67584.0000 ,206848.0000 ,30720.0000 ,169984.0000 ,133120.0000 ,272384.0000 ,96256.0000 ,235520.0000 ,59392.0000 ,198656.0000 ,22528.0000 ,161792.0000 ,124928.0000 ,88064.0000 ,227328.0000 ,51200.0000 ,190464.0000 ,14336.0000 ,153600.0000 ,116736.0000 ,79872.0000 ,219136.0000 ,43008.0000 ,182272.0000 ,6144.0000 ,145408.0000 ,108544.0000 ,71680.0000 ,210944.0000 ,34816.0000 ,174080.0000 ,137216.0000 ,100352.0000 ,239616.0000 ,63488.0000 ,202752.0000 ,26624.0000 ,165888.0000 ,129024.0000 ,92160.0000 ,231424.0000 ,55296.0000 ,194560.0000 ,18432.0000 ,157696.0000 ,1728.0000 ,60416.0000 ,199680.0000 ,23552.0000 ,162816.0000 ,125952.0000 ,89088.0000 ,228352.0000 ,52224.0000 ,191488.0000 ,15360.0000 ,154624.0000 ,117760.0000 ,257024.0000 ,80896.0000 ,220160.0000 ,44032.0000 ,183296.0000 ,7168.0000 ,146432.0000 ,109568.0000 ,72704.0000 ,211968.0000 ,35840.0000 ,175104.0000 ,138240.0000 ,101376.0000 ,240640.0000 ,64512.0000 ,203776.0000 ,27648.0000 ,166912.0000 ,130048.0000 ,93184.0000 ,232448.0000 ,56320.0000 ,195584.0000 ,19456.0000 ,158720.0000 ,121856.0000 ,84992.0000 ,224256.0000 ,48128.0000 ,187392.0000 ,11264.0000 ,150528.0000 ,113664.0000 ,76800.0000 ,216064.0000 ,39936.0000 ,179200.0000 ,3072.0000 ,142336.0000 ,105472.0000 ,68608.0000 ,207872.0000 ,31744.0000 ,171008.0000 ,134144.0000 ,97280.0000 ,236544.0000 ,864.0000 ,576.0000 ,288.0000 ,292.0000 ,1428.0000 ,1080.0000 ,360.0000 ,457.0000 ),
actvy_durtn_amt DECIMAL(18,4) COMPRESS (0.0000 ,112.0000 ,224.0000 ,80.0000 ,192.0000 ,3600.0000 ,48.0000 ,160.0000 ,16.0000 ,128.0000 ,240.0000 ,96.0000 ,208.0000 ,64.0000 ,176.0000 ,32.0000 ,144.0000 ,177.0000 ,33.0000 ,145.0000 ,1.0000 ,113.0000 ,225.0000 ,81.0000 ,193.0000 ,49.0000 ,161.0000 ,17.0000 ,129.0000 ,241.0000 ,97.0000 ,209.0000 ,65.0000 ,210.0000 ,66.0000 ,178.0000 ,34.0000 ,146.0000 ,2.0000 ,114.0000 ,226.0000 ,82.0000 ,194.0000 ,50.0000 ,162.0000 ,18.0000 ,130.0000 ,242.0000 ,98.0000 ,243.0000 ,99.0000 ,211.0000 ,67.0000 ,179.0000 ,35.0000 ,147.0000 ,3.0000 ,115.0000 ,227.0000 ,83.0000 ,195.0000 ,51.0000 ,163.0000 ,19.0000 ,131.0000 ,164.0000 ,20.0000 ,132.0000 ,244.0000 ,100.0000 ,212.0000 ,68.0000 ,1700.0000 ,180.0000 ,36.0000 ,148.0000 ,4.0000 ,116.0000 ,228.0000 ,84.0000 ,196.0000 ,52.0000 ,197.0000 ,53.0000 ,165.0000 ,21.0000 ,133.0000 ,245.0000 ,101.0000 ,213.0000 ,69.0000 ,181.0000 ,37.0000 ,149.0000 ,5.0000 ,117.0000 ,229.0000 ,85.0000 ,118.0000 ,230.0000 ,86.0000 ,198.0000 ,54.0000 ,166.0000 ,22.0000 ,134.0000 ,246.0000 ,102.0000 ,214.0000 ,70.0000 ,182.0000 ,38.0000 ,150.0000 ,6.0000 ,151.0000 ,7.0000 ,119.0000 ,231.0000 ,87.0000 ,199.0000 ,55.0000 ,167.0000 ,23.0000 ,135.0000 ,247.0000 ,103.0000 ,215.0000 ,71.0000 ,183.0000 ,39.0000 ,184.0000 ,40.0000 ,152.0000 ,8.0000 ,120.0000 ,232.0000 ,88.0000 ,200.0000 ,56.0000 ,312.0000 ,168.0000 ,24.0000 ,136.0000 ,3400.0000 ,248.0000 ,104.0000 ,216.0000 ,72.0000 ,105.0000 ,217.0000 ,73.0000 ,185.0000 ,41.0000 ,153.0000 ,9.0000 ,121.0000 ,233.0000 ,89.0000 ,201.0000 ,57.0000 ,169.0000 ,25.0000 ,137.0000 ,249.0000 ,138.0000 ,250.0000 ,106.0000 ,218.0000 ,74.0000 ,186.0000 ,42.0000 ,154.0000 ,10.0000 ,122.0000 ,234.0000 ,90.0000 ,202.0000 ,58.0000 ,170.0000 ,26.0000 ,59.0000 ,171.0000 ,27.0000 ,139.0000 ,107.0000 ,219.0000 ,75.0000 ,187.0000 ,43.0000 ,155.0000 ,11.0000 ,123.0000 ,235.0000 ,91.0000 ,203.0000 ,236.0000 ,92.0000 ,204.0000 ,60.0000 ,172.0000 ,28.0000 ,140.0000 ,108.0000 ,220.0000 ,76.0000 ,188.0000 ,44.0000 ,156.0000 ,12.0000 ,124.0000 ,125.0000 ,237.0000 ,93.0000 ,205.0000 ,61.0000 ,173.0000 ,29.0000 ,141.0000 ,109.0000 ,221.0000 ,77.0000 ,189.0000 ,45.0000 ,157.0000 ,13.0000 ,46.0000 ,158.0000 ,14.0000 ,126.0000 ,238.0000 ,94.0000 ,206.0000 ,62.0000 ,174.0000 ,30.0000 ,142.0000 ,110.0000 ,222.0000 ,78.0000 ,190.0000 ,223.0000 ,79.0000 ,191.0000 ,47.0000 ,159.0000 ,15.0000 ,127.0000 ,239.0000 ,95.0000 ,207.0000 ,63.0000 ,175.0000 ,31.0000 ,143.0000 ,111.0000 ),
prcss_crtn_dttm TIMESTAMP(0),
actvy_type_id INTEGER TITLE 'Activity Type Id' COMPRESS (1 ,2 ,3 ,4 ))
PRIMARY INDEX ( served_subs_id )
PARTITION BY RANGE_N(actvy_start_dt BETWEEN DATE '2011-01-01' AND '2020-12-31' EACH INTERVAL '1' DAY ,
NO RANGE OR UNKNOWN);

 

show table PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP;

CREATE MULTISET TABLE PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CAL_DT DATE FORMAT 'YYYY-MM-DD',
RSA_WORKING_DAY_IND CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
PREV_WK_ID INTEGER,
PREV_28DAY_PRED_ID INTEGER)
PRIMARY INDEX ( CAL_DT );

 

Stats on PRD_EDW.EVNT_DETLStats on PRD_EDW.EVNT_DETL

Stats on PRD_EDW_TMP.LST_28DAY_WEEKLY_TMPStats on PRD_EDW_TMP.LST_28DAY_WEEKLY_TMP

 

 

10 REPLIES
Senior Apprentice

Re: Long running query issue

Hi,

 

I suspect that your basic performance problem is the full table scan of your big table.

 

The PPI on that table is not being used by the optimiser. What you might want to try is:

- move the calculation of 'integration_dt' from a scalar sub-query to a derived table

- inner join this new derived table to your big table using the "edra.actvy_start_dt <= integration_dt" condition

 

See if that gives you partition elimination (I'd expect to see the phrase 'enhanced by dynamic partition elimination'). If you get partition elimination then hopefully the query will run quicker.

 

Out of interest, what percentage of that big table would you expect to meet that above condition? If it is  at or close to 100% then you probably won't speed up the query by much.

 

If that works I'd be tempted to raise an incident with TD. I like using scalar sub-queries and I'd have expected this one to use the PPI.

 

HTH
Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
nsi
Teradata Employee

Re: Long running query issue

Hi Dave,

 

Thanks for your reply. I tried to move the calculation of 'integration_dt' to a derived table and saw the explain plan. Unfortunately the explain plan is now using a 'product join' for the join condition: "edra.actvy_start_dt <= integration_dt" and the query execution time has also shot up drastically.

 

Out of 53 billion records, around 117 million records meet the 'where' condition. So, the final output has around 117 million records.

 

Is there a way I can make the big table use the PPI instead of FTS?

 

 

Thanks

Senior Apprentice

Re: Long running query issue

Hi,

I'm not surprised its a product join because one side of the join is a single row. What we're looking for to speed up access to the big table is 'partition elimination'.

One thing I should have asked earlier (sorry) is: if you have run this query, do you have query log information for it? Specifically do you have step level info (dbc.dbqlstetbl)? That will confirm which step is actually taking a long time.

 

I've mocked this up on my 15.10 system and haven't so far found a way to get partition elimination.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Long running query issue

I wonder why the optimizer is not doing a Dynamic Plan instead:

This request is eligible for incremental planning and execution (IPE).
The following is the static plan for the request.

 

What's this PRD_edw.current_integration_date_v view?

I assume this current_integration_date is close to today and thus you select almost all rows in your table.

Btw, do you really need that Outer Join to aggregate the data several years back?

 

When you wrote 

Out of 53 billion records, around 117 million records meet the 'where' condition. So, the final output has around 117 million records.

this final output is after aggregation, which matches the statistics on served_subs_id?

Junior Supporter

Re: Long running query issue

This is a very common situation that i have also faced. Why is the optimizer not smart enought to get that "SELECT current_integration_date FROM PRD_edw.current_integration_date_v"  would give only one date as output and subbstitute it so that it can use static partition elimination ? - Question to Optimizer experts here.

 

Are you using this query in any tool like informatica ? If yes, you can run this query - SELECT current_integration_date FROM PRD_edw.current_integration_date_v in a pre session and pass that as a parameter to this query and the it would use partition elimination.

Thx ! Samir

nsi
Teradata Employee

Re: Long running query issue

Hi Dieter,

 

Thanks for your reply. The 'PRD_edw.current_integration_date_v' is a view. We select today's date from this view. I think, I can exclude that 'where' condition because it is any which ways selecting all the records from the left table.

 

Also, the final output after aggregation matches the statistics on served_subs_id.

 

nsi
Teradata Employee

Re: Long running query issue

Hi Samir,

 

Unfortuntely I am not using Informatica here. I am using ODI. There is no provision similar to pre session commands of Informatica in ODI as per my knowledge.

 

Thanks

Senior Apprentice

Re: Long running query issue

Hi,

 

A word of caution. If you remove that 'where' clause then you are changing the business question being asked. Is this really what you want to do?

 

If it is then you are going to get a full table scan of your big table - because that is what you're asking for (it is the outer table and no selection against it)

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Long running query issue

'NEXT_INTEGRATION_DAY' is probably set to current_date when loading finished. 

Otherwise you can simply use CURRENT_DATE instead.

 

Regarding LST_28DAY_WEEKLY_TMP, is it recreated daily to match the last n weeks?

When the calculaton is based on CURRENT_DATE it could be done directly withput join.

 

As Dave already wrote, the most important info is the DBQL steps, which step is consuming most resources, join or aggregation?

 

Do you really run a SELECT * against this view or are there WHERE-conditions?