优化sqlldr加载上100万条记录的数据！

来源：互联网发布：mysql 截断编辑：程序博客网时间：2024/06/02 12:45

 print?
[oracle@linux sqlldr]$ pwd  
/u01/sqlldr  
[oracle@linux sqlldr]$ ll  
总计 4  
-rw-r--r-- 1 oracle oinstall 228 03-14 18:55 getdata.sql  
[oracle@linux sqlldr]$ cat getdata.sql                          --创建一个sql脚本。  
SELECT A.OWNER || ',"' || A.OBJECT_NAME || '",' || A.OBJECT_ID || ',' ||  
       TO_CHAR(A.CREATED, 'yyyy-mm-dd hh24:mi:ss') || ',' || A.STATUS  
  FROM DBA_OBJECTS A,  
       (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 30) B;  
[oracle@linux sqlldr]$ sqlplus /nolog  
  
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 3月 14 18:55:27 2012  
  
Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
  
SQL> conn /as sysdba  
已连接。  
SQL> select count(*) from dba_objects;  
  
  COUNT(*)  
----------  
     71876  
  
SQL> set echo off  
SQL> set term off  
SQL> set feedback off  
SQL> set heading off  
SQL> spool /u01/sqlldr/data.dat  
SQL> @/u01/sqlldr/getdata.sql  
SQL> spool off  
SQL> set heading on  
SQL> set feedback on  
SQL> set term on  
SQL> set echo on  

可以看见dba_objects这个表有7万多条记录，将其与一个30跳记录的表做笛卡尔运算就就可以生成一个200多万跳记录了。

[sql] view plain copy
 print?
[oracle@linux sqlldr]$ ll  
总计 170904  
-rw-r--r-- 1 oracle oinstall 174824593 03-14 18:58 data.dat  
-rw-r--r-- 1 oracle oinstall       228 03-14 18:55 getdata.sql  
[oracle@linux sqlldr]$ wc -l data.dat   
2322150 data.dat  

可以看见data.dat这个文件有160多MB，而且这个文件有230多万行。

2、初始化环境

[sql] view plain copy
 print?
SQL> conn u1/u1  
已连接。  
SQL> create table t1(  
  2  owner varchar2(255),  
  3  object_name varchar2(255),  
  4  object_id number,  
  5  status varchar2(255),  
  6  created date);  
  
表已创建。  
  
SQL> create index ind_obj_owner_name on t1(owner,object_name);  
  
索引已创建。  

3、创建控制文件

[sql] view plain copy
 print?
[oracle@linux sqlldr]$ cat sqlldr.ctl   
LOAD DATA    
INFILE data.dat    
TRUNCATE INTO TABLE T1    
FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'    
(owner,    
 object_name,  
 object_id,  
 created date 'yyyy-mm-dd hh24:mi:ss',  
 status)  

4、执行导入

[sql] view plain copy
 print?
$ sqlldr u1/u1 control=sqlldr.ctl errors=999999  

因为data.dat里面有很多的空行，所以把errors设置得大一点。截取sqlldr.log日志文件最后一部分：

[sql] view plain copy
 print?
表 T1:  
  2156280 行 加载成功。  
  由于数据错误, 165870 行 没有加载。  
  由于所有 WHEN 子句失败, 0 行 没有加载。  
  由于所有字段都为空的, 0 行 没有加载。  
  
  
为绑定数组分配的空间:                 82560 字节 (64 行)    --rows是64，使用了82560字节的bindsize。  
读取   缓冲区字节数: 1048576  
  
跳过的逻辑记录总数:          0  
读取的逻辑记录总数:       2322150  
拒绝的逻辑记录总数:     165870  
废弃的逻辑记录总数:        0  
  
从 星期三 3月  14 20:02:25 2012 开始运行  
在 星期三 3月  14 20:13:05 2012 处运行结束  
  
经过时间为: 00: 06: 44.54  
CPU 时间为: 00: 00: 38.14  

5、能不能快一点呢

[sql] view plain copy
 print?
$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=640  

常规路径导入时默认一次加载64行，可能有点小了。现在给为640行。截取sqlldr.log日志文件最后一部分：

[sql] view plain copy
 print?
表 T1:  
  2156280 行 加载成功。  
  由于数据错误, 165870 行 没有加载。  
  由于所有 WHEN 子句失败, 0 行 没有加载。  
  由于所有字段都为空的, 0 行 没有加载。  
  
  
为绑定数组分配的空间:                255420 字节 (198 行)  
读取   缓冲区字节数: 1048576  
  
跳过的逻辑记录总数:          0  
读取的逻辑记录总数:       2322150  
拒绝的逻辑记录总数:     165870  
废弃的逻辑记录总数:        0  
  
从 星期三 3月  14 20:20:53 2012 开始运行  
在 星期三 3月  14 20:29:42 2012 处运行结束  
  
经过时间为: 00: 05: 11.45            --可以看见这里还是提高了点时间。  
CPU 时间为: 00: 00: 16.64  

由于640行所占用空间已经超出了参数bindsize的默认值，因此rows自动修改为198行。这说明bindsize偏小，我们将bindsize改为10M（10*1024*1024=10485760）,同时将rows提高到5000行。

[sql] view plain copy
 print?
$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=5000 bindsize=10485760  

截取sqlldr.log日志文件最后一部分：

[sql] view plain copy
 print?
表 T1:  
  2156280 行 加载成功。  
  由于数据错误, 165870 行 没有加载。  
  由于所有 WHEN 子句失败, 0 行 没有加载。  
  由于所有字段都为空的, 0 行 没有加载。  
  
  
为绑定数组分配的空间:               6450000 字节 (5000 行)     --可以看见这里的bindsize才使用了6M左右的空间。  
读取   缓冲区字节数:10485760  
  
跳过的逻辑记录总数:          0  
读取的逻辑记录总数:       2322150  
拒绝的逻辑记录总数:     165870  
废弃的逻辑记录总数:        0  
  
从 星期三 3月  14 20:36:25 2012 开始运行  
在 星期三 3月  14 20:46:51 2012 处运行结束  
  
经过时间为: 00: 04: 43.84         --看这里，时间又提高了。  
CPU 时间为: 00: 00: 12.27  

6、能不能再快一点呢

前面的测试都是基于常规路径加载，下面使用直接路径加载，所有的参数都默认：

[sql] view plain copy
 print?
$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true  

截取sqlldr.log日志文件最后一部分：

[cpp] view plain copy
 print?
表 T1:  
  2156280 行 加载成功。  
  由于数据错误, 165870 行 没有加载。  
  由于所有 WHEN 子句失败, 0 行 没有加载。  
  由于所有字段都为空的, 0 行 没有加载。  
  
  日期高速缓存:  
   最大大小:      1000  
   条目数:       926  
   命中数    :   2155354  
   未命中数  :         0  
  
在直接路径中没有使用绑定数组大小。  
列数组  行数:    5000  
流缓冲区字节数:  256000  
读取   缓冲区字节数: 1048576  
  
跳过的逻辑记录总数:          0  
读取的逻辑记录总数:       2322150  
拒绝的逻辑记录总数:     165870  
废弃的逻辑记录总数:        0  
由 SQL*Loader 主线程加载的流缓冲区总数:      500  
由 SQL*Loader 加载线程加载的流缓冲区总数:      334  
  
从 星期三 3月  14 21:12:45 2012 开始运行  
在 星期三 3月  14 21:16:05 2012 处运行结束  
  
经过时间为: 00: 02: 16.57                --可以看见这次时间提升还是比较多的。  
CPU 时间为: 00: 00: 10.11  

直接路径加载的参数也有不少，这里先设置参数，后面再详细介绍每个参数的含义。

[sql] view plain copy
 print?
$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true streamsize=10485760 date_cache=5000  

截取sqlldr.log日志文件最后一部分：

[sql] view plain copy
 print?
表 T1:  
  2156280 行 加载成功。  
  由于数据错误, 165870 行 没有加载。  
  由于所有 WHEN 子句失败, 0 行 没有加载。  
  由于所有字段都为空的, 0 行 没有加载。  
  
  日期高速缓存:  
   最大大小:      5000              --可以看见日日缓存是5000  
   条目数:       926                --从这里看日期的唯一性比较低，使用默认值1000也就够了。  
   命中数    :   2155354  
   未命中数  :         0  
  
在直接路径中没有使用绑定数组大小。  
列数组  行数:    5000  
流缓冲区字节数:10485760  
读取   缓冲区字节数: 1048576  
  
跳过的逻辑记录总数:          0  
读取的逻辑记录总数:       2322150  
拒绝的逻辑记录总数:     165870  
废弃的逻辑记录总数:        0  
由 SQL*Loader 主线程加载的流缓冲区总数:      500  
由 SQL*Loader 加载线程加载的流缓冲区总数:        0  
  
从 星期三 3月  14 21:20:36 2012 开始运行  
在 星期三 3月  14 21:22:22 2012 处运行结束  
  
经过时间为: 00: 01: 15.51                    --时间又提高了。  
CPU 时间为: 00: 00: 09.97  

其实影响性能的因素还有很多，下面列举一些还能降低性能的因素：

a、如果控制文件中全部指定数据类型，并且改为定长格式，导入效率也能提升一些。

b、再比如说表上面有索引，把索引禁用掉，效率又可以提升。

c、还有就是表改为nologging也可以提升效率。

0 0