I want to extract a lot of mostly fairly small blobs out into the file system. Around 800 000. I was intending to use utl_file to write out the blob to the file system.
However I've had significant performance issues. Even before I get to writing to the file system performance is slow. Just reading in the blobs using dbms_lob.read in chunks of 32767 (the max size the utl_file.put_raw can take) takes a l-o-n-g time. I end up writing the files out at 1-2 kb a second. At this rate it would take several months to export all the blobs.
Anyone got any ideas how I could speed this up? It doesn't have to be very quick but 100x faster then 1kb.sec would be nice!
My script is basically:
for c1 in
(some l-o-n-g SQL statement)
loop
l_blob_len := dbms_lob.getlength(c1.blob);
-- Open the destination file.
l_file := utl_file.fopen('C_ORA01',c1.c_name||c1.a_name'.'||c1.f_ext,'wb', 32767);
l_pos:=1;
while l_pos < l_blob_len loop
dbms_lob.read(c1.blob, l_amount, l_pos, l_buffer);
utl_file.put_raw(l_file, l_buffer, true);
l_pos := l_pos + l_amount;
end loop;
-- Close the file
utl_file.fclose(l_file);
end loop;