玉面飞龙的BLOG

使用批量装载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

避免了内存紧张