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

BOCN数据仓库ETL性能分析与调整方法,前言,随着数据仓库基础数据和应用的不断增加，数据仓库的性能问题日益突出。目前超过1小时的作业有2个30分钟到1小时的作业有10个10分钟到30分钟的作业有48个因此对数据仓库进行性能优化的需求越来越迫切,作业时长分析,共有12个作业运行时间在30分钟以上,共有48个作业运行时间为10分钟到30分钟,月末超长作业列表,月末跑批：,ETL脚本性能问题与原因分析,业务规则理解数据访问路径选取的合理性SQL策略与算法Multi-Statement实现并行InsertSelect比对算法不标准加载策略不优-大数据量Update、向非空表进行Insert一个任务内多脚本，过多的数据库录入/录出ACRM中居多模型优化PPI和PI的选择对小表或代码表的部分字段进行CollectStatistics,优化sql的执行路径实际并发运行环境的影响,DuplicateRows,RowDistributionUsingaUPICase1,RowDistributionUsingaNUPICase2,RowDistributionUsingaHighlyNon-UniquePrimaryIndex(NUPI)Case3,JoinRedistribution,JoinRedistribution(cont.),DuplicatingaTableinSpool,MergeJoin,ProductJoin,脚本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*脚本开发人员应提高对数据模型的理解，加强与设计人员的沟通,脚本SQL案例分析2并行Insert,修改前：insertintoAselect*fromB;insertintoAselect*fromC;修改后：insertintoAselect*fromB;insertintoAselect*fromc;*并行插入空表不记录TransactionJournal*充分利用Teradata向空表Insert较快以及并行操作的特性,脚本SQL案例分析3Delete/Insert,以T03_AGMT_RELA_H(20726301)任务脚本为例在脚本中delete表中大部分记录，然后进行插入DELETEFROMPDATA.T03_AGMT_RELA_H/*修改删除条件,将删除条件改为主键*/WHERE()IN(SELECTFROMPWORK.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.,脚本SQL案例分析3Delete/Insert,调整SQL策略先将需要保留的数据插入临时表清空目标表通过Multi-Statement对目标表进行并行insertselect操作*利用Teradata空表insert操作与并行的特性*避免大数据量的delete,update操作,脚本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_NumANDstatis_dt=CAST(2005-10-25ASDATEformatYYYY-MM-DD)或WHEREpre_loan_card_acct=b.tmp_Acct_NumANDstatis_dt=2005-10-25,脚本SQL案例分析5分析SQL的执行路径,通过执行explainselect.fromaleftjoinbona观察Teradata优化器的所生成的执行路径是否合理SQL执行路径中应避免大表的在所有AMP重新分布小表应尽量在AMP上重新分布或者在所有AMP上复制通过以下技术手段可以引导优化器生成正确而合理的执行路径SQL本身写法的检查，Join条件是否正确，避免大表间的笛卡尔乘积相关表物理模型设计是否合理,PI的选择等对小表的索引字段以及在表关联join时多次出现的条件字段收集统计信息CollectStatistics以T03的T03_CARD_ACCT_BAL_COMPO_H为例,脚本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的执行路径,如下,脚本SQL案例分析5分析SQL的执行路径Cont.,3)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindexPDATA.T4.Cde_Type=CRDCARD_BAL_COMPONENTwitharesidualconditionof(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.T3bywayoftheprimaryindexPDATA.T3.Cde_Type=CURRENCYwitharesidualconditionof(PDATA.T3.SrcTab_Cd=S01_TGEN015)AND(PDATA.T3.Cde_Type=CURRENCY)intoSpool4(all_amps),whichisredistributedbyhashcodetoallAMPs.ThesizeofSpool4isestimatedwithhighconfidencetobe146rows.Theestimatedtimeforthisstepis0.00seconds.,脚本SQL案例分析5分析SQL的执行路径Cont.,COLLECTSTATSUSINGSAMPLEONSDATA_FULL.S01_TGEN042COLUMN(BCT_IDN_CDE);COLLECTSTATSUSINGSAMPLEONSDATA_FULL.S01_TGEN042COLUMN(CRY_CDE);,脚本SQL案例分析5分析SQL的执行路径Cont.,3)Weexecutethefollowingstepsinparallel.1)Wedoasingle-AMPRETRIEVEstepfromPDATA.T4bywayoftheprimaryindexPDATA.T4.Cde_Type=CRDCARD_BAL_COMPONENTwitharesidualconditionof(PDATA.T4.SrcTab_Cd=S01_TGEN066)AND(PDATA.T4.Cde_Type=CRDCARD_BAL_COMPONENT)intoSpool2(all_amps),whichisduplicatedonallAMPs.ThesizeofSpool2isestimatedwithhighconfidencetobe684rows.Theestimatedtimeforthisstepis0.00seconds.2)Wedoasingle-AMPRETRIEVEstepfromPDATA.T3bywayoftheprimaryindexPDATA.T3.Cde_Type=CURRENCYwitharesidualconditionof(PDATA.T3.SrcTab_Cd=S01_TGEN015)AND(PDATA.T3.Cde_Type=CURRENCY)intoSpool3(all_amps),whichisduplicatedonallAMPs.ThenwedoaSORTtoorderSpool3byrowhash.ThesizeofSpool3isestimatedwithhighconfidencetobe16,644rows.Theestimatedtimeforthisstepis0.04seconds.,脚本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.,脚本SQL案例分析5分析SQL的执行路径Cont.,优化后，性能显著提高,脚本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.,脚本SQL案例分析6物理表的设计,CREATEMULTISETTABLET03_AGMT_PTY_RELA_H,NOFALLBACK,NOBEFOREJOURNAL,NOAFTERJOURNAL,CHECKSUM=DEFAULT(Party_IdCHAR(19)CHARACTERSETLATINCASESPECIFICTITLE当事人编号NOTNULL,Agmt_IdCHAR(21)CHARACTERSETLATINCASESPECIFICTITLE协议号NOTNULL,Agmt_MdfrVARCHAR(18)CHARACTERSETLATINCASESPECIFICTITLE协议修饰符NOTNULL,Agmt_Pty_Rela_Type_CdCHAR(2)CHARACTERSETLATINCASESPECIFICTITLE协议当事人关系类型代码NOTNULL,St_DtDATEFORMATYYYYMMDDTITLE开始日期NOTNULLDEFAULTDATE1900-01-01,End_DtDATEFORMATYYYYMMDDTITLE结束日期NOTNULLDEFAULTDATE3000-12-31)PRIMARYINDEXXIE1T03_AGMT_PTY_RELA_H(Agmt_Id)PARTITIONBYRANGE_N(CAST(Agmt_Pty_Rela_Type_Cd)ASINTEGER)BETWEEN1AND100EACH1,NORANGEORUNKNOWN);COLLECTSTATSONT03_AGMT_PTY_RELA_HCOLUMNPARTITION；,脚本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,脚本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).,脚本SQL案例分析6减少IO操作,INSERTINTOPWORK.VT_T03_AGMT_PTY_RELA_H_RT_cur(。)SELECTA1.CI_NO/*客户号*/,A1.AC/*账号*/,/*设置为*/,11/*协议当事人关系类型代码:11-持有的核心帐户*/,A2.OPEN_ACCT_DTASopen_date/*开户日期*/,CASEWHENA2.agmt_Stat_cdA2THEN30001231ELSE20071223ENDASClose_Dt/*销户日期*/FROMSDATA_FULL.S02_CITACRPA1INNERJOINPDATA.T03_AGREEMENT_RTA2ONA1.AC=A2.Agmt_Id;INSERTINTOPWORK.VT_T03_AGMT_PTY_RELA_H_RT_cur(。)SELECTCOALESCE(A1.RGN_CODE|A1.CINO,)ASParty_Id/*地区或分行号|客户编号*/,COALESCE(A1.AC,)ASAC/*账号*/,