转载

Oracle数据加载之sqlldr工具的介绍

环境:

服务端:RHEL6.4 + Oracle 11.2.0.4

客户端:WIN10 + Oracle 11.2.0.1 client

sqlldr语法
sqlldr实验准备
sqlldr常规加载
sqlldr常规加载优化
sqlldr直接路径加载
sqlldr直接路径加载优化
References

1. sqlldr语法

用法: SQLLDR keyword=value [,keyword=value,...]  有效的关键字:      userid -- ORACLE 用户名/口令    control -- 控制文件名        log -- 日志文件名        bad -- 错误文件名       data -- 数据文件名    discard -- 废弃文件名 discardmax -- 允许废弃的文件的数目         (全部默认)       skip -- 要跳过的逻辑记录的数目  (默认 0)       load -- 要加载的逻辑记录的数目  (全部默认)     errors -- 允许的错误的数目         (默认 50)       rows -- 常规路径绑定数组中或直接路径保存数据间的行数                (默认: 常规路径 64, 所有直接路径)   bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)     silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)     direct -- 使用直接路径                     (默认 FALSE)    parfile -- 参数文件: 包含参数说明的文件的名称   parallel -- 执行并行加载                    (默认 FALSE)       file -- 要从以下对象中分配区的文件 skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE) skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE) commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)   readsize -- 读取缓冲区的大小               (默认 1048576) external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED) columnarrayrows -- 直接路径列数组的行数  (默认 5000) streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000) multithreading -- 在直接路径中使用多线程  resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE) resumable_name -- 有助于标识可恢复语句的文本字符串 resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200) date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000) no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)  PLEASE NOTE: 命令行参数可以由位置或关键字指定 。前者的例子是 'sqlldr scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo userid=scott/tiger'。位置指定参数的时间必须早于 但不可迟于由关键字指定的参数。例如, 允许 'sqlldr scott/tiger control=foo logfile=log', 但是 不允许 'sqlldr scott/tiger control=foo log', 即使 参数 'log' 的位置正确。

2. sqlldr实验准备

2.1 模拟构建导入的源文件

select count(*) from dba_objects;

getobject.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<=20) b;

call.sql

set echo off set term off set linesize 140 pagesize 0 set feedback off set heading off spool E:/jingyu/scripts/ldr_object.csv @E:/jingyu/scripts/getobject.sql spool off set heading on set feedback on set term on set echo on

SQL调用脚本生成源文件

@E:/jingyu/scripts/call.sql

我这里得到了一个234M大小的ldr_object.csv文件,最后一列有空行,可以考虑先数据清洗后再导入。

2.2 创建表

create table objects( owner varchar2(30), object_name varchar2(50), object_id number, status varchar2(10), created date );  create index idx_obj_owner_name on objects(owner, object_name);

2.3 控制文件ldr_object.ctl

load data infile "E:/jingyu/scripts/ldr_object.csv" truncate into table objects fields terminated by "," optionally enclosed by ' ' TRAILING NULLCOLS (owner, object_name, object_id, created "to_date(:created,'yyyy-mm-dd hh24:mi:ss')", status  "trim(:status)" )

这里没有处理源文件的行尾空格,所以用到了trim函数对最后一列进行处理,当然还是建议在加载前就进行源文件的格式处理。

3. sqlldr常规加载

sqlldr jingyu/jingyu@db1 control=E:/jingyu/scripts/ldr_object.ctl bad=E:/jingyu/scripts/ldr_object1.bad log=E:/jingyu/scripts/ldr_object1.log skip=0 errors=9999

对应log文件:

SQL*Loader: Release 11.2.0.1.0 - Production on 星期一 9月 21 10:58:39 2015  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  控制文件:      E:/jingyu/scripts/ldr_object.ctl 数据文件:      E:/jingyu/scripts/ldr_object.csv   错误文件:    E:/jingyu/scripts/ldr_object1.bad   废弃文件:    未作指定   (可废弃所有记录)  要加载的数: ALL 要跳过的数: 0 允许的错误: 9999 绑定数组: 64 行, 最大 256000 字节 继续:    未作指定 所用路径:       常规  表 OBJECTS,已加载从每个逻辑记录 插入选项对此表 TRUNCATE 生效 TRAILING NULLCOLS 选项生效     列名                        位置      长度  中止 包装数据类型 ------------------------------ ---------- ----- ---- ---- --------------------- OWNER                               FIRST     *   ,  O ( ) CHARACTER             OBJECT_NAME                          NEXT     *   ,  O ( ) CHARACTER             OBJECT_ID                            NEXT     *   ,  O ( ) CHARACTER             CREATED                              NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "to_date(:created,'yyyy-mm-dd hh24:mi:ss')" STATUS                               NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "trim(:status)"   表 OBJECTS:   1731340 行 加载成功。   由于数据错误, 0 行 没有加载。   由于所有 WHEN 子句失败, 0 行 没有加载。   由于所有字段都为空的, 0 行 没有加载。   为绑定数组分配的空间:                 82560 字节 (64 行) 读取   缓冲区字节数: 1048576  跳过的逻辑记录总数:          0 读取的逻辑记录总数:       1731340 拒绝的逻辑记录总数:          0 废弃的逻辑记录总数:        0  从 星期一 9月  21 10:58:39 2015 开始运行 在 星期一 9月  21 11:12:20 2015 处运行结束  经过时间为: 00: 13: 41.34 CPU 时间为: 00: 01: 51.13

13分41秒加载完成1731340记录数。

4. sqlldr常规加载优化

sqlldr jingyu/jingyu@db1 control=E:/jingyu/scripts/ldr_object.ctl bad=E:/jingyu/scripts/ldr_object2.bad log=E:/jingyu/scripts/ldr_object2.log skip=0 errors=9999 rows=5000 bindsize=20971520 readsize=20971520

对应log文件:

SQL*Loader: Release 11.2.0.1.0 - Production on 星期一 9月 21 11:17:26 2015  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  控制文件:      E:/jingyu/scripts/ldr_object.ctl 数据文件:      E:/jingyu/scripts/ldr_object.csv   错误文件:    E:/jingyu/scripts/ldr_object2.bad   废弃文件:    未作指定   (可废弃所有记录)  要加载的数: ALL 要跳过的数: 0 允许的错误: 9999 绑定数组: 5000 行, 最大 20971520 字节 继续:    未作指定 所用路径:       常规  表 OBJECTS,已加载从每个逻辑记录 插入选项对此表 TRUNCATE 生效 TRAILING NULLCOLS 选项生效     列名                        位置      长度  中止 包装数据类型 ------------------------------ ---------- ----- ---- ---- --------------------- OWNER                               FIRST     *   ,  O ( ) CHARACTER             OBJECT_NAME                          NEXT     *   ,  O ( ) CHARACTER             OBJECT_ID                            NEXT     *   ,  O ( ) CHARACTER             CREATED                              NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "to_date(:created,'yyyy-mm-dd hh24:mi:ss')" STATUS                               NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "trim(:status)"   表 OBJECTS:   1731340 行 加载成功。   由于数据错误, 0 行 没有加载。   由于所有 WHEN 子句失败, 0 行 没有加载。   由于所有字段都为空的, 0 行 没有加载。   为绑定数组分配的空间:               6450000 字节 (5000 行) 读取   缓冲区字节数:20971520  跳过的逻辑记录总数:          0 读取的逻辑记录总数:       1731340 拒绝的逻辑记录总数:          0 废弃的逻辑记录总数:        0  从 星期一 9月  21 11:17:26 2015 开始运行 在 星期一 9月  21 11:19:43 2015 处运行结束  经过时间为: 00: 02: 17.92 CPU 时间为: 00: 00: 35.25

加载速度从13分41秒提升到2分17秒。因为默认一次加载从64行改为5000行,同时增大了bindsize的值为20971520(20M),实际5000行使用了6M左右的空间,所以表数据量大的情况下,还可以继续加大rows的参数值,具体效率提升情况还是需要具体的测试才可以最终选择合适的值。

5. sqlldr直接路径加载

sqlldr jingyu/jingyu@db1 control=E:/jingyu/scripts/ldr_object.ctl bad=E:/jingyu/scripts/ldr_object3.bad log=E:/jingyu/scripts/ldr_object3.log skip=0 errors=9999 rows=10000 direct=true

对应log文件:

SQL*Loader: Release 11.2.0.1.0 - Production on 星期一 9月 21 11:33:10 2015  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  控制文件:      E:/jingyu/scripts/ldr_object.ctl 数据文件:      E:/jingyu/scripts/ldr_object.csv   错误文件:    E:/jingyu/scripts/ldr_object3.bad   废弃文件:    未作指定   (可废弃所有记录)  要加载的数: ALL 要跳过的数: 0 允许的错误: 9999 继续:    未作指定 所用路径:       直接  表 OBJECTS,已加载从每个逻辑记录 插入选项对此表 TRUNCATE 生效 TRAILING NULLCOLS 选项生效     列名                        位置      长度  中止 包装数据类型 ------------------------------ ---------- ----- ---- ---- --------------------- OWNER                               FIRST     *   ,  O ( ) CHARACTER             OBJECT_NAME                          NEXT     *   ,  O ( ) CHARACTER             OBJECT_ID                            NEXT     *   ,  O ( ) CHARACTER             CREATED                              NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "to_date(:created,'yyyy-mm-dd hh24:mi:ss')" STATUS                               NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "trim(:status)"  表 OBJECTS 的以下索引已处理: 索引 JINGYU.IDX_OBJ_OWNER_NAME 已成功加载, 具有 1731340 个关键字  表 OBJECTS:   1731340 行 加载成功。   由于数据错误, 0 行 没有加载。   由于所有 WHEN 子句失败, 0 行 没有加载。   由于所有字段都为空的, 0 行 没有加载。  在直接路径中没有使用绑定数组大小。 列数组  行数:    5000 流缓冲区字节数:  256000 读取   缓冲区字节数: 1048576  跳过的逻辑记录总数:          0 读取的逻辑记录总数:       1731340 拒绝的逻辑记录总数:          0 废弃的逻辑记录总数:        0 由 SQL*Loader 主线程加载的流缓冲区总数:      581 由 SQL*Loader 加载线程加载的流缓冲区总数:      669  从 星期一 9月  21 11:33:10 2015 开始运行 在 星期一 9月  21 11:35:24 2015 处运行结束  经过时间为: 00: 02: 13.92 CPU 时间为: 00: 00: 23.92

sqlldr直接路径加载速度提升到2分13秒。

注意:直接路径加载过程中,索引会变成unusable状态,加载完成后变为valid状态。

#直接路径加载过程中,查看索引状态为UNUSABLE。 SQL> select table_name, index_name, status from user_indexes where table_name='OBJECTS';   TABLE_NAME                     INDEX_NAME                     STATUS ------------------------------ ------------------------------ -------- OBJECTS                        IDX_OBJ_OWNER_NAME             UNUSABLE  #直接路径加载完成,查看索引状态为VALID。 SQL> select table_name, index_name, status from user_indexes where table_name='OBJECTS';   TABLE_NAME                     INDEX_NAME                     STATUS ------------------------------ ------------------------------ -------- OBJECTS                        IDX_OBJ_OWNER_NAME             VALID

6. sqlldr直接路径加载优化

针对streamsize,date_cache两个参数,参数具体含义见第一部分的帮助。

sqlldr jingyu/jingyu@db1 control=E:/jingyu/scripts/ldr_object.ctl bad=E:/jingyu/scripts/ldr_object4.bad log=E:/jingyu/scripts/ldr_object4.log skip=0 errors=9999 rows=10000 direct=true streamsize=10485760 date_cache=5000

对应log文件:

SQL*Loader: Release 11.2.0.1.0 - Production on 星期一 9月 21 11:58:35 2015  Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.  控制文件:      E:/jingyu/scripts/ldr_object.ctl 数据文件:      E:/jingyu/scripts/ldr_object.csv   错误文件:    E:/jingyu/scripts/ldr_object4.bad   废弃文件:    未作指定   (可废弃所有记录)  要加载的数: ALL 要跳过的数: 0 允许的错误: 9999 继续:    未作指定 所用路径:       直接  表 OBJECTS,已加载从每个逻辑记录 插入选项对此表 TRUNCATE 生效 TRAILING NULLCOLS 选项生效     列名                        位置      长度  中止 包装数据类型 ------------------------------ ---------- ----- ---- ---- --------------------- OWNER                               FIRST     *   ,  O ( ) CHARACTER             OBJECT_NAME                          NEXT     *   ,  O ( ) CHARACTER             OBJECT_ID                            NEXT     *   ,  O ( ) CHARACTER             CREATED                              NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "to_date(:created,'yyyy-mm-dd hh24:mi:ss')" STATUS                               NEXT     *   ,  O ( ) CHARACTER                 列的 SQL 串: "trim(:status)"  表 OBJECTS 的以下索引已处理: 索引 JINGYU.IDX_OBJ_OWNER_NAME 已成功加载, 具有 1731340 个关键字  表 OBJECTS:   1731340 行 加载成功。   由于数据错误, 0 行 没有加载。   由于所有 WHEN 子句失败, 0 行 没有加载。   由于所有字段都为空的, 0 行 没有加载。  在直接路径中没有使用绑定数组大小。 列数组  行数:    5000 流缓冲区字节数:10485760 读取   缓冲区字节数: 1048576  跳过的逻辑记录总数:          0 读取的逻辑记录总数:       1731340 拒绝的逻辑记录总数:          0 废弃的逻辑记录总数:        0 由 SQL*Loader 主线程加载的流缓冲区总数:      581 由 SQL*Loader 加载线程加载的流缓冲区总数:        0  从 星期一 9月  21 11:58:35 2015 开始运行 在 星期一 9月  21 12:00:42 2015 处运行结束  经过时间为: 00: 02: 07.63 CPU 时间为: 00: 00: 29.36

速度提升到2分7秒。