如何优化向子分区的Direct-Path Insert操作？

我们采用Direct-Path方式填充某数据集市，指定了插入的具体子分区，SQL语句如下：

INSERT /*+ APPEND_VALUES */ INTO DWH.DM_CORE_AC_SALDOS SUBPARTITION(SYS_SUBP7709) VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ) 

整个流程由dbms_parallel_execute控制，并行度设为40，即40个独立会话分别向不同子分区插入数据。但该流程执行耗时过长，会话窗口显示无会话锁冲突，但会话处于User I/O等待类的WAITING状态，几乎所有会话的等待事件为db file sequential read。查询dba_data_files的file#后发现，多数文件属于索引表空间。已知Direct-Path Insert会在插入末尾进行索引维护，推测性能瓶颈可能来自索引维护操作。

请问：

• 是否存在操作失误？
• 是否需要调整数据库参数或修改表结构（如initrans、pctfree）？
• 这种情况下是否应避免使用Direct-Path Insert？

1. 操作失误判断 #

当前操作的核心问题在于Direct-Path Insert并行插入与索引维护的冲突：

• 使用APPEND_VALUES提示走Direct-Path Insert时，若目标表存在索引，不会像常规插入那样实时维护索引，而是在插入阶段结束后批量维护索引。40个并行会话同时完成数据插入后，会集中触发索引批量维护，引发大量db file sequential read（索引块随机读），进而导致严重的I/O等待，这就是性能瓶颈的根源。
• 并行度40的设置是否合理需要结合服务器CPU、I/O能力评估，若服务器资源（尤其是I/O）不足以支撑40个会话并发进行索引维护，会进一步加剧等待。

2. 参数与表结构调整建议 #

表结构调整 #

• 临时禁用并重建索引：插入前对目标表所有索引执行ALTER INDEX ... UNUSABLE，插入完成后再执行ALTER INDEX ... REBUILD PARALLEL。这种方式能完全规避Direct-Path Insert时的索引维护开销，且并行重建索引的效率远高于插入时的批量维护。
• 无需调整PCTFREE：Direct-Path Insert直接写入高水位线以下的新块，PCTFREE对该操作无效，修改此参数无帮助。
• 无需调整INITRANS：当前会话是向不同子分区插入，不存在同分区的并发写入竞争，调整INITRANS无法解决索引维护的I/O瓶颈。

数据库参数调整 #

• 降低并行度：若服务器I/O资源有限，可适当降低dbms_parallel_execute的并行度（比如降至20以内），减少并发索引维护带来的I/O压力。
• 优化I/O相关参数：若存储支持，可调整db_file_multiblock_read_count优化批量读性能，但该参数对db file sequential read（随机读）优化有限；同时可检查磁盘I/O调度策略，确保存储系统能高效处理随机读请求。

3. 是否要避免Direct-Path Insert？ #

不需要完全避免。Direct-Path Insert在批量数据加载时的优势依然明显（绕过缓冲区、直接写入数据文件，数据插入阶段效率极高）。问题的核心并非Direct-Path Insert本身，而是未妥善处理索引维护环节。

正确的做法是结合「索引禁用-数据插入-并行重建索引」的流程，让Direct-Path Insert负责高效写入数据，再通过并行重建索引完成维护，能整体提升数据加载效率。

内容的提问来源于stack exchange，提问作者Sherzodbek