Very slow extraction of BLOBs
Hello
Im trying to extract some blob objects from a table in my database (Oracle 10.2.0.1.0) ( TNS for 32-bit Windows: Version 10.2.0.1.0 - Production).
The objects are not so huge, theire are approx from 100kb to 1000 kb, the files are .doc, .bmp and .pdf files.
And the thing is everytime I start the extract (see script below) the first 5-6 objects are retreived with a speed of 30-40kb/s. And then it starts to slow down for each file --> 10 kb/s --> 5 kb/s,and in the end it feels like 0.001 kb/s - extremly slow.
So I tried to add dbms_lock.sleep(x) after every extract of a file, cause I thought it could be the preformance of the server(it had huge I/O, low CPU, so could have been the HDD), but it didint help.
Anyone has any enhancement ideas or something that could be useful to know ?
DECLARE
l_file UTL_FILE.FILE_TYPE;
l_buffer RAW(32767);
-- If file is bigger then 32k then you have to read it in loops.
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob BLOB;
l_blob_len INTEGER;
l_filename varchar2(240);
CURSOR GET_BLOB IS
SELECT VSP_05_COMPRESSED_F,VSP_05_SATTACH_BLOB,DET_01_NATTACH_NUM,DET_01_STITLE,DET_01_APP_FILE_NAME,FILENAME
FROM DELTA.DELTA_DOC;
BEGIN
-- Get LOB locator
FOR READ_BLOB IN GET_BLOB LOOP
l_blob := READ_BLOB.VSP_05_SATTACH_BLOB;
l_filename := READ_BLOB.FILENAME;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('BLOB_DIR',l_filename,'wb', 32767);
SYS.DBMS_LOCK.SLEEP(2);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
-- DBMS_OUTPUT.PUT_LINE (l_filename || ' reading in chunks');
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;
SYS.DBMS_LOCK.SLEEP(0.1);
END LOOP;
l_pos := 1;
-- Close the file.
UTL_FILE.fclose(l_file);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
UTL_FILE.fclose(l_file);
END IF;
RAISE;
END;