SQL*Loader导入百万级别数据

生成百万级的数据文件

连接到本地数据库(自己安装的):

找一个合适大小的表:


SELECT COUNT(0) FROM DBA_OBJECTS;
COUNT(0)

----------

65389


然后找一个有20行记录的表,对其进行笛卡尔运算,即可生成130万条记录了.

为了更好地体现通用性,我们在输出时对created日期做一下格式转换:



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 content by rownum<=20) b ;

示例代码保存为SQL文件:getobject.sql


set echo off
set term off
set line 1000 pages 0
set feedback off
set heading off
spool [当前路径]/ldr_object.csv
@[当前路径]/getobject.sql
spool off
set heading on
set feedback on
set term on
set echo on

示例代码保存为:SQL文件:call.sql

然后在SQL*Plus命令行环境中,执行下列命令即可:

SQL> @[当前路径]call.sql

然后就耐心等待把,132万的记录量,输出也是需要一定时间的.

查看:



exit
du -m ldr_object.csv

//输出的数据以M为单位.


初始化环境

创建表:


CREATE TABLE OBJECTS(
OWNER VARCHAR2(30),
OBJECT_NAME VARCHAR2(50),
OBJECT_ID NUMBER,
STATUS VARCHAR2(2000),
CREATED DATE);

创建Index.



create index idx_obj_owner_name on objects(owner,object_name);

执行导入

创建控制文件如下:



LOAD DATA
INFILE ldr_object.csv
INSERT INTO TABLE OBJECTS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(owner,
object_name ,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status char(2000)
)
BEGINDATA

控制文件保存为:ldr_object.ctl

这里注意,对于CREATED列,我们指定了日期格式,并进行了转换,这个格式一定要与数据文件中日期格式相符,不然日期格式转换时会报错并导致数据加载失败.

按照默认参数执行SQLLDR,看看需要多长时间,同时指定ERRORS参数值为10,明确指定出现10次错误即中止加载:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10

5.4.4 能不能再快一点呢

SQLLDR常规路径导入时默认一次加载64行,现在要加载的总行数已经达到百万级,十位数显然太小,我们首先尝试修改该值,先直接在后面加个0好了,看看能对效率起到多大的提升:

执行如下命令:sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=640

日志节选信息:



value used for ROWS parameter changed from 640 to 84
Table OBJECTS:
1307820 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 254856 bytes(84 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Run began on Wed Jun 05 14:18:43 2013

Run ended on Wed Jun 05 14:28:56 2013

Elapsed time was: 00:10:12.39

CPU time was:00:00:16.40


注意节选信息的第一行,该信息是提示由于640行所占用的空间已经超过了参数BINDSIZE的默认值,因此自动修改到最大可承受的84行,这说明BINDSIZE参数默认值偏小.速度只比刚才小了2秒(变化几乎可以忽略)

再进一步调整BINDSIZE参数值,默认为256k,我们将其修改为10M(1024kb * 1024 * 10 = 10485760),同时将一次加载的行数提高到5000.



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl ERRORS=10 ROWS=5000 BINDSIZE=10485760

时间变得更长,正在研究:

大约时间是:13:48

Oracle LOGO
image-1838

能不能再快一点呢

所有参数默认,只打开直接路径加载:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE

靠,这个速度才是王道啊!



Table OBJECTS:

1307820 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Date conversion cache disabled due to overflow (default size: 1000)

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes: 256000

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 1250

Total stream buffers loaded by SQL*Loader load thread: 4996

Run began on Wed Jun 05 14:55:12 2013

Run ended on Wed Jun 05 14:56:28 2013

Elapsed time was: 00:01:15.74

CPU time was:00:00:11.80


有没有可能更快呢

这究竟是希望还是欲望,已经说不清楚了,反正没个尽头。

直接导入路径导入可用的参数也有不少,不过我们这里总数据量不大,因此实际能够起到效率提升的不多,我准备主要从以下两个参数入手:

  1. STREAMSIZE : 直接路径加载默认读取全部记录,因此不需要设置ROWS参数,读取到的数据处理后存入流缓存区,即STREAMSIZE参数,该参数默认值为256kb,这里加大到10MB.
  2. DATE_CACHE : 该参数指定一个转换后日期格式的缓存区,以条为单位,默认值1000条,即保存1000条转换后的日期格式,由于我们要导入的数据中有日期列,因此加载该参数值到5000,以降低日期转换带来的开销.

修改参数后执行命令最终形式如下所示:



sqlldr SCOTT/TIGER CONTROL=ldr_object.ctl DIRECT=TRUE STREAMSIZE=10485760 DATE_CACHE=5000

可能已经达到性能瓶颈:



Date cache:

Max Size: 5000

Entries : 1207

Hits : 1306613

Misses :0

Bind array size not used in direct path.

Column array rows : 5000

Stream buffer bytes:16777216

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 1307820

Total logical records rejected:0

Total logical records discarded: 0

Total stream buffers loaded by SQL*Loader main thread: 1250

Total stream buffers loaded by SQL*Loader load thread: 0

Run began on Wed Jun 05 15:15:39 2013

Run ended on Wed Jun 05 15:17:07 2013

Elapsed time was: 00:01:28.14

CPU time was:00:00:09.05

SQL*Loader加载综述

事实上想在Oracle存储过程中调用SQLLDR非常麻烦,因为SQLLDR是一个执行程序而不是一个接口,在9i之前版本要在存储过程中实现类似功能也很复杂,虽然可以通过UTL_FILE之类的包间接实现,但需要编写大量脚本,考虑字符截取,过滤,判断等诸多事宜。

一个不慎就可能造成执行报错,或者更不慎,执行到一般的时候报错(可能比没执行还要麻烦),幸运的是,9i及之后的版本,Oracle提供了一个新的功能---外部表(External Tables),顾名思义就是数据存储在数据库之外的表,这是一个号称"SQL*Loader替代者"的新特性.

2013/06/05 15:25:57

--EOF--

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据