I have the need the export BLOBs from our Oracle 11g (11.2.0.3) system. The process works great for the BLOBs (JPG photos) that are < 32,767 bytes. I'm able to export ~4000 photos in under 5 seconds to a local directory on the database server using dbms_log.read & utl_file.put_raw. If the file is over the 32,767 byte limit for the read buffer, this is where the performance issues start. I've seen similar posts about the exact performance issue, but the solutions offered have already been researched without success. Based on monitoring tools, the CPU, I/O, and memory are not being pressured during the export. I'm trying to understand why the larger BLOBs (all of them are under 100K in size) that have to be pieced together at 32,767 byte increments are having such a huge export slowness compared to BLOBs under 32,767 bytes. When the large BLOBs are exported they can take up to 15 seconds per file to export.
Related Post of slow blob extraction
Related Post of BLOB export tuning
Has anyone experienced BLOB export slowness with files greater than 32,767 bytes?
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;
Thanks in advance for any insight into BLOB exporting.
You need to reset l_amount also.
l_amount := 32767;
the second parameter of dbms_lob.read is an IN OUT parameter.
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);
If you are unlucky it will be just 1 byte left to read and then you step through the big next blob byte by byte:
amount
Number of bytes (for BLOBs) or characters (for CLOBs) to read, or number that were read.
I have done the whole homework and could reproduce the slow performance. I created some testdata as described here: Prepare test data on Oracle with blob column
Then I tried it with my own test program:
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;