BULK COLLECT不加LIMIT会爆内存,因Oracle不会自动分批,而是全量加载导致PGA耗尽报ORA-04030;合理LIMIT值为1000–5000,需结合字段类型、索引提示及游标%NOTFOUND判断退出,且FORALL前须确保集合已初始化并判空。

为什么BULK COLLECT不加LIMIT会爆内存
Oracle 不会自动分批 —— BULK COLLECT INTO 要么全取,要么报 ORA-04030(PGA 内存耗尽)。一旦结果集超过 PGA_AGGREGATE_TARGET 单次分配上限(通常几 MB),进程直接挂掉。尤其在并发场景下,100 个会话各占 5MB,就是 500MB PGA 消耗,DBA 看到监控就拒批。
怎么设 LIMIT 值才合理
不是越大越好,实测 LIMIT 1000–5000 是多数场景的甜点区:
-
LIMIT > 10000时,FORALL可能触发隐式分片,带来额外硬解析开销 - 含
CLOB/VARCHAR2(4000)字段时,建议从1000起调,再逐步压测 - Oracle 19c 实测
LIMIT 500在吞吐与内存占用间最平衡(尤其配合FORALL) - 别写死
LIMIT 10000就完事——必须结合游标定义里的索引提示,比如/*+ INDEX(a idx_status_time) */
循环退出条件必须用 %NOTFOUND,不能只看 COUNT
常见错误是写 EXIT WHEN v_batch.COUNT = 0,这会导致最后一次 fetch 没取满但仍有数据被跳过。因为:
- 最后一次
FETCH ... LIMIT 1000只返回 3 行,v_batch.COUNT是 3 ≠ 0,但游标已空 - 正确做法:每次
FETCH后立刻检查cur%NOTFOUND,且要放在EXIT前 - 游标首次打开后
FETCH返回空集合,v_batch是空(COUNT = 0),但不会抛NO_DATA_FOUND,这点和普通SELECT INTO不同
FORALL 前必须确保集合非 NULL
如果源 SQL 因绑定变量值导致无结果,v_batch 是 NULL 而非空集合,直接 FORALL i IN 1..v_batch.COUNT 会报 ORA-06531:
- 初始化集合:声明时加
:= t_type(),例如l_data t_emp := t_emp() - FETCH 后加判空:
IF l_data.COUNT > 0 THEN FORALL i IN INDICES OF l_data ... - 避免混用
INDICES OF和VALUES OF:19c 不报错,但执行计划可能退化为多次表扫描
关键不在“用了没”,而在“取多少、怎么判、后续怎么交出去”。漏掉任一环,LIMIT 就只是个摆设。











