Oracle BLOB提取非常慢 [英] Oracle BLOB Extraction Very Slow

问题描述

从我管理的oracle 10gR2 10.2.05数据库中提取BLOBS时，我遇到性能问题.我有大约400个存储为BLOBS的文件，需要将其写出到文件系统中.下面是我的代码.当我执行此过程时，最初的大约8个文件将在几秒钟内写入，然后从那里开始呈指数下降，在开始的8个文件后每40秒大约有1个文件.对我来说，这没有任何意义，为什么呢?前8个文件很快，但是之后一切都变慢了.我尝试将其作为存储过程运行，将UTL_FILE.fopen更改为"wb"(写二进制文件)，并且还使用了NFS挂载的文件系统，以免影响数据库的性能.这些都没有产生任何影响.以这种速度，我将花费6个小时来提取400个平均每个文件约1.5MB的文件.有人看到我的代码有什么问题吗，或者知道更好的方法吗?顺便说一下，我使用的示例代码在这里 http://www.oracle-base .com/articles/9i/ExportBlob9i.php 作为起点.

I am having performance issues when extracting BLOBS from an oracle 10gR2 10.2.05 database I administer. I have around 400 files stored as BLOBS that I need to write out to the file system. Below is my code. When I execute this procedure the first 8 or so files are written within a couple of seconds and from there things slow down exponentially, somewhere around 1 file every 40 seconds after the first 8. To me this doesn't make any sense, why would the first 8 files be fast but after that everything slows down. I have tried running this as a stored procedure, changing the UTL_FILE.fopen to "wb" (write binary), and also using a NFS mounted file system so as not to impede the performance of the database. None of this has had any impact. At this rate it is going to take me 6 hours to extract 400 files that average around 1.5MB each. Does anyone see anything wrong with my code or know of a better way to do this? By the way I used this example code found here http://www.oracle-base.com/articles/9i/ExportBlob9i.php as a starting point.

感谢您的帮助！

DECLARE
  TYPE comment_text IS TABLE OF documents.comment_text%TYPE;
  TYPE localdata IS TABLE OF documents.localdata%TYPE;
  l_file UTL_FILE.FILE_TYPE;
  l_buffer RAW(32767);
  l_amount BINARY_INTEGER := 32767;
  l_pos INTEGER := 1;
  l_blob localdata;
  l_fname comment_text;
  l_blob_len INTEGER;
  l_x NUMBER := 1;
BEGIN
  SELECT comment_text, localdata
  BULK COLLECT INTO l_fname, l_blob
  FROM documents
  WHERE user_id='BILLYBOB';
  IF SQL%ROWCOUNT =0 THEN
    DBMS_OUTPUT.PUT_LINE('No records found!');
  ELSE
    FOR i IN l_fname.FIRST .. l_fname.LAST
    LOOP
      l_blob_len := DBMS_LOB.getlength(l_blob(i));
      DBMS_OUTPUT.PUT_LINE(l_blob_len);
      l_file := UTL_FILE.fopen('BLOBS',l_x || '_' || l_fname(i),'w', 32767);
      l_pos := 1;
      l_x := l_x + 1;
      WHILE l_pos < l_blob_len
      LOOP
        DBMS_LOB.read(l_blob(i), l_amount, l_pos, l_buffer);
        UTL_FILE.put_raw(l_file, l_buffer, TRUE);
        l_pos := l_pos + l_amount;
      END LOOP;
      UTL_FILE.fclose(l_file);
    END LOOP;
  END IF;
END;

推荐答案

我很确定您不应该在过程开始时将所有BLOB都提取到一个数组中.由于您读取了BLOB数据并且从未真正关闭任何lob定位器，因此Oracle必须将所有这些信息保留在内存中.我想这是内存溢出的情况.

I'm pretty sure you shouldn't fetch all BLOBs into an array at the start of the procedure. Since you read the BLOB data and never actually close any lob locator, Oracle has to keep all this info in memory. I would guess this is a case of memory overfill.

尝试以下方法:

CURSOR cc IS (SELECT ...)
BEGIN
   OPEN cc;
   LOOP
      FETCH cc
         INTO l_fname, l_blob;
      EXIT WHEN cc%NOTFOUND;
      l_blob_len := DBMS_LOB.getlength(l_blob);
      DBMS_OUTPUT.PUT_LINE(l_blob_len);
      l_file := UTL_FILE.fopen('BLOBS', l_x || '_' || l_fname, 'w', 32767);
      l_pos  := 1;
      l_x    := l_x + 1;
      WHILE l_pos < l_blob_len LOOP
         DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
         UTL_FILE.put_raw(l_file, l_buffer, TRUE);
         l_pos := l_pos + l_amount;
      END LOOP;
      UTL_FILE.fclose(l_file);
   END LOOP;
   CLOSE cc;
END;

这篇关于Oracle BLOB提取非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！