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

BOCN数据仓库 ETL性能分析与调整方法 1 前言 随着数据仓库基础数据和应用的不断增加 数据仓库的性能问题日益突出 目前超过1小时的作业有2个30分钟到1小时的作业有10个10分钟到30分钟的作业有48个因此对数据仓库进行性能优化的需求越来越迫切 2 作业时长分析 共有12个作业运行时间在30分钟以上 3 共有48个作业运行时间为10分钟到30分钟 4 月末超长作业列表 月末跑批 5 ETL脚本性能问题与原因分析 业务规则理解数据访问路径选取的合理性SQL策略与算法Multi Statement实现并行Insert Select 比对算法不标准加载策略不优 大数据量Update 向非空表进行Insert一个任务内多脚本 过多的数据库录入 录出ACRM中居多模型优化PPI和PI的选择对小表或代码表的部分字段进行CollectStatistics 优化sql的执行路径实际并发运行环境的影响 6 DuplicateRows 7 RowDistributionUsingaUPI Case1 8 RowDistributionUsingaNUPI Case2 9 RowDistributionUsingaHighlyNon UniquePrimaryIndex NUPI Case3 10 JoinRedistribution 11 JoinRedistribution cont 12 DuplicatingaTableinSpool 13 MergeJoin 14 ProductJoin 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 NumANDstatis 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 COLLECTSTATSUSINGSAMPLEONSDATA FULL S01 TGEN042COLUMN BCT IDN CDE COLLECTSTATSUSINGSAMPLEONSDATA 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 物理表的设计 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 DtDATEFORMAT YYYYMMDD TITLE 开始日期 NOTNULLDEFAULTDATE 1900 01 01 End DtDATEFORMAT YYYYMMDD TITLE 结束日期 NOTNULLDEFAULTDATE 3000 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 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 减少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 cd A2 THEN 30001231 ELSE 20071223 ENDASClose 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 账号 A2 Agmt Mdfr 设置为 11 11 持有的