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

1、生成百万级数据文件

[sql] view plain copy print ?

1. [oracle@linux sqlldr]$ pwd  
2. /u01/sqlldr  
3. [oracle@linux sqlldr]$ ll  
4. 总计 4  
5. -rw-r--r-- 1 oracle oinstall 228 03-14 18:55 getdata.sql  
6. [oracle@linux sqlldr]$ cat getdata.sql                          --创建一个sql脚本。  
7. SELECT A.OWNER || ',"' || A.OBJECT_NAME || '",' || A.OBJECT_ID || ',' ||  
8.        TO_CHAR(A.CREATED, 'yyyy-mm-dd hh24:mi:ss') || ',' || A.STATUS  
9.   FROM DBA_OBJECTS A,  
10.        (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 30) B;  
11. [oracle@linux sqlldr]$ sqlplus /nolog  
12.   
13. SQL*Plus: Release 11.2.0.1.0 Production on 星期三 3月 14 18:55:27 2012  
14.   
15. Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
16.   
17. SQL> conn /as sysdba  
18. 已连接。  
19. SQL> select count(*) from dba_objects;  
20.   
21.   COUNT(*)  
22. ----------  
23.      71876  
24.   
25. SQL> set echo off  
26. SQL> set term off  
27. SQL> set feedback off  
28. SQL> set heading off  
29. SQL> spool /u01/sqlldr/data.dat  
30. SQL> @/u01/sqlldr/getdata.sql  
31. SQL> spool off  
32. SQL> set heading on  
33. SQL> set feedback on  
34. SQL> set term on  
35. SQL> set echo on  

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

[sql] view plain copy print ?

1. [oracle@linux sqlldr]$ ll  
2. 总计 170904  
3. -rw-r--r-- 1 oracle oinstall 174824593 03-14 18:58 data.dat  
4. -rw-r--r-- 1 oracle oinstall       228 03-14 18:55 getdata.sql  
5. [oracle@linux sqlldr]$ wc -l data.dat   
6. 2322150 data.dat  

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

2、初始化环境

[sql] view plain copy print ?

1. SQL> conn u1/u1  
2. 已连接。  
3. SQL> create table t1(  
4.   2  owner varchar2(255),  
5.   3  object_name varchar2(255),  
6.   4  object_id number,  
7.   5  status varchar2(255),  
8.   6  created date);  
9.   
10. 表已创建。  
11.   
12. SQL> create index ind_obj_owner_name on t1(owner,object_name);  
13.   
14. 索引已创建。  

3、创建控制文件

[sql] view plain copy print ?

1. [oracle@linux sqlldr]$ cat sqlldr.ctl   
2. LOAD DATA    
3. INFILE data.dat    
4. TRUNCATE INTO TABLE T1    
5. FIELDS TERMINATED BY ','OPTIONALLY ENCLOSED BY '"'    
6. (owner,    
7.  object_name,  
8.  object_id,  
9.  created date 'yyyy-mm-dd hh24:mi:ss',  
10.  status)  

4、执行导入

[sql] view plain copy print ?

1. $ sqlldr u1/u1 control=sqlldr.ctl errors=999999  

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

[sql] view plain copy print ?

1. 表 T1:  
2.   2156280 行 加载成功。  
3.   由于数据错误, 165870 行 没有加载。  
4.   由于所有 WHEN 子句失败, 0 行 没有加载。  
5.   由于所有字段都为空的, 0 行 没有加载。  
6.   
7.   
8. 为绑定数组分配的空间:                 82560 字节 (64 行)    --rows是64，使用了82560字节的bindsize。  
9. 读取   缓冲区字节数: 1048576  
10.   
11. 跳过的逻辑记录总数:          0  
12. 读取的逻辑记录总数:       2322150  
13. 拒绝的逻辑记录总数:     165870  
14. 废弃的逻辑记录总数:        0  
15.   
16. 从 星期三 3月  14 20:02:25 2012 开始运行  
17. 在 星期三 3月  14 20:13:05 2012 处运行结束  
18.   
19. 经过时间为: 00: 06: 44.54  
20. CPU 时间为: 00: 00: 38.14  

5、能不能快一点呢

[sql] view plain copy print ?

1. $ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=640  

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

[sql] view plain copy print ?

1. 表 T1:  
2.   2156280 行 加载成功。  
3.   由于数据错误, 165870 行 没有加载。  
4.   由于所有 WHEN 子句失败, 0 行 没有加载。  
5.   由于所有字段都为空的, 0 行 没有加载。  
6.   
7.   
8. 为绑定数组分配的空间:                255420 字节 (198 行)  
9. 读取   缓冲区字节数: 1048576  
10.   
11. 跳过的逻辑记录总数:          0  
12. 读取的逻辑记录总数:       2322150  
13. 拒绝的逻辑记录总数:     165870  
14. 废弃的逻辑记录总数:        0  
15.   
16. 从 星期三 3月  14 20:20:53 2012 开始运行  
17. 在 星期三 3月  14 20:29:42 2012 处运行结束  
18.   
19. 经过时间为: 00: 05: 11.45            --可以看见这里还是提高了点时间。  
20. CPU 时间为: 00: 00: 16.64  

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

[sql] view plain copy print ?

1. $ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=5000 bindsize=10485760  

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

[sql] view plain copy print ?

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

6、能不能再快一点呢

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

[sql] view plain copy print ?

1. $ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true  

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

[cpp] view plain copy print ?

1. 表 T1:  
2.   2156280 行 加载成功。  
3.   由于数据错误, 165870 行 没有加载。  
4.   由于所有 WHEN 子句失败, 0 行 没有加载。  
5.   由于所有字段都为空的, 0 行 没有加载。  
6.   
7.   日期高速缓存:  
8.    最大大小:      1000  
9.    条目数:       926  
10.    命中数    :   2155354  
11.    未命中数  :         0  
12.   
13. 在直接路径中没有使用绑定数组大小。  
14. 列数组  行数:    5000  
15. 流缓冲区字节数:  256000  
16. 读取   缓冲区字节数: 1048576  
17.   
18. 跳过的逻辑记录总数:          0  
19. 读取的逻辑记录总数:       2322150  
20. 拒绝的逻辑记录总数:     165870  
21. 废弃的逻辑记录总数:        0  
22. 由 SQL*Loader 主线程加载的流缓冲区总数:      500  
23. 由 SQL*Loader 加载线程加载的流缓冲区总数:      334  
24.   
25. 从 星期三 3月  14 21:12:45 2012 开始运行  
26. 在 星期三 3月  14 21:16:05 2012 处运行结束  
27.   
28. 经过时间为: 00: 02: 16.57                --可以看见这次时间提升还是比较多的。  
29. CPU 时间为: 00: 00: 10.11  

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

[sql] view plain copy print ?

1. $ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true streamsize=10485760 date_cache=5000  

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

[sql] view plain copy print ?

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

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

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

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

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