过程缓冲区溢出 [英] Procedure Buffer overflow

问题描述

我有以下过程，该过程将一列中的空值填满.如果我的数据集很少，则该程序可以正常工作.但是我所针对的数据大约有 30 亿条记录.仅在100万条记录上对该脚本进行测试就抛出了这些执行力.

I have following procedure which is filling up null values in a column. The procedure works fine if i have very small set of data. But data that i am targettign on is about 3 billions of record. Just having this script tested on 1 Million record threw these execptions.

ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "DBNAME.PRBACKFILLI", line 39
ORA-06512: at line 2

经过一番挖掘，我意识到

After having a little bit digging, I realized that DBMS_OUTPUT.PUT_LINE prints output at the end of the procedure. Now the thing is we want debugging info, what should we do?

CREATE OR REPLACE PROCEDURE PRBACKFILL (str_dest IN VARCHAR2)  AS 
  CURSOR cr_pst_ IS
    select id, seq from TABLE_ where ID is null;

  TYPE t_id_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  TYPE t_seq_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  a_id   t_id_array;
  a_seq  t_seq_array;
  i_bulk_limit  NUMBER := 1000;
BEGIN
  OPEN cr_pst_;
  LOOP
    FETCH cr_pst_
    BULK COLLECT INTO a_id, a_seq LIMIT i_bulk_limit;


    FOR i IN 1..a_id.count LOOP
      a_id(i) := Floor(a_seq(i)/10000000000000);
    END LOOP;

    FORALL i IN 1 .. a_id.count
      UPDATE TABLE_
      SET ID = a_id(i)
      WHERE SEQ = a_seq(i);

      COMMIT;
      DBMS_OUTPUT.PUT_LINE ('COMMITED '||i_bulk_limit||' records');
    EXIT WHEN cr_pst_%NOTFOUND;
  END LOOP; -- main cursor loop
  CLOSE cr_pst_;

  DBMS_OUTPUT.PUT_LINE ('Backfill completed gracefully!');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('No more records to process');
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('errno: '||TO_CHAR(SQLCODE)||' Msg: ' || SQLERRM);              
END PRBACKFILL;
.
/
sho err;

推荐答案

首先，通常不会使用 DBMS_OUTPUT 进行日志记录.通常，将数据写入日志表会更有意义，特别是如果您将日志记录过程定义为自主事务，以便您可以在过程运行时监视日志数据. DBMS_OUTPUT 仅在整个过程完成执行后才会显示，这通常是毫无意义的.

First off, you would normally not use DBMS_OUTPUT for logging. It would generally make far more sense to write the data to a log table particularly if your logging procedure was defined as an autonomous transaction so that you could monitor the log data while the procedure was running. DBMS_OUTPUT is only going to be displayed after the entire procedure has finished executing at which point it's generally somewhat pointless.

与第一点有关，依靠 DBMS_OUTPUT 向调用者指示存在某种异常是一种非常糟糕的做法.至少，您希望重新引发抛出的异常，以便获取错误堆栈以调试问题.

Related to that first point, relying on DBMS_OUTPUT to indicate to the caller that there has been some sort of exception is a very poor practice. At a minimum, you'd want to re-raise the exception that was thrown so that you'd get the error stack in order to debug the problem.

第二，当启用输出时，必须指定 DBMS_OUTPUT 可以写入的缓冲区的大小.看来您已经声明缓冲区为20,000字节，如果您只是

Second, when you enable output, you have to specify the size of the buffer that DBMS_OUTPUT can write to. It appears that you've declared the buffer to be 20,000 bytes which is the default if you simply

SQL> set serveroutput on;

您可以通过指定大小来更改它，但是最大大小不能超过1,000,000字节

You can change that by specifying a size but the maximum size is limited to 1,000,000 bytes

SQL> set serveroutput on size 1000000;

如果您打算以1000行的块来更新30亿行，那么缓冲区将太小了.您将使用当前代码生成60倍以上的数据量.如果您在客户端和服务器上都使用10.2，则应该能够分配一个无限的缓冲区

If you plan on updating 3 billion rows in 1000 row chunks, that's going to be far too small a buffer. You're going to generate more than 60 times that amount of data with your current code. If you are using 10.2 both on the client and on the server, you should be able to allocate an unlimited buffer

SQL> set serveroutput on size unlimited;

但这不是早期版本中的选项.

but that is not an option in earlier releases.

最后，您确定首先需要使用PL/SQL吗?看来您只需执行一个UPDATE便可以更有效地做到这一点

Finally, are you certain that you need to resort to PL/SQL in the first place? It appears that you could do this more efficiently by simply executing a single UPDATE

UPDATE table_
   SET id = floor( seq/ 10000000000000 )
 WHERE id is null;

与PL/SQL替代方法相比，它的代码少得多，易于阅读，并且效率更高.唯一的缺点是，您的UNDO表空间必须足够大以容纳所生成的UNDO，但是将单个列从NULL更新为非NULL数值不应产生那么多的UNDO.

That's much less code, much easier to read, and will be more efficient than the PL/SQL alternative. The only downside is that it requires that your UNDO tablespace be large enough to accommodate the UNDO that is generated but updating a single column from NULL to a non-NULL numeric value shouldn't generate that much UNDO.

这篇关于过程缓冲区溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！