performance - 对于大于 32kb 的文件，BLOB 导出速度很慢

Question

我需要从我们的 Oracle 11g (11.2.0.3) 系统导出 BLOB。该过程适用于小于 32,767 字节的 BLOB(JPG 照片)。我能够使用 dbms_log.read 和 utl_file.put_raw 在 5 秒内将约 4000 张照片导出到数据库服务器上的本地目录。如果文件超过读取缓冲区的 32,767 字节限制，这就是性能问题开始的地方。我看过关于确切性能问题的类似帖子，但所提供的解决方案已经过研究但没有成功。根据监控工具，导出过程中CPU、I/O、内存没有压力。我试图理解为什么必须以 32,767 字节的增量将较大的 BLOB(它们的大小都小于 100K)与 32,767 字节以下的 BLOB 相比，导出速度如此之快。导出大型 BLOB 时，每个文件最多可能需要 15 秒才能导出。

Related Post of slow blob extraction

Related Post of BLOB export tuning

有没有人遇到过文件大于 32,767 字节的 BLOB 导出缓慢？

DECLARE

  CURSOR cur_photo IS
    select  substr(c.custnum, -7, length(c.custnum)) custnum,
            cp.cust_id,
            cp.photo 
    from customer c
    inner join customer_photo cp
      on c.cust_id = cp.cust_id
    inner join customer_def_grp_value cdv
      on c.cust_id = cdv.cust_id;

  select_sql varchar2(225);
  l_file      UTL_FILE.FILE_TYPE;
  l_buffer    RAW(32767);
  l_amount    PLS_INTEGER := 32767;
  l_pos       PLS_INTEGER := 1;
  l_blob      BLOB;
  l_blob_len  PLS_INTEGER;
  l_filename  varchar2(225);
  error_number varchar2(225);
  error_message varchar2(225);

BEGIN
  --dbms_output.put_line('Starting at: ' || to_char(systimestamp, 'DD-MON-YYYY HH:MI:SS.FF6'));
  --DBMS_OUTPUT.ENABLE (buffer_size => NULL); 
  FOR custphoto IN cur_photo LOOP
    --dbms_output.put_line('In the loop ' || custphoto.cust_id);

    select_sql := 'SELECT photo FROM customer_photo WHERE cust_id = :cust_id';
    --dbms_output.put_line('Statement: ' || select_sql);

    EXECUTE IMMEDIATE select_sql INTO l_blob using custphoto.cust_id;

    l_blob_len := DBMS_LOB.getlength(l_blob);
    --dbms_output.put_line('BLOB length: ' || l_blob_len);

    -- Set the filename
    l_filename := custphoto.custnum || '.jpg';
    --dbms_output.put_line('Filename: ' || l_filename);

    -- Open the destination file.
    l_file := UTL_FILE.fopen('jpeg', l_filename, 'wb', 32767);

    --dbms_output.put_line('Start Export at: ' || to_char(systimestamp, 'DD-MON-YYYY HH:MI:SS.FF6'));

    IF l_blob_len < 32767 then
      --dbms_output.put_line('BLOB < 32767 bytes');
      DBMS_LOB.read(l_blob, l_blob_len, l_pos, l_buffer);
      UTL_FILE.put_raw(l_file, l_buffer, TRUE);
    ELSE -- write in pieces
      --dbms_output.put_line('BLOB >= 32767 bytes');
      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;
    END IF;

    -- Close the file.
    UTL_FILE.fclose(l_file);

    -- Reset the pos for the next jpg file
    l_pos := 1;

  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    -- Close the file if something goes wrong.
    error_number := sqlcode; 
    error_message := substr(sqlerrm, 1, 100); 
    dbms_output.put_line('Error Number: ' || error_number); 
    dbms_output.put_line('Error Message: ' || error_message); 
    utl_file.fclose_all;
  RAISE;

END;

提前感谢您对 BLOB 导出的任何见解。

Answer 1

您还需要重置 l_amount。

l_amount := 32767;

dbms_lob.read 的第二个参数是一个IN OUT 参数。

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_lob.htm#i999170

DBMS_LOB.READ (
   lob_loc   IN             BLOB,
   amount    IN OUT  NOCOPY INTEGER,
   offset    IN             INTEGER,
   buffer    OUT            RAW);

如果你不走运，它只剩下 1 个字节可供读取，然后你将逐字节遍历下一个大 blob:

amount

Number of bytes (for BLOBs) or characters (for CLOBs) to read, or number that were read.

我已经完成了全部作业，可以重现缓慢的性能。我创建了一些测试数据，如下所述:Prepare test data on Oracle with blob column

然后我用自己的测试程序试了一下:

create or replace directory outdir as '/home/oracle/pngs';
set serveroutput on
declare
  l_file      utl_file.file_type;
  l_buffer    RAW(32767);
  l_amount    PLS_INTEGER := 32767;
  l_pos       PLS_INTEGER := 1;
  l_blob_len  PLS_INTEGER;
begin
  --l_amount := 1; -- this wrecked the performance
  for c in (select * from demo.blob_test) loop
    l_file := UTL_FILE.fopen('OUTDIR', 'blob'||c.id||'.png', 'wb', 32767);
    l_blob_len := DBMS_LOB.getlength(c.data);
    IF l_blob_len < 32767 then
      dbms_output.put_line(systimestamp||' BLOB < 32767 bytes');
      DBMS_LOB.read(c.data, l_blob_len, l_pos, l_buffer);
      UTL_FILE.put_raw(l_file, l_buffer, TRUE);
      dbms_output.put_line(systimestamp||' done');
    ELSE 
      dbms_output.put_line(systimestamp||' BLOB >= 32767 bytes len '||l_blob_len);
      WHILE l_pos < l_blob_len LOOP
        --dbms_output.put_line(systimestamp||' l_pos '||l_pos||' l_amount '||l_amount);
        DBMS_LOB.read(c.data, l_amount, l_pos, l_buffer);
        UTL_FILE.put_raw(l_file, l_buffer, TRUE);
        l_pos := l_pos + l_amount;
      END LOOP;
      dbms_output.put_line(systimestamp||' done');
    END IF;
    l_pos := 1;
    l_amount := 32767; -- this handled it
    utl_file.fclose(l_file);
  end loop;
end;