Oracle BLOB Extraction Very Slow

Question

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.

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'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.

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;