PL/SQL快速导出大表全部BLOB字段到文件系统的高效方法咨询

你当前方案慢、并行Hint无效的核心原因如下：

• PL/SQL存储过程是单会话串行执行的，你加的/*+ FULL PARALLEL(10) */只是让扫表查询环节并行，查询返回的所有行还是要排队在单个会话里逐条处理写文件，瓶颈卡在串行写入环节，所以并行Hint无法提升整体性能
• UTL_FILE单次put_raw最大支持写入32767字节，如果BLOB读取的缓冲区设置过小，会导致函数调用次数过多，额外增加上下文切换开销
• 2.5亿个小文件的写入带来极高的文件系统元数据开销：包括inode分配、目录项查找等，机械盘下随机写性能会被严重压榨

按优先级从高到低执行，可将耗时从天级降至小时级：

1. 替换单线程逻辑为并行多任务导出 #

这是收益最高的优化点，直接把并行能力覆盖到查询+写入全链路：

• 优先用Oracle自带的DBMS_PARALLEL_EXECUTE包实现自动分片并行，不需要自己写调度逻辑，示例代码如下：

DECLARE
  -- 自定义单分片导出逻辑
  PROCEDURE export_chunk(p_start_id NUMBER, p_end_id NUMBER) IS
    v_buffer RAW(32767);
    v_amount BINARY_INTEGER := 32767;
    v_offset NUMBER;
    v_file UTL_FILE.FILE_TYPE;
  BEGIN
    FOR rec IN (SELECT ID, FILENAME, OFFSET_BLOB FROM 你的表名 WHERE ID BETWEEN p_start_id AND p_end_id) LOOP
      -- 避免FILENAME重复导致覆盖，可加ID后缀区分
      v_file := UTL_FILE.FOPEN('你的目录对象', rec.FILENAME || '_' || rec.ID || '.offsets', 'wb', 32767);
      v_offset := 1;
      LOOP
        DBMS_LOB.READ(rec.OFFSET_BLOB, v_amount, v_offset, v_buffer);
        UTL_FILE.PUT_RAW(v_file, v_buffer, TRUE);
        v_offset := v_offset + v_amount;
      END LOOP;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        IF UTL_FILE.IS_OPEN(v_file) THEN
          UTL_FILE.FCLOSE(v_file);
        END IF;
    END LOOP;
  END;
BEGIN
  -- 创建并行任务
  DBMS_PARALLEL_EXECUTE.CREATE_TASK(task_name => 'BLOB_EXPORT_TASK');
  -- 按主键ID分片，每个分片处理100万行
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(
    task_name => 'BLOB_EXPORT_TASK',
    table_owner => '你的表所有者用户名',
    table_name => '你的表名',
    table_column => 'ID',
    chunk_size => 1000000
  );
  -- 启动并行执行，并行度根据CPU、磁盘带宽调整，普通SSD建议设为16~32
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name => 'BLOB_EXPORT_TASK',
    sql_stmt => 'BEGIN export_chunk(:start_id, :end_id); END;',
    language_flag => DBMS_SQL.NATIVE,
    parallel_level => 16
  );
  -- 任务完成后清理
  DBMS_PARALLEL_EXECUTE.DROP_TASK(task_name => 'BLOB_EXPORT_TASK');
END;
/

• 如果不使用内置包，也可以手动按ID拆分分片，用shell脚本启动多个独立的sqlplus后台进程分别处理不同分片，效果一致。

2. 优化文件IO开销 #

• 把导出目录放在SSD/NVMe盘上，不要用机械盘，小文件随机写性能差距可达上百倍
• 若必须保留单个BLOB对应一个文件的规则，把文件分散到多个子目录存储，比如按ID前两位拆分100个子目录，避免单个目录下文件过多导致的目录项查找性能暴跌
• 临时关闭导出目录所在文件系统的atime更新，减少不必要的元数据写入开销

3. 可选极致优化（性能可再提升10倍以上） #

如果业务允许调整导出文件的组织规则，取消单BLOB单文件的要求：
按分片生成聚合大文件，额外生成一个索引文件记录每个FILENAME对应的在聚合文件中的偏移量、长度，后续读取时直接按偏移量从大文件中读取即可，把2.5亿次小文件随机写变成数百次大文件顺序写，可最大化利用磁盘带宽。

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