ETL性能问题分析与调整方法

BOCN数据仓库

－ETL性能分析与调整方法1.前言

随着数据仓库基础数据和应用的不断增加，数据仓库的性能问题日益突出。目前超过1小时的作业有2个30分钟到1小时的作业有10个10分钟到30分钟的作业有48个因此对数据仓库进行性能优化的需求越来越迫切2.作业时长分析共有12个作业运行时间在30分钟以上系统作业运行时间SDDT05_EVENT_FUND_TXN_EXP1:47:03ALSALM_DEPOSIT_FIX_DATA1:05:00BRSBRS_EXP_DATA0:51:52MAST_AGMT_INFO_A0:47:01SDDSRDFTPONE0:46:08MAST_AGMT_INFO_B0:46:02PCRPCR_EXPORT0:43:20SDDSRDFTPTHREE0:43:07MASOTHER0:39:33SDDSRDFTPTWO0:38:43OCM99_OCRM_JOB_END0:37:20ALSALM_GROUP_INT_H0:33:193.共有48个作业运行时间为10分钟到30分钟系统作业运行时间系统作业运行时间BAKBAK_ESSAPS_SRC0:29:14DQCT03_AGMT_AMT_H0:15:18T03T03_AGREEMENT_RT0:28:20CCRGRDJKFEXPDATA0:15:03PMMJXKH_C_LDM0:28:19DQCDQC_CHECK_KPI0:14:50BAKBAK_ETLENV_710:27:34PMMJXKH_P_LDM_NET0:14:08T03T03_CARD_ACCT_BAL_COMPO_H0:24:39ALSALM_LOAN_DATA0:13:57T03T03_AGREEMENT_S020:24:19MAST_MA_BALANCE0:13:53T03T03_AGMT_ACCU_H0:23:59CBST_CBS_BAL_DL0:13:18MAST_AGMT_INFO_C0:22:49BAKBAK_ETLENV_400:12:58T03T03_AGMT_BAL_H0:22:17MASFTPFILE0:12:32PMMJXKH_P_LDM_SAV0:21:33ACMCUST_FIX_IND_POST0:12:31CFST_CFS_AC_TREE0:21:31T05T05_CRDCARD_TXN_DTL0:12:05SDDBCNTDEPO0:20:57ECLNB_CUST_TXN_SUMM0:12:03DQCT03_CARD_ACCT_BAL_COMPO_H0:20:32ECLNB_NEW_CUST_SUMM0:11:58MDLM03_FIN_ACCT0:19:37BDET03_ACCT_CRDCARD0:11:53BDET03_AGREEMENT0:19:26IFST_IFS_BAL_BASIC0:11:20IRPT_DEP_POT_INT_RATE0:17:24MDLM01_CUST_SIGN_INFO0:11:16PCRCC_CR_ID_I0:17:08IRPT_LOAN_POT_INT_RATE0:11:07SDDREPAYDET0:17:06RIRDEPOS_BAL_DATA0:11:07T03T03_AGMT_BAL_H_S010:17:01T05T05_EVT_MESSAGE_SEND0:10:47ACMCUST_BASIC_INFO_PRE0:16:51CRST_REP_GJYW_ZB0:10:46CABCAB_EXP_DATA0:16:40MDLM03_CARD_TXN_STAT0:10:44PMMJXKH_P_LDM_CARD0:16:37IRPT_IRP_FLUIDITY_BASIC0:10:37MDLM03_FIN_ACCT_STAT0:16:20PCRCC_CR_BASE_I0:10:28PFXT05_VCH_FINANCE_TMP0:15:31ACMTHIRD_PTY_MON_SUMM0:10:204.月末超长作业列表月末跑批：

系统名作业名跑批时间(分钟/min)IRPT_IRP_SAME_BIZ_AVGBALSQL语句无法跑批IRPT_IRP_DEPOSIT_ACTIVITYSQL语句无法跑批IFST_IFS_AC_DLSQL语句无法跑批OCMOCRM_HIGH_CUST_INFOSQL语句无法跑批5.ETL脚本性能问题与原因分析业务规则理解数据访问路径选取的合理性SQL策略与算法Multi-Statement实现并行Insert…Select…比对算法不标准加载策略不优-大数据量Update、向非空表进行Insert

一个任务内多脚本，过多的数据库录入/录出ACRM中居多模型优化PPI和PI的选择对小表或代码表的部分字段进行CollectStatistics,优化sql的执行路径实际并发运行环境的影响6.DuplicateRowsAduplicaterowisarowofatablewhosecolumnvaluesareallidenticaltoanotherrowinthesametable.col_a col_b col_c20 50 A25 50 A25 50 ADuplicateRowsBecauseaPKuniquelyidentifieseachrow,ideallyarelationaltableshouldnothaveduplicaterows!TheANSIstandard,however,permitsduplicaterowsforspecializedsituations,thusTeradatapermitsthemaswell.Youmayselectwhetheryourtablewillorwillnotallowthem.*Note:IfaUPIisselectedonaSETtable,theduplicaterowcheckisreplacedbyacheckforduplicateindexvalues.CREATESETTABLEtable_A : :CREATEMULTISETTABLEtable_B : :Checksfor*anddisallowsduplicaterows.Doesn’tcheckforandallowsduplicaterows.TheTeradatadefaultTheANSIdefault7.RowDistributionUsingaUPI–Case1Notes:Often,butnotalways,thePKcolumn(s)willbeusedasaUPI.PIvaluesforOrder_Numberareknowntobeunique(it’saPK).TeradatawilldistributedifferentindexvaluesevenlyacrossallAMPs.ResultingrowdistributionamongAMPsisveryuniform.Assuresmaximumefficiencyforparalleloperations.AMPAMPAMPAMPo_# c_# o_dt o_st7202 2 4/09 C7415 1 4/13 Co_# c_# o_dt o_st7325 2 4/13 O7103 1 4/10 O7402 3 4/16 Co_# c_# o_dt o_st7188 1 4/13 C7225 2 4/15 Co_# c_# o_dt o_st7324 3 4/13 O7384 1 4/12 COrder8.RowDistributionUsingaNUPI–Case2Notes:Customer_NumbermaybethepreferredaccesscolumnforORDERtable,thusagoodindexcandidate.ValuesforCustomer_Numberaresomewhatnon-unique.ChoiceofCustomer_NumberisthereforeaNUPI.RowswiththesamePIvaluedistributetothesameAMP.Rowdistributionislessuniformorskewed.o_# c_# o_dt o_st7325 2 4/13 O7202 2 4/09 C7225 2 4/15 Co_# c_# o_dt o_st7384 1 4/12 C7103 1 4/10 O7415 1 4/13 C7188 1 4/13 Co_# c_# o_dt o_st7402 3 4/16 C7324 3 4/13 OAMPAMPAMPAMPOrder9.RowDistributionUsingaHighlyNon-UniquePrimaryIndex(NUPI)–Case3OrderNotes:ValuesforOrder_Statusare“highly”non-unique.ChoiceofOrder_StatuscolumnisaNUPI.Onlytwovaluesexist,soonlytwoAMPswilleverbeusedforthistable.Tablewillnotperformwellinparalleloperations.Highlynon-uniquecolumnsarepoorPIchoicesgenerally.Thedegreeofuniquenessiscriticaltoefficiency.AMPAMPAMPAMPo_# c_# o_dt o_st7402 3 4/16 C7202 2 4/09 C7225 2 4/15 C7415 1 4/13 C7188 1 4/13 C7384 1 4/12 Co_# c_# o_dt o_st7103 1 4/10 O7324 3 4/13 O7325 2 4/13 O10.JoinRedistributionSELECT ...FROM Table1T1INNERJOIN Table2T2ON T1.A=T2.A;Joincolumnsarefromthesamedomain. NoRedistributionneeded.T1

A B C PI

100 214 433T2

A B C PI

100 725 002SELECT ...FROM Table3T3INNERJOIN Table4T4ON T3.A=T4.B;Joincolumnsarefromthesamedomain. Redistributionneeded.T3

A B C PI

255 345 225T4 A B C PI 867 255 566SPOOL A B C PI 867 255 566RedistributeT4rowsinspooloncolumnB.11.JoinRedistribution(cont.)Joinisoncolumnsthatisn'tthePrimaryIndexofeithertable.SELECT ...FROM Table5T5INNERJOIN Table6T6ON T5.B=T6.C;Joincolumnsarefromthesamedomain. Redistributionneeded.T5 A B C PI 456 777 876T6 A B C PI 993 228 777RedistributeT5rowsinspooloncolumnB.RedistributeT6rowsinspooloncolumnC.SPOOL A B C PI 456 777 876SPOOL A B C PI 993 228 777IfthecolumnsbeingjoinedtogetherarenotPrimaryIndexcolumns(fromsamedomain),optionstheOptimizermaychoosefrominclude:Redistributebothtablesinspool(asshownabove)DuplicatethesmallertableinspoolacrossallAMPs12.DuplicatingaTableinSpool1Mrows1Mrows1Mrows1Mrows1Mrows1Mrows1Mrows1MrowsTableFormergejoins,theoptimizermaychoosetoduplicateasmalltableoneachAMP.Forproductjoins,theoptimizeralways

duplicatesonetableacrossallAMPs.Ineithercase,eachAMPmusthaveenoughspoolspaceforacompletecopy.Table1Mrows8MrowsSPOOL(TableisduplicatedoneachAMP)1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows1Mrows8Mrows13.MergeJoinRowsmustbeonthesameAMPtobejoined.MergeJoinreadsblocksfrombothtablesonlyonce.Usuallychosenforanequalityjoincondition.Generallymoreefficientthanaproductjoin.Mergejoinprocess:IdentifytheSmallerTable.Ifnecessary:Putqualifyingdataofoneorbothtablesintospool(s).MovethespoolrowstoAMPsbasedonthejoincolumnhash.Sortthespoolrowsintojoincolumnhashsequence.Comparetherowswithmatchingjoincolumnrowhashvalues.Causessignificantlyfewercomparisonsthanaproductjoin.A3 DataA3 DataA3 DataB7 DataB7 DataC4 DataC4 DataJoinColumnHashA3 DataB8 DataC4 DataJoinColumnHash14.ProductJoinRowsmustbeonthesameAMPtobejoined.DataDataDataDataDataDataDataDataDataDataDoesnotsorttherows.Mayre-readblocksfromonetableifAMPmemorysizeisexceeded.ItcompareseveryqualifyingTable1rowtoeveryqualifyingTable2row.ThosethatmatchtheWHEREconditionaresavedinspool.ItiscalledaProductJoinbecause:TotalCompares=#QualifiedRowsTable1*#QualifiedRowsTable2TheinternalcomparesbecomeverycostlywhentherearemorerowsthanAMPmemorycanholdatonetime.Theyaregenerallyunintentionalandoftengivemeaninglessoutput.ProductJoinprocess:IdentifytheSmallerTableandduplicateitinspoolonallAMPs.JoineachspoolrowforSmallerTabletoeveryrowforLargerTable.15.脚本SQL案例分析1–业务规则理解以CAS的CLAT任务脚本为例其中，统计每个帐户的最近的交易日期，通过金融传票的明细进行汇总groupby取得(效率较低);

SELECTAcct_Num,max(Tx_Dt)FROMCAS.V_FINANCE_EVENTWHEREsubstring(acct_numfrom19for3)IN('209','229')GROUPBY1;

实际可以从T_P_LOAN_CONTRACT中last_tx_dt字段得到

脚本调整后,运行时间16mins53secsVS.2mins*脚本开发人员应提高对数据模型的理解，加强与设计人员的沟通16.脚本SQL案例分析2–并行Insert修改前：

insertintoAselect*fromB;

insertintoAselect*fromC;修改后：insertintoAselect*fromB;insertintoAselect*fromc;*并行插入空表不记录TransactionJournal*充分利用Teradata向空表Insert较快以及并行操作的特性17.脚本SQL案例分析3–Delete/Insert以T03_AGMT_RELA_H(20726301)任务脚本为例在脚本中delete表中大部分记录，然后进行插入

DELETEFROMPDATA.T03_AGMT_RELA_H/*修改删除条件,将删除条件改为主键*/WHERE(…)IN(SELECT…FROMPWORK.VT_T03_AGMT_RELA_H_curWHEREAgmt_Rela_Type_CdNOTIN('01','02','03','04','05','06','07','08','09','17','61','62','63','64','65','66','67','68'));INSERTINTOPDATA.T03_AGMT_RELA_H***Deletecompleted.11169050rowsremoved.***Totalelapsedtimewas59minutesand45seconds.***Insertcompleted.11185846rowsadded.18.脚本SQL案例分析3–Delete/Insert调整SQL策略先将需要保留的数据插入临时表清空目标表通过Multi-Statement对目标表进行并行insert…select操作*利用Teradata空表insert操作与并行的特性*避免大数据量的delete,update操作19.脚本SQL案例分析4–避免大表字段转换在WHERE条件的运算操作中，会出现对大表的字段进行类型转换或者其他处理，然后与小表字段或常量进行等值或比较操作

以SGR的T_P_PRE_LOAN_RET_CARD为例WHEREpre_loan_card_acct=b.tmp_Acct_NumANDCAST(SUBSTR(statis_dt,1,4)||SUBSTR(statis_dt,6,2)||SUBSTR(statis_dt,9,2)ASchar(8))='20051025';-----------------------------------------------------------------------------------------WHEREpre_loan_card_acct=b.tmp_Acct_NumAND

statis_dt=CAST(‘2005-10-25’ASDATEformat‘YYYY-MM-DD’)或WHEREpre_loan_card_acct=b.tmp_Acct_NumANDstatis_dt=‘2005-10-25’20.脚本SQL案例分析5–分析SQL的执行路径通过执行explainselect….fromaleftjoinbona…

观察Teradata优化器的所生成的执行路径是否合理SQL执行路径中应避免大表的在所有AMP重新分布小表应尽量在AMP上重新分布或者在所有AMP上复制通过以下技术手段可以引导优化器生成正确而合理的执行路径SQL本身写法的检查，Join条件是否正确，避免大表间的笛卡尔乘积相关表物理模型设计是否合理,PI的选择等对小表的索引字段以及在表关联join时多次出现的条件字段收集统计信息CollectStatistics以T03的T03_CARD_ACCT_BAL_COMPO_H为例21.脚本SQL案例分析5–分析SQL的执行路径Cont.多表joinFROMSDATA_FULL.S01_TGEN042T1LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT2ONT2.SrcTab_Cd='S01_HARDCODE'/*源表名*/ANDT2.Cde_Type='CURRENT_CAL_INT'/*代码类型*/ANDT1.CTD_INT_BLG_CDE=T2.SrcCde_Cd/*主表与映射表关联条件*/

LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT3ONT3.SrcTab_Cd='S01_TGEN015'/*源表名*/ANDT3.Cde_Type='CURRENCY'/*代码类型*/ANDT1.CRY_CDE=T3.SrcCde_Cd/*主表与映射表关联条件*/

LEFTJOINPDATA.T99_STD_CDE_MAP_INFOT4ONT4.SrcTab_Cd='S01_TGEN066'/*源表名*/ANDT4.Cde_Type='CRDCARD_BAL_COMPONENT'/*代码类型*/ANDT1.BCT_IDN_CDE=T4.SrcCde_Cd/*主表与映射表关联条件*/;

通过执行Explain可以观察该SQL的执行路径,如下22.脚本SQL案例分析5–分析SQL的执行路径Cont.3)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindex"PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT'"witharesidualconditionof("(PDATA.T4.SrcTab_Cd='S01_TGEN066')AND(PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT')")intoSpool2(all_amps),whichisduplicatedonallAMPs.ThesizeofSpool2isestimatedwithhighconfidencetobe684rows.Theestimatedtimeforthisstepis0.00seconds.4)Weexecutethefollowingstepsinparallel.1)Wedoanall-AMPsJOINstepfromSDATA_FULL.T1bywayofanall-rowsscanwithnoresidualconditions,whichisjoinedtoSpool2(LastUse)bywayofanall-rowsscan.SDATA_FULL.T1andSpool2areleftouterjoinedusingaproductjoin,withajoinconditionof("SDATA_FULL.T1.BCT_IDN_CDE=SrcCde_Cd").TheinputtableSDATA_FULL.T1willnotbecachedinmemory.TheresultgoesintoSpool3(all_amps),whichisredistributedbyhashcodetoallAMPs.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool3isestimatedwithnoconfidencetobe54,801,029rows.Theestimatedtimeforthisstepis30.76seconds.2)Wedoasingle-AMPRETRIEVEstepfromPDATA.T3bywayoftheprimaryindex"PDATA.T3.Cde_Type='CURRENCY'"witharesidualconditionof("(PDATA.T3.SrcTab_Cd='S01_TGEN015')AND(PDATA.T3.Cde_Type='CURRENCY')")intoSpool4(all_amps),whichisredistributedbyhashcodetoallAMPs.ThesizeofSpool4isestimatedwithhighconfidencetobe146rows.Theestimatedtimeforthisstepis0.00seconds.23.脚本SQL案例分析5–分析SQL的执行路径Cont.

No.DBNameTableNamePerm(MB)SkewIndex1SDATA_FULLS01_TGEN04212,422.32.1PRIMARYINDEX(ACT_IDN_SKY)2PDATAT99_STD_CDE_MAP_INFO0.593.5PRIMARYINDEXXIE1T99_STD_CDE_MAP_INFO(Cde_Type)COLLECT

STATS

USING

SAMPLE

ONSDATA_FULL.S01_TGEN042COLUMN(BCT_IDN_CDE);COLLECT

STATS

USING

SAMPLE

ONSDATA_FULL.S01_TGEN042COLUMN(CRY_CDE);脚本SQL案例分析5–分析SQL的执行路径Cont.3)Weexecutethefollowingstepsinparallel.1)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindex"PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT'"witharesidualconditionof("(PDATA.T4.SrcTab_Cd='S01_TGEN066')AND(PDATA.T4.Cde_Type='CRDCARD_BAL_COMPONENT')")intoSpool2(all_amps),whichisduplicatedonallAMPs.ThesizeofSpool2isestimatedwithhighconfidencetobe684rows.Theestimatedtimeforthisstepis0.00seconds.2)Wedoasingle-AMPRETRIEVEstepfromPDATA.T3bywayoftheprimaryindex"PDATA.T3.Cde_Type='CURRENCY'"witharesidualconditionof("(PDATA.T3.SrcTab_Cd='S01_TGEN015')AND(PDATA.T3.Cde_Type='CURRENCY')")intoSpool3(all_amps),whichisduplicatedonallAMPs.ThenwedoaSORTtoorderSpool3byrowhash.ThesizeofSpool3isestimatedwithhighconfidencetobe16,644rows.Theestimatedtimeforthisstepis0.04seconds.

25.脚本SQL案例分析5–分析SQL的执行路径Cont.4)Wedoanall-AMPsJOINstepfromSDATA_FULL.T1bywayofanall-rowsscanwithnoresidualconditions,whichisjoinedtoSpool2(LastUse)bywayofanall-rowsscan.SDATA_FULL.T1andSpool2areleftouterjoinedusingaproductjoin,withajoinconditionof("SDATA_FULL.T1.BCT_IDN_CDE=SrcCde_Cd").TheinputtableSDATA_FULL.T1willnotbecachedinmemory.TheresultgoesintoSpool4(all_amps),whichisbuiltlocallyontheAMPs.ThenwedoaSORTtoorderSpool4byrowhash.Theresultspoolfilewillnotbecachedinmemory.ThesizeofSpool4isestimatedwithlowconfidencetobe54,722,052rows.Theestimatedtimeforthisstepis45.95seconds.26.脚本SQL案例分析5–分析SQL的执行路径Cont.优化后，性能显著提高27.脚本SQL案例分析6–物理表的设计DELETEFROMPDATA.T03_AGMT_PTY_RELA_H/*修改删除条件,将删除条件改为主键*/WHERE(Agmt_Id/*协议号*/,Agmt_Mdfr/*协议修饰符*/,Agmt_Pty_Rela_Type_Cd/*协议当事人关系类型代码*/)IN(SELECTAgmt_Id/*协议号*/,Agmt_Mdfr/*协议修饰符*/,Agmt_Pty_Rela_Type_Cd/*协议当事人关系类型代码*/FROMPWORK.VT_T03_AGMT_PTY_RELA_H_cur2)Insert…***Deletecompleted.29728007rowsremoved.***Totalelapsedtimewas10minutesand40seconds.***Insertcompleted.29814092rowsadded.28.脚本SQL案例分析6–物理表的设计CREATE

MULTISET

TABLET03_AGMT_PTY_RELA_H,NO

FALLBACK,

NO

BEFORE

JOURNAL,

NO

AFTER

JOURNAL,

CHECKSUM=DEFAULT(Party_IdCHAR(19)CHARACTERSETLATINCASESPECIFICTITLE'当事人编号'NOT

NULL,Agmt_IdCHAR(21)CHARACTERSETLATINCASESPECIFICTITLE'协议号'NOT

NULL,Agmt_MdfrVARCHAR(18)CHARACTERSETLATINCASESPECIFICTITLE'协议修饰符'NOT

NULL,Agmt_Pty_Rela_Type_CdCHAR(2)CHARACTERSETLATINCASESPECIFICTITLE'协议当事人关系类型代码'NOT

NULL,St_DtDATEFORMAT'YYYYMMDD'TITLE'开始日期'NOT

NULL

DEFAULTDATE'1900-01-01',End_DtDATEFORMAT'YYYYMMDD'TITLE'结束日期'NOT

NULL

DEFAULTDATE'3000-12-31')PRIMARY

INDEXXIE1T03_AGMT_PTY_RELA_H(Agmt_Id)PARTITIONBYRANGE_N(CAST((Agmt_Pty_Rela_Type_Cd)ASINTEGER)BETWEEN1AND100EACH1,NORANGEORUNKNOWN);COLLECTSTATSONT03_AGMT_PTY_RELA_HCOLUMNPARTITION；

29.脚本SQL案例分析6–物理表的设计DELETEFROMPDATA.T03_AGMT_PTY_RELA_H/*修改删除条件,将删除条件改为主键*/WHERE(Agmt_Id/*协议号*/,Agmt_Mdfr/*协议修饰符*/,Agmt_Pty_Rela_Type_Cd/*协议当事人关系类型代码*/)IN(SELECTAgmt_Id/*协议号*/,Agmt_Mdfr/*协议修饰符*/,Agmt_Pty_Rela_Type_Cd/*协议当事人关系类型代码*/FROMPWORK.VT_T03_AGMT_PTY_RELA_H_cur2)ANDAgmt_Pty_Rela_Type_CdIN('14','15','81','18','19','84','85','16')–取特定的协议类型

Insert…30.脚本SQL案例分析6–物理表的设计

3)Wedoanall-AMPsDELETEfrom8partitionsoftest.T03_AGMT_PTY_RELA_Hwithaconditionof("(test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='14')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='15')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='81')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='18')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='19')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='84')OR((test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='85')OR(test.T03_AGMT_PTY_RELA_H.Agmt_Pty_Rela_Type_Cd='16')))))))").31.脚本SQL案例分析6–减