oracle 加载百万级文本文件,优化sqlldr加载上100万条记录的数据！

1、生成百万级数据文件

[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多万跳记录了。

[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> 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、创建控制文件

[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、执行导入

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

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

表 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、能不能快一点呢

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

表 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行。

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 rows=5000 bindsize=10485760截取sqlldr.log日志文件最后一部分：

表 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、能不能再快一点呢

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

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true截取sqlldr.log日志文件最后一部分：

表 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直接路径加载的参数也有不少，这里先设置参数，后面再详细介绍每个参数的含义。

$ sqlldr u1/u1 control=sqlldr.ctl errors=999999 direct=true streamsize=10485760 date_cache=5000截取sqlldr.log日志文件最后一部分：

表 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也可以提升效率。