为了优化而调整的参数带来了负面影响-sqlldr加载效率差问题分析

最近忙着培训的事，没怎么写文章了，实在有点对不住关注公众号的朋友。

今天说的这个案例发生在年初，某银行的一个数仓系统整体性能不佳，其中还有个奇怪的问题就是，两个结构比较类似的表，用sqlldr加载4000万左右的数据，一个需要1.5小时，另一个就要4.5小时，这对一个跑批业务来说影响是非常大的。客户自查了挺长时间也没找到原因。

sqlldr参数都是一样的: direct=y ,128 条记录commit一次； 加载的表都是分区表，通过dba_tables 比较过，没有什么不一样的设置。

通过采集数据加载过程的dba_hist_active_sess_history信息，发现了一些蛛丝马迹： 加载慢的sqlldr进程，enq: CF - contention和control file parallel write 两个等待事件比正常的表要高出很多。根据enq: CF - contention这个等待事件进行检索，找到如下解释和几个可能原因：

任何需要读取控制文件的动作期间都会产生CF队列，CF锁用于controlfile序列操作和共享部分controlfile读和写：

•发生检查点

•日志文件的切换

•归档online redolog

•运行崩溃后的恢复

•热备的开始和结束

•DML通过nologging选项执行对象时

看到最后一个可能原因的时候，客户突然想起了什么：表上好像在之前做优化的时候设置了nologging。到dba_tables字典一查，两个表的logging属性都为空（客户之前的比较也是通过dba_tables进行的）。因为是分区表，这个属性是在各分区上设置的，马上再到dba_tab_partitions字典查，确实是加载慢的表的各分区上的logging属性都是'NO'，而加载快的表，各分区的logging属性都是'YES'。看到了这个区别，问题的答案也就一目了然了。

马上做验证测试，确认无误。

总结与建议：

其实nologging的设置，本身也是没有问题的，问题还是在于sqlldr的设置，每128条记录commit一次的频率太高，如果把每次提交的记录数调高（比如调到100万记录commit一次），那就不会有这个问题了。我的建议就是不用改表上的nologging设置，而是增大每次commit的记录数。所有的sqlldr都修改这个参数，现有其他sqlldr的加载过程也会提速。

其实用sqlldr做数据加载我认为不是最好的选择，用外部表做数据加载是一个很好的选择，外部表从9i开始出现，到现在的19c，增加了很多的新特性：

1. 像普通表一样查询（如果需要加载的数据只参与少量查询，可以直接使用外部表，不用加载到数据库）
2. 支持压缩格式访问（对网络吞吐量是瓶颈的系统很有帮助）
3. 12.2开始支持外部表分区（表明oracle开始加大对大数据特性的支持）
4. 18c支持In-Memory和Inline（不需要提前create table，直接在SQL调用即可）
5. 19c支持外部表混合分区：一部分分区是普通分区，一部分分区是外部表分区

当然，加并行缩短大数据量加载时间的功能也是必不可少的，在跑批关键路径上适当增加并行，理所当然。

sqlldr，提高效率可能需要设置一些参数，不是数据加载的最佳选择。用好外部表，可以大大提升你的业务效率。

（完）