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

本文由 dbaplus 社群授权转载。

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

一、大数据量超宽表导入

该 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。当然如果没硬性限制的话，很多场景可以考虑直接用外部表。

作者介绍：

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

原文链接：

https://mp.weixin.qq.com/s?__biz=MzI4NTA1MDEwNg==&mid=2650779518&idx=2&sn=08c456ecb524111099bee96f28756db6&chksm=f3f91aebc48e93fd8c40705cf2b218ef9592e5ec9c0f65eefeeebc12d2a54ab81c135a7a4704&scene=27#wechat_redirect