使用批量装载SELECT <column_list> BULK COLLECT INTO <variable_list>的时候，Oracle会一次性的将结果集装载到PGA中再进行下步操作。如果结果集较大并且OS物理内存紧张的话，可能会导致ORA-4030错误和严重的SWAP.
这时候推荐使用limit
OWNER SEGMENT_NAME SEGMENT_TYPE size(M)
——- ————– ———- ——- ——- ——-
ORACLE BIGTABLE TABLE 45
测试表有45M
SQL> select PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where addr=(XXXX);
PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
———— ————- —————- ———–
297041 321889 0 321889
SQL> declare
2 type bigtable_a is table of bigtable.owner%type;
3 type bigtable_b is table of bigtable.object_name%type;
4 l_bc_a bigtable_a;
5 l_bc_b bigtable_b;
6 begin
7 select owner, object_name bulk collect into l_bc_a, l_bc_b from bigtable;
8 for i in l_bc_a.first .. l_bc_a.last loop
9 dbms_output.put_line(l_bc_a(i) || ‘, ‘ || l_bc_b(i));
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> /
PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
———— ————- —————- ———–
330921 69520017 0 69520017
导致 PGA_ALLOC_MEM增长69M左右。
使用 limit 限制每次取多少结果集，
SQL> select PGA_USED_MEM,PGA_ALLOC_MEM,PGA_FREEABLE_MEM,PGA_MAX_MEM from v$process where addr=(select paddr from v$session where sid=(select sid from v$Mystat where rownum=1));
PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
———— ————- —————- ———–
374073 382089 0 382089
SQL> declare
2 type bigtable_a is table of bigtable.owner%type;
3 type bigtable_b is table of bigtable.object_name%type;
4 l_bc_a bigtable_a;
5 l_bc_b bigtable_b;
6
7 cursor cr_rec is
8 select owner, object_name from bigtable;
9 begin
10 open cr_rec;
11 loop
12 fetch cr_rec bulk collect into l_bc_a, l_bc_b limit 200;
13 for i in l_bc_a.first .. l_bc_a.last loop
14 dbms_output.put_line(l_bc_a(i));
15 dbms_output.put_line(l_bc_b(i));
16 end loop;
17 exit when cr_rec%notfound;
18 end loop;
19 close cr_rec;
20 end;
21 /
PL/SQL procedure successfully completed.
SQL>/
PGA_USED_MEM PGA_ALLOC_MEM PGA_FREEABLE_MEM PGA_MAX_MEM
———— ————- —————- ———–
333625 757553 0 757553
避免了内存紧张
