PL/SQL存储过程执行过慢求助：新增代码后插入效率骤降

看来你的批量插入速度掉得有点离谱啊——200万条按当前1小时4000条的速度，得跑500小时才能完成，这肯定不是正常状态。结合你提到的新增defaultAddress_value变量后性能下降的情况，我给你整理几个核心优化方向，都是PL/SQL批量操作里经过验证的高效手段：

1. 用BULK COLLECT + FORALL彻底替换逐行游标循环 #

这是提升PL/SQL批量操作性能的最关键一步。原来的逐行处理会频繁在PL/SQL引擎和SQL引擎之间切换上下文，这是性能杀手。改成批量收集+批量插入，能把上下文切换的开销降到最低，速度能提升几十甚至上百倍。

重构后的核心代码示例：

create or replace PROCEDURE LOAD_ADDRESS_PROCEDURE AS
    -- 定义与源查询匹配的记录类型和集合类型
    TYPE AddressRec IS RECORD (
        ADDRESS VARCHAR2(200),
        CITY VARCHAR2(100),
        STATE VARCHAR2(100),
        ZIP VARCHAR2(20),
        COUNTRY VARCHAR2(100),
        -- 补充你需要的其他字段
        se_col VARCHAR2(100) -- 替换成实际字段名
    );
    TYPE AddressTab IS TABLE OF AddressRec;
    v_addresses AddressTab;
    
    -- 调整批次大小，建议从5000开始测试（根据数据库性能调整）
    v_batch_size CONSTANT PLS_INTEGER := 5000;
    -- 提前初始化固定变量，避免循环内重复赋值/查询
    defaultAddress_value varchar2(1) := 'N'; -- 假设是固定值，按需修改
    createdby_value varchar2(50) := 'LOAD_PROCEDURE';

    -- 定义游标（保留你的源查询逻辑）
    CURSOR C1 IS 
        SELECT ADDRESS, CITY, STATE, ZIP, COUNTRY, se_col
        FROM your_source_table -- 替换成实际源表
        -- 补充你的过滤/关联条件
        WHERE ...;
BEGIN
    -- 如果defaultAddress_value需要从表查询，只查一次，不要放在循环里
    -- SELECT default_val INTO defaultAddress_value FROM config_table WHERE id = 1;

    OPEN C1;
    LOOP
        -- 批量收集源数据到集合中
        FETCH C1 BULK COLLECT INTO v_addresses LIMIT v_batch_size;
        EXIT WHEN v_addresses.COUNT = 0;

        -- 批量插入到目标表
        FORALL i IN 1..v_addresses.COUNT
            INSERT INTO target_address_table (
                ADDRESS, CITY, STATE, ZIP, COUNTRY,
                CREATEDBY, DEFAULT_ADDRESS, se_col
                -- 补充其他目标字段
            ) VALUES (
                v_addresses(i).ADDRESS, v_addresses(i).CITY,
                v_addresses(i).STATE, v_addresses(i).ZIP,
                v_addresses(i).COUNTRY, createdby_value,
                defaultAddress_value, v_addresses(i).se_col
                -- 对应其他字段值
            );

        -- 提交当前批次
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('已插入 ' || v_addresses.COUNT || ' 条记录');
    END LOOP;
    CLOSE C1;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('所有记录插入完成');
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('错误信息: ' || SQLERRM);
        RAISE;
END LOAD_ADDRESS_PROCEDURE;
/

2. 调整批次大小，减少提交频率 #

你原来每次插100条就提交，这太频繁了——每次提交都要写重做日志，IO开销极大。建议把批次大小调整到5000~10000条（具体根据服务器内存和PGA设置调整，避免内存溢出），这样能大幅减少提交次数，提升整体速度。

3. 优化defaultAddress_value的处理逻辑 #

你提到新增这个变量后性能下降，大概率是这个变量在循环里被重复赋值或查询了。如果它是固定值，直接在存储过程开头的声明部分赋值；如果需要从数据库查询，只查一次，不要放在循环里反复执行查询——循环里的单次小查询累积起来就是巨大的性能开销。

4. 临时禁用非必要的索引、触发器和约束 #

插入数据时，数据库需要为每条记录维护索引、执行行级触发器、校验外键约束，这些操作会严重拖慢批量插入的速度。如果业务允许，可以先临时禁用这些非必要的对象，插入完成后再恢复：

-- 禁用非主键索引
ALTER INDEX idx_target_address_city UNUSABLE;
-- 禁用行级触发器
ALTER TRIGGER trg_target_address_insert DISABLE;
-- 禁用外键约束（如果不影响数据一致性）
ALTER TABLE target_address_table DISABLE CONSTRAINT fk_address_user;

-- 插入完成后恢复
ALTER INDEX idx_target_address_city REBUILD;
ALTER TRIGGER trg_target_address_insert ENABLE;
ALTER TABLE target_address_table ENABLE CONSTRAINT fk_address_user;

5. 使用APPEND提示和并行插入加速 #

如果目标表是堆表，可以用INSERT /*+ APPEND */提示，直接把数据写到高水位线以上，避免常规插入的空间管理开销；如果数据库支持并行处理，加上PARALLEL(n)提示（n为CPU核心数的一半左右），利用多核心加速插入：

FORALL i IN 1..v_addresses.COUNT
    INSERT /*+ APPEND PARALLEL(4) */ INTO target_address_table (
        -- 字段列表
    ) VALUES (
        -- 值列表
    );

注意：APPEND模式下，插入的数据在提交前对其他会话不可见，且可能导致表的高水位线上升，后续可以用ALTER TABLE target_address_table MOVE整理空间。

6. 优化源数据查询的性能 #

如果游标C1的SELECT语句本身很慢，那插入速度也快不起来。检查源查询是否有合适的索引，是否做了不必要的关联、排序或过滤，优化源查询的执行计划——比如给源表的过滤字段加索引，避免全表扫描。

7. 调整PGA内存避免磁盘溢出 #

如果设置的批次大小较大，要确保PGA_AGGREGATE_TARGET足够大，避免批量数据溢出到临时表空间（这会导致性能骤降）。可以临时增大PGA：

ALTER SESSION SET PGA_AGGREGATE_TARGET = 2G; -- 根据服务器内存调整，比如内存16G的话可以设4G

最后提醒：所有优化操作先在测试环境验证，确认没有问题后再应用到生产环境，避免数据风险。

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