Oracle Blob Extraction Very Slow

Question

I'm having performance issues when retrieving BLOBS from the oracle 10gR2 10.2.05 database that I am administering. I have about 400 files stored as BLOBS that I need to write to the file system. Below is my code. When I perform this procedure, the first 8 or so files are written in a few seconds, and from there things slow down exponentially, somewhere around 1 file every 40 seconds after the first 8. For me, it makes no sense why the first 8 files will be fast, but then everything slows down. I tried to run this as a stored procedure, changing UTL_FILE.fopen to "wb" (write a binary file), and also using the installed NFS file system so as not to interfere with the database. None of this affected. At this speed, it will take me 6 hours to extract 400 files,which average about 1.5 MB. Does anyone see something wrong with my code or know a better way to do this? By the way, I used this sample code found herehttp://www.oracle-base.com/articles/9i/ExportBlob9i.php as a starting point.

Thanks for any help!

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;

Answer 1

I am sure that you should not extract all BLOB arrays into an array at the beginning of the procedure. Since you read BLOB data and never close any trap locator, Oracle must store all this information in memory. I would suggest that this is a case of memory overflow.

Try this instead:

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;