ORACLE数据加载性能研究

ORACLE 数据加载性能研究1. 研究目的和方法后线系统占了上海分中心产品数量的 40%以上，随着业务量的增长，自主优化将是今后的一项重要工作；另外随着后蓝图时代的到来，对海量数据挖掘与分析的各项技术预研也十分必要。所有这些后线系统的一个共同特点就是都要经过数据加载这个影响批量时间窗口的关键步骤。几乎所有后线系统都使用ORACLE 数据库，因此对 ORACLE 数据库加载工具 SQL*LOADER 的性能优化研究具有重要意义。SQL*LOADER 是从外部文件加载数据到 ORACLE 数据库的最佳首选工具，然而工具的使用性能受很多因素影响。首先工具本身有很多与性能相关的参数，例如加载路径模式、缓存设置、是否并行等，这些参数在不同的环境中可能有不同的最佳值；然后，复杂的外部环境可能对加载性能也有不同程序的影响，例如加载表或分区的各种属性（LOGGING、COMPRESS 、是否经过分析、是否有索引等） 、是否 CACHE 表、是否外部表以及数据库 I/O 参数等；另外，文件加载的内容和过程方法可能对性能也有一定影响，例如从文件如何选择字段、文件是否经过压缩、全量加工时是否使用 HINTS 等。项目组在使用 SQL*LOADER 的过程中有一些自己的方法和经验，但这些方法和经验可能并不全面和合适。ORACLE 的官方文档对如何使用和优化SQL*LOADER 有一些指导，但这些指导是否可靠和适用各种环境？在这里，将结合对 SQL*LOADER 的使用经验及 ORACLE 的官方指导，通过参考项目环境设计案例进行测试，力求对影响 SQL*LOADER 性能的各方面因素进行全面分析研究，总结出具有实用价值的指导方法，以帮助项目组高效使用SQL*LOADER 工具，并为今后架设高效的海量数据分析系统打好技术基础。2. 环境准备本次测试研究借用了一个合适的小机环境，参考了一个通用的数据文件加载和全量数据加工案例。 环境说明 系统环境硬件平台：IBM,9119-590处理器（PowerPC_POWER5）：8 个，lcpu=16内存：12544 MBOS：AIX5.3注：测试期间，topas 命令显示系统资源可用率基本在 60% 90%之间。 数据库环境版本：ORACLE DATABASE：10G（Release 10.2.0.3.0）SQL*LOADER：10.2.0.3参数：参数 值 是否缺省db_block_size 8192 FALSEsga_target 1536M FALSElock_sga FALSE TRUEdb_2k_cache_size 64M FALSEdb_file_multiblock_read_count 16 FALSEparallel_max_servers 285 TRUEpga_aggregate_target 962 FALSE 数据准备建表脚本附件：ORACLE-.txt 准备全量数据创建全量表 PER_SUB_INVM_B：建表脚本参见附件中的语句：create table PER_SUB_INVM_B导入全量表数据：imp hdqs/hdqs file=per_invm_b.dmp log=./imp.log tables=per_sub_invm_b BUFFER=10240000 ignore=y statistics=none全量数据记录数：31152966 准备加载文件及相应的控制文件压缩的数据文件 数据量 控制文件0134690D.w01.gz.201109201 40 万 0134690D.w01.ctl0134690D.w01.fix.ctl0132510D.w01.gz.201109201 100 万0132510D.w01.ctl0132510D.w01.full.ctl0132510D.w01.50.ctl0132510D.w01.stream.ctl0107400D.w01.gz.201109201 700 万 0107400D.w01.ctl 创建临时表 TMP_INVM_PER_B注：将临时表建成分区表，方便管理和能够提高性能。由于对临时表的使用基本是全分区描述，所以不需建索引。建表脚本参见附件中的语句：create table TMP_INVM_PER_B 创建临时表 TMP_INVM_PER_B_FULL注：创建本表是为了测试全字段加载和部分字段加载的性能差别建表脚本参见附件中的语句：create table TMP_INVM_PER_B_FULL 创建临时表 TMP_INVM_PER_B_50注：创建本表是为了测试基准加载速率建表脚本参见附件中的语句：create table TMP_INVM_PER_B_50 创建外部表 TMP_INVM_PER_B_EX_43251创建文件目录 dir，并授权：(hdqsi04):/hdqsi04/hdqs/loadtask mkdir di(hdqsi04):/hdqsi04/hdqs/loadtask chmod 777 dir 或，至少也要执行：(hdqsi04):/hdqsi04/hdqs/loadtask chmod o+x dir注：使用外部表需要创建目录对象，必须对相应的文件目录设置访问权限：否则，在查询外部表时出现如下错误：ORA-29913: error in executing ODCIEXTTABLEOPEN calloutORA-29400: data cartridge errorKUP-04063: unable to open log file 0132510D.w01.ex.logOS error Permission deniedORA-06512: at “SYS.ORACLE_LOADER“, line 19创建目录对象：create directory DIR_LOADTASKas /hdqsi04/hdqs/loadtask/dir;创建外部表：先使用带 external_table=GENERATE_ONLY 参数的 sqlldr 命令生成创建外部表的 SQL 语句模板：time sqlldr hdqs/hdqs control=./0132510D.w01.ctl log=0132510D.w01.ex.log bindsize=40960000 rows=10000 EXTERNAL_TABLE=GENERATE_ONLY然后在 0132510D.w01.ex.log 中提取创建外部表语句，更改其中的表名、目录对象名称等信息，生成实际的建表语句。建表脚本参见附件中的语句：CREATE TABLE “TMP_INVM_PER_B_EX_43251“ 3. 研究内容与测试分析 研究 1：sqlldr 缓存和事务参数对加载性能的影响首先介绍通常比较影响加载性能的缓存和事务等参数。这些参数的优化值依操作系统、输入数据类型、ORACLE 列数据类型等因素而变化。为这些参数设置合适的较大值将提高加载性能，但是如果参数设置过大导致进程 PAGING过度，性能将急剧下降。不合理的参数还会导致缓存分配失败而报错。本节将研究如何使用好这些参数达到性能优化的通用规律。READSIZE 设置数据文件的读缓存大小，缺省值 1000000 字节（近 1MB） 。对于常规路径和直接路径，最大允许值都是 20M。在常规路径下，BINDSIZE 受限于本参数大小。然而如果本参数值小于BINDSIZE 值，本参数将随之增大（ORACLE 文档上并没说当此值小于直接路径的 COLUMNARRAYROWS 参数时也随之增大） 。如果此参数设置过大（本环境测试中当常规路径或直接路径达到 70MB 附近时） ，将出现“SQL*Loader-556: unable to allocate read buffer”和“SQL*Loader-509: System error: Not enough space”错误。而当此参数较小时，常规路径下也会随 BINDSIZE 值而被动增大，因此这种情况下可以省略此参数。BINDSIZE 用于设置常规路径下 bind array 缓存区（可理解为按照 INSERT语句组织的有结构缓存区）的最大值，缺省值 256000 字节（近 256KB） 。较大的参数值将容纳更多的数据行，减少调用数据库的次数，从而可提高性能。覆盖由于 ROWS 参数而确定的本参数大小。BINDSIZE 必须至少可以容纳一行数据，否则报错。ORACLE 会尽量使用BINDSIZE 区域而容纳足够多的数据行，直到达到 ROWS 参数值的限制。BINDSIZE 中的每个区域（如定长区、变长区、图形区等）需要连续的内存，如果操作系统不能提供，将报错。当此参数设置过大（本环境测试中当达到70MB 附近时）也将出现“SQL*Loader-556: unable to allocate read buffer”和“SQL*Loader-509: System error: Not enough space”错误。ORACLE 官方文档没有推荐使用本参数的最大值，为安全起见，我们可以设置为 20M。COLUMNARRAYROWS 用于设置直接路径下分配给 column arrays 区（可理解为按照 ORACLE 表空间数据文件存储组织的有结构缓存区）用于容纳记录的行数（在 stream 缓存创建之前） ，缺省值 5000。此参数如果设置过大（本环境测试达到 93000 附近）将出现“SQL*Loader-700: Out of memory”；继续增大，还会出现“OCI-04030: out of process memory”错误，或前面参数相同的错误。ORACLE 官方文档没有推荐使用本参数的最大值，为安全起见，我们可以设置为 2000050000。STREAMSIZE 参数指定直接路径 steam buffer 缓存的大小。缺省值 256000字节（近 256KB） 。当在多 CPU 系统上执行直接路径装载时，指定 column array和 steam buffer 大小特别有用。MULTITHREADING 参数只用于直接路径，在多 CPU 系统上缺省值是TRUE，在单 CPU 系统上缺省值是 FALSE。多线程加载意味着，column arrays 到 stream buffers 的转换，和 stream buffer的加载是并行执行的（如果可能） 。当 Column arrays 足够大而可以容纳多个stream buffers 时，才可能实现这一优化。ROWS 参数对于常规路径，用于设置 bind array 缓存区中每次 commit 行数；对于直接路径，用于设置 column arrays 缓存区中每次 data saves 行数。对于常规路径，缺省值是 64；对于直接路径，缺省值是 all。对于常规路径，bind array 缓存区数据是通过 INSERT 语句灌入到数据库中，如果 ROWS 参数设置太小，commit 太频繁，会增加数据库额外开销，速度降低；但 ROWS 受限于前面的缓存参数，独自设大值也起不了作用（设置小值却是有效的） 。对于直接路径，Data Save 是高耗时操作，官方推荐将此参数值设置到 Data Save 大于 15 分钟才发生一次。注：ROWS 参数不支持并行加载模式，如果在并行加载命令行中使用此参数，报错“SQL*Loader-281: Warning: ROWS parameter ignored in parallel mode.” 常规路径测试根据系统资源都设定尽可能大的参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log readsize=40960000 bindsize=40960000 rows=10000Commit point reached - logical record count 8819Commit point reached - logical record count 10733.第一次 第二次 第三次real 1m48.80s real 1m48.80s real 1m47.30suser 1m41.01s user 1m41.01s user 1m42.23ssys 0m1.18s sys 0m1.18s sys 0m1.27s都使用缺省参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log Commit point reached - logical record count 64Commit point reached - logical record count 128第一次 第二次 第三次real 2m1.19s real 2m3.70s real 1m59.17suser 1m40.63s user 1m40.73s user 1m40.49ssys 0m1.25s sys 0m1.26s sys 0m1.26s使用优化参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log bindsize=20480000 rows=10000Commit point reached - logical record count 4409Commit point reached - logical record count 5366第一次 第二次 第三次real 1m45.75s real 1m47.42s real 1m46.35suser 1m40.70s user 1m40.70s user 1m40.52ssys 0m0.98s sys 0m0.98s sys 0m1.02s 直接路径测试根据系统资源都设定尽可能大的参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log direct=true readsize=20480000 COLUMNARRAYROWS=50000 rows=50000Save data point reached - logical record count 50000.Save data point reached - logical record count 100000.第一次 第二次 第三次real 1m56.56s real 1m56.99s real 1m56.71suser 1m43.81s user 1m43.83s user 1m43.79ssys 0m0.94s sys 0m0.91s sys 0m0.93s全使用缺省参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log direct=true 第一次 第二次 第三次real 2m0.17s real 2m1.25s real 2m0.31suser 1m42.61s user 1m42.90s user 1m42.44ssys 0m0.86s sys 0m0.86s sys 0m0.87s只设置 COLUMNARRAYROWS 参数：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log direct=true COLUMNARRAYROWS=50000第一次 第二次 第三次real 2m1.44s real 2m0.74s real 2m0.61suser 1m42.67s user 1m43.00s user 1m42.74ssys 0m0.85s sys 0m0.88s sys 0m0.86s只使用官方推荐的 READSIZE 参数最大值：SQL alter table TMP_INVM_PER_B truncate Partition TMP_INVM_PER_B_43469;time sqlldr hdqs/hdqs control=./0134690D.w01.ctl log=0134690D.w01.log direct=true readsize=20480000第一次 第二次 第三次real 1m56.44s real 1m57.45s real 1m56.40suser 1m43.29s user 1m43.38s user 1m43.53ssys 0m0.94s sys 0m0.90s sys 0m0.94s 分析与结论对于常规路径：都使用尽可能大的参数跟都使用缺省参数相比，CPU 时间（sys+user）三次分别慢了 0.31s、0.21s、1.75s，而实际使用时间三次分别快了12.39s、14.9