sql - Oracle ORA-04030 即使在循环中使用绑定(bind)变量

Question

我必须使用 PL/SQL 从远程表中删除近 5 亿行。由于 UNDO 表空间无法处理该卷，因此正在以 1,000,000 的批量大小进行删除并提交。另外 - 为了减少硬解析，我使用以下语法使用绑定(bind)变量:

str := 'delete from table@link where id >= :x and id < :y';
execute immediate str using start_id, start_id+1000000

每次调用后，start_id 都会递增 1000000，直到 SQL%rowcount 返回 0(零)并且达到 end_id(已知)。

但是进程得到ORA-0430如下:

ORA-04030: out of process memory when trying to allocate 16408 bytes (QERHJ hash-joi,QERHJ Bit vector)

ORA-04030: out of process memory when trying to allocate 41888 bytes (kxs-heap-c,temporary memory)

请注意，我已经在使用绑定(bind)变量，因此在第一次执行后没有硬解析。

一件事可能是目标 ID 的范围。假设前几行按升序排列，则 ID 为

100,000,000,000
200,000,000,000
50,000,000,000,000,000
50,000,000,000,011,111

在第二次迭代中，从 200,000,000,000 到 200,000,100,000 的 ID 将被删除。

但由于此范围内没有 ID，因此需要将近 50,000,000,000 次迭代才能到达下一行(50,000,000,000,000,000/1000000 = 50,000,000,000)。

当然 - 我总是可以检查目标的 ID 并选择正确的范围(比默认值 100 万大得多)。

但进程内存不足不应该是这种情况。

添加代码:

remote.sql:远程执行:

create table test1
(
    id      number(38) primary key
); 
insert into test1 select level from dual connect by level < 1000000;
insert into test1 values ( 1000000000000 );
insert into test1 values ( 2000000000000 );
commit;
exec dbms_stats.gather_table_stats ( ownname => user, tabname => 'test1',           
     cascade => true, estimate_percent => 100 );
commit; 

本地.sql :

create or replace procedure batch_del
as
     l_min_val       integer;
     l_max_val       integer;
     l_cnt           integer;
     l_cnt_dst       integer;
     l_begin         integer;
     l_end           integer;
     l_str           varchar2(1000);
     l_tot_cnt       integer;
  pragma autonomous_transaction;
  begin
      l_tot_cnt := 0;
      l_str := ' select min(id), max(id), count(*) from test1@dst';
      execute immediate l_str into l_min_val, l_max_val, l_cnt_dst;
      dbms_output.put_line ( 'min: ' || l_min_val || ' max: ' || l_max_val
           || ' total : ' || l_cnt_dst );
      l_begin := l_min_val;
      while l_begin  < l_max_val
      loop
           begin
              l_end := l_begin + 100000;
              delete from test1@dst where id >= l_begin and id < l_end;
              l_cnt := SQL%ROWCOUNT;
              dbms_output.put_line ( 'Rows Processed : ' || l_cnt );
              l_tot_cnt := l_tot_cnt + l_cnt;
              dbms_output.put_line ( 'Rows Processed So Far : ' || l_tot_cnt );
              commit;
          exception
          when others then
               dbms_output.put_line    ( 'Error : ' || sqlcode );
     end;
 l_begin := l_begin + 100000;  
 end loop;
 dbms_output.put_line ( 'Total : ' || l_tot_cnt );
end;

**所有本地实现**

drop table test1;
create table test1
(
 id    number(38) primary key
);
insert into test1 select level from dual connect by level < 1000000;
insert into test1 values ( 1000000000000 );
insert into test1 values ( 2000000000000 );
commit;
exec dbms_stats.gather_table_stats ( ownname => user, tabname => 'test1', 
cascade => true, estimate_percent => 100 );
commit;
create or replace procedure batch_del
as
   l_min_val       integer;
   l_max_val       integer;
   l_cnt           integer;
   l_begin         integer;
   l_tot_cnt       integer;
 pragma autonomous_transaction;
 begin
  l_tot_cnt := 0;
  select min(id), max(id) into l_min_val, l_max_val from test1;
  l_begin := l_min_val;
  while l_begin  < l_max_val
  loop
    begin
     delete from test1 where id >= l_begin and id < l_begin + 10000;
     l_cnt := SQL%ROWCOUNT;
     dbms_output.put_line ( 'Rows Processed : ' || l_cnt );
     l_tot_cnt := l_tot_cnt + l_cnt;
     dbms_output.put_line ( 'Rows Processed So Far : ' || l_tot_cnt );
     commit;
   exception
     when others then
      dbms_output.put_line    ( 'Error : ' || sqlcode );
 end;
 l_begin := l_begin + 10000;
 end loop;
 dbms_output.put_line ( 'Total : ' || l_tot_cnt );
end;  

set timing on;
set serveroutput on size unli;
exec batch_del;

Answer 1

您正在您的程序中使用 DMS_Output:

dbms_output.put_line ( 'Rows Processed : ' || l_cnt );
....
dbms_output.put_line ( 'Rows Processed So Far : ' || l_tot_cnt );

上述每个调用都会产生一个大约 25 个字符长的字符串(~25 个字节)。

PUT_LINE 过程不会在控制台上打印“在线”消息，而是将所有消息放入内存缓冲区，请参阅文档中的注释:DBMS_OUTPUT

Note:
Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure.
....
....
Rules and Limits
The maximum line size is 32767 bytes.

The default buffer size is 20000 bytes. The minimum size is 2000 bytes and the maximum is unlimited.

你在问题​​中写道:

it will take almost 50,000,000,000 iterations

很容易估计存储DBMS_Output消息所需的内存大小，
只需:2条消息，每条25字节，50,000,000,000次迭代

2 * 25 * 50,000,000,000 = 2 500 000 000 000 bytes

您似乎需要大约 2500 GB(~2,5 TB)的内存来存储从您的过程中发出的所有消息。 PGA_AGGREGATE_TARGET = 1.5 gB 绝对太低了。

---

只需从您的代码中删除 DBMS_Output，没有人(任何人)能够从控制台读取 50~1000 亿条消息。
如果要监视该过程，请使用 DBMS_APPLICATION_INFO.SET_CLIENT_INFO Procedure ，您可以存储最多 64 个字符的消息，然后查询 V$SESSION View 以检索最后一条消息。