数据库使用SQL*Loader导入的并行误区，如何巧妙规避？

作者介绍

蒋健， 云趣网络科技联合创始人，11g OCM，多年Oracle设计、管理及实施经验，精通数据库优化，Oracle CBO及并行原理。云趣鹰眼监控核心设计和开发者，资深Python Web开发者。

本文中的SQL*Loader案例源于几年前数据库一体机PK测试的场景，场景比较特殊，在疯狂的PK中，方案不断迭代升级，使得案例有一定的趣味性。近期又碰到客户在弄 SQL* Lo ader 的导入，老案例整理下分享一波。

一、大数据量超宽表导入

该SQL*Loader测试场景如下：

将一个包含约数亿行数据的txt文件（不可切分文件），使用SQL*Loader导入到数据库中，表约有200+列，不能改数据库层面配置，统计上机操作到导入完成的时间计算成绩，数据量不对该场景直接计0分。

当时的最新款exadata x5测试该场景计时约40分钟（直接路径，Parallel，BINDSIZE等该优化的都优化了），这个场景我测出来约10min，远超所有竞争对手的成绩，那究竟是什么操作能有如此大性能提升呢？

1、并行误区

当时由于甲方测试方案中限制不能拆分文件，导致大家测试时没有开启并行，那么SQL*Loader是否一定要拆分文件后才能进行并行呢？当然不是，这个场景中出了第1版本的方案：

• wc -l统计数据行数；

• 配合skip + load 生成多条命令逻辑上 进行切分文件；

• 批量并行执行命令导入数据。

生成SQL*Loader的命令可以使用以下脚本，其中total line number取wc -l的结果，DOP为自定义并行度：

    set serveroutput on 
    set linesize 1000
    set pages 0
    declare
    total_line_number number;
    dop  number;
    skip  number;
    load  number;
    tail_of_mod  number;
    command varchar2(4000);
    directory varchar2(4000);
    begin
    total_line_number := 348104868;
    directory := '/home/oracle/adam';
    dop := 20;
    skip := 0;
    load := 0;
    tail_of_mod := mod(total_line_number,dop);
    load := trunc(total_line_number/dop);
    for i in 1..dop loop
    if i = dop then 
    load := load + tail_of_mod;
    end if;
    command := 'nohup sqlldr tester/tester control='||directory||'/load.ctl log='||directory||'/test'||i||'.log READSIZE=20000000 BINDSIZE=20000000 direct=true parallel=true  errors=99999 silent=errors,discards skip='||skip||'   load='||load ||' &' ;
    dbms_output.put_line(command);
    skip := skip+load;
    end loop;
    end;
    /

当时测试SQL*Loader场景时，故意最后一个测试，方案报上去，甲方就补充了一条规则，数据量不对该场景直接0分。

经历过后面的场景后发现，当时确实是运气还不错，改进版方案应对的坑当时都没踩到。这个场景中，表的列特别多，其实导入过程中瓶颈并不是IO，而是CPU，这也才使得逻辑切分的方法非常适合使用。

那么如果是IO为瓶颈呢？可能这种方式并不太合适。对于有高性能存储的环境，测试发现单进程压测可以达到峰值60%的IOPS，也就是多进程能带来的IO上的提升很有限，同时skip操作，其实会产生无用的读操作，同时也消耗IO资源，综上IO为瓶颈的导入采用这种方式可能大打折扣。

二、超大数据量导入

这个场景有趣的地方就在于，如果你没看上面的那波操作，导入会一帆风顺……场景描述起来很简单：SQL*Loader单表导入6T的文本文件，条件也一样不能拆分文件。

只是这次稍微有点不一样的就是，有环境测试，气氛没那么紧张。但按照老的方案上来，第一步就坑了。之前的场景中，wc统计那步大约3分钟就完成了。而这次wc搞了两个小时还没弄完，不得不感叹这个厕所上的时间有点长啊。

很想抽根烟，可惜我不会，只能老实的cancel掉再来改进方案。很快我发现其实可以这样。

1、改进方案

• 在wc统计总行数的过慢的时候，可采用估值方式。head -n 50000 xxxx.file > 1.txt；

• 大文件的字节/小文件的字节数 * 采样样数，可以近似估算出一个总数；

• 最后一个Job不用写load数，即为全部加载。

看上去这个方案还是不错，执行下来也还ok。

2、重大bug

只是跟甲方汇报的时候，发现了一个大问题，数据对不上！我反思了一下方案，没找到逻辑上的毛病，查了日志，也没问题，只能重新再导入一次，还是4294967295这个数值，比甲方提供的数据少几个亿。

作为Oracle的DBA，一个常识就是当你遇到不合理，解释不通的问题时候，MOS上的bug列表永远不会让你失望，印象中拿着4294967295这个幸运数字马上定位到了文档id(1161183.1) SQL*Loader Fails To Load More Than 4294967295 (2^32 – 1) rows From An External Data File 参考下图：

简单来说就是：每命令只能load约42亿数据,加上skip约65亿。12c后才修改这个bug，当时主流版本时11.2.0.4所以这个任务不拆分文件SQL*Loader应该是搞不定的。

三、总结

对于SQL*Loader导入场景中，skip + load实现并行的导入的方式对于CPU消耗大的导入（涉及很多的单行拆分）还是有适用场景的，但有对于IO密集型的导入，可能需要测试以及规避bug。当然如果没硬性限制的话，很多场景可以考虑直接用外部表。