ETL性能问题分析与调整方法PPT学习教案

1、会计学1 ETL性能问题分析与调整方法性能问题分析与调整方法 第1页/共35页 系统 作业运行时间 SDDT05_EVENT_FUND_TXN_EXP1:47:03 ALSALM_DEPOSIT_FIX_DATA1:05:00 BRSBRS_EXP_DATA0:51:52 MAST_AGMT_INFO_A0:47:01 SDDSRDFTPONE0:46:08 MAST_AGMT_INFO_B0:46:02 PCRPCR_EXPORT0:43:20 SDDSRDFTPTHREE0:43:07 MASOTHER0:39:33 SDDSRDFTPTWO0:38:43 OCM99_OCRM_JOB_E

2、ND0:37:20 ALSALM_GROUP_INT_H0:33:19 第2页/共35页 系统 作业运行时间系统作业运行时间 BAKBAK_ESSAPS_SRC0:29:14DQCT03_AGMT_AMT_H0:15:18 T03T03_AGREEMENT_RT0:28:20CCRGRDJKFEXPDATA0:15:03 PMMJXKH_C_LDM0:28:19DQCDQC_CHECK_KPI0:14:50 BAKBAK_ETLENV_710:27:34PMMJXKH_P_LDM_NET0:14:08 T03T03_CARD_ACCT_BAL_COMPO_H0:24:39ALSALM_LOAN

3、_DATA0:13:57 T03T03_AGREEMENT_S020:24:19MAST_MA_BALANCE0:13:53 T03T03_AGMT_ACCU_H0:23:59CBST_CBS_BAL_DL0:13:18 MAST_AGMT_INFO_C0:22:49BAKBAK_ETLENV_400:12:58 T03T03_AGMT_BAL_H0:22:17MASFTPFILE0:12:32 PMMJXKH_P_LDM_SAV0:21:33ACMCUST_FIX_IND_POST0:12:31 CFST_CFS_AC_TREE0:21:31T05T05_CRDCARD_TXN_DTL0:1

4、2:05 SDDBCNTDEPO0:20:57ECLNB_CUST_TXN_SUMM0:12:03 DQCT03_CARD_ACCT_BAL_COMPO_H0:20:32ECLNB_NEW_CUST_SUMM0:11:58 MDLM03_FIN_ACCT0:19:37BDET03_ACCT_CRDCARD0:11:53 BDET03_AGREEMENT0:19:26IFST_IFS_BAL_BASIC0:11:20 IRPT_DEP_POT_INT_RATE0:17:24MDLM01_CUST_SIGN_INFO0:11:16 PCRCC_CR_ID_I0:17:08IRPT_LOAN_POT

5、_INT_RATE0:11:07 SDDREPAYDET0:17:06RIRDEPOS_BAL_DATA0:11:07 T03T03_AGMT_BAL_H_S010:17:01T05T05_EVT_MESSAGE_SEND0:10:47 ACMCUST_BASIC_INFO_PRE0:16:51CRST_REP_GJYW_ZB0:10:46 CABCAB_EXP_DATA0:16:40MDLM03_CARD_TXN_STAT0:10:44 PMMJXKH_P_LDM_CARD0:16:37IRPT_IRP_FLUIDITY_BASIC0:10:37 MDLM03_FIN_ACCT_STAT0:

6、16:20PCRCC_CR_BASE_I0:10:28 PFXT05_VCH_FINANCE_TMP0:15:31ACMTHIRD_PTY_MON_SUMM0:10:20 第3页/共35页 系统名作业名跑批时间(分钟/min) IRPT_IRP_SAME_BIZ_AVGBALSQL语句无法跑批 IRPT_IRP_DEPOSIT_ACTIVITYSQL语句无法跑批 IFST_IFS_AC_DLSQL语句无法跑批 OCMOCRM_HIGH_CUST_INFOSQL语句无法跑批 第4页/共35页 第5页/共35页 Duplicate Rows A duplicate row is a row of

7、a table whose column values are all identical to another row in the same table. col_acol_bcol_c 2050A 2550A 2550A Duplicate Rows Because a PK uniquely identifies each row, ideally a relational table should not have duplicate rows! The ANSI standard, however, permits duplicate rows for specialized si

8、tuations, thus Teradata permits them as well. You may select whether your table will or will not allow them. * Note: If a UPI is selected on a SET table, the duplicate row check is replaced by a check for duplicate index values. CREATE SET TABLE table_A : : CREATE MULTISET TABLE table_B : : Checks f

9、or * and disallows duplicate rows.Doesnt check for and allows duplicate rows. The Teradata defaultThe ANSI default 第6页/共35页 Row Distribution Using a UPI Case 1 Notes: Often, but not always, the PK column(s) will be used as a UPI. PI values for Order_Number are known to be unique (its a PK). Teradata

10、 will distribute different index values evenly across all AMPs. Resulting row distribution among AMPs is very uniform. Assures maximum efficiency for parallel operations. AMPAMPAMPAMP o_#c_#o_dt o_st 720224/09 C 741514/13 C o_#c_#o_dt o_st 732524/13 O 710314/10 O 740234/16 C o_#c_#o_dt o_st 718814/1

11、3 C 722524/15 C o_#c_#o_dt o_st 732434/13 O 738414/12 C Order Num ber Custom er Num ber Order Date Order Status PK UPI 7325 7324 7415 7103 7225 7384 7402 7188 7202 2 3 1 1 2 1 3 1 2 4/13 4/13 4/13 4/10 4/15 4/12 4/16 4/13 4/09 O O C O C C C C C Order 第7页/共35页 Row Distribution Using a NUPI Case 2 Not

12、es: Customer_Number may be the preferred access column for ORDER table, thus a good index candidate. Values for Customer_Number are somewhat non-unique. Choice of Customer_Number is therefore a NUPI. Rows with the same PI value distribute to the same AMP. Row distribution is less uniform or skewed.

13、o_#c_#o_dt o_st 732524/13 O 720224/09 C 722524/15 C o_#c_#o_dt o_st 738414/12 C 710314/10 O 741514/13 C 718814/13 C o_#c_#o_dt o_st 740234/16 C 732434/13 O AMPAMPAMPAMP Order Num ber Custom er Num ber Order Date Order Status PK NUPI 7325 7324 7415 7103 7225 7384 7402 7188 7202 2 3 1 1 2 1 3 1 2 4/13

14、 4/13 4/13 4/10 4/15 4/12 4/16 4/13 4/09 O O C O C C C C C Order 第8页/共35页 Row Distribution Using a Highly Non-Unique Primary Index (NUPI) Case 3 Order Num ber Custom er Num ber Order Date Order Status PK NUPI 7325 7324 7415 7103 7225 7384 7402 7188 7202 2 3 1 1 2 1 3 1 2 4/13 4/13 4/13 4/10 4/15 4/1

15、2 4/16 4/13 4/09 O O C O C C C C C Order Notes: Values for Order_Status are “highly” non- unique. Choice of Order_Status column is a NUPI. Only two values exist, so only two AMPs will ever be used for this table. Table will not perform well in parallel operations. Highly non-unique columns are poor

16、PI choices generally. The degree of uniqueness is critical to efficiency. AMPAMPAMPAMP o_#c_#o_dt o_st 740234/16 C 720224/09 C 722524/15 C 741514/13 C 718814/13 C 738414/12 C o_#c_#o_dt o_st 710314/10 O 732434/13 O 732524/13 O 第9页/共35页 Join Redistribution SELECT. . . FROMTable1 T1 INNER JOINTable2 T

17、2 ONT1.A = T2.A; Join columns are from the same domain.No Redistribution needed. T1 AB C PI 100214 433 T2 AB C PI 100725 002 SELECT. . . FROMTable3 T3 INNER JOINTable4 T4 ONT3.A = T4.B; Join columns are from the same domain.Redistribution needed. T3 AB C PI 255345 225 T4 AB C PI 867255 566 SPOOL AB

18、C PI 867255 566 Redistribute T4 rows in spool on column B. 第10页/共35页 Join Redistribution (cont.) Join is on columns that isnt the Primary Index of either table. SELECT. . . FROMTable5 T5 INNER JOINTable6 T6 ONT5.B = T6.C; Join columns are from the same domain.Redistribution needed. T5 AB C PI 456777

19、 876 T6 AB C PI 993228 777 Redistribute T5 rows in spool on column B. Redistribute T6 rows in spool on column C. SPOOL AB C PI 456777 876 SPOOL AB C PI 993228 777 If the columns being joined together are not Primary Index columns (from same domain), options the Optimizer may choose from include: Red

20、istribute both tables in spool (as shown above) Duplicate the smaller table in spool across all AMPs 第11页/共35页 Duplicating a Table in Spool 1M rows1M rows1M rows1M rows1M rows1M rows1M rows1M rowsTable For merge joins, the optimizer may choose to duplicate a small table on each AMP. For product join

21、s, the optimizer always duplicates one table across all AMPs. In either case, each AMP must have enough spool space for a complete copy. Table1M rows 8M rows SPOOL (Table is duplicated on each AMP) 1M rows 8M rows 1M rows 8M rows 1M rows 8M rows 1M rows 8M rows 1M rows 8M rows 1M rows 8M rows 1M row

22、s 8M rows 第12页/共35页 Merge Join Rows must be on the same AMP to be joined. Merge Join reads blocks from both tables only once. Usually chosen for an equality join condition. Generally more efficient than a product join. Merge join process: Identify the Smaller Table. If necessary: Put qualifying data

23、 of one or both tables into spool(s). Move the spool rows to AMPs based on the join column hash. Sort the spool rows into join column hash sequence. Compare the rows with matching join column row hash values. Causes significantly fewer comparisons than a product join. A3Data A3Data A3Data B7Data B7D

24、ata C4Data C4Data Join Column Hash A3Data B8Data C4Data Join Column Hash 第13页/共35页 Product Join Rows must be on the same AMP to be joined. Data Data Data Data Data Data Data Data Data Data Does not sort the rows. May re-read blocks from one table if AMP memory size is exceeded. It compares every qua

25、lifying Table1 row to every qualifying Table2 row. Those that match the WHERE condition are saved in spool. It is called a Product Join because: Total Compares = # Qualified Rows Table 1 * # Qualified Rows Table 2 The internal compares become very costly when there are more rows than AMP memory can

26、hold at one time. They are generally unintentional and often give meaningless output. Product Join process: Identify the Smaller Table and duplicate it in spool on all AMPs. Join each spool row for Smaller Table to every row for Larger Table. 第14页/共35页 n脚本调整后,运行时间 16mins 53secs VS. 2 mins * 脚本开发人员应提高对数据模型的脚本开发人员应提高对数据模型的 理解，加强与设计人员的沟通理解，加强与设计人员的沟通 第15页/共35页 * 并行插入空表不记录并行插入空表不记录Transaction Journal *充分利用充分利用Teradata向空表向空表Insert较快较快 以及并行操作的特性以及并行操作的特性 第16页/共35页 第17页/共35页 第18页/共35页 char(8)=20051025; - WHERE pre_loan_card_acct