Hi,
I am experiencing odd performance problems with a PL/SQL procedure that loads files from an OS directory into a BLOB column in a database table.
My system is Oracle 11gR2 on a Solaris 10 machine.
My PL/SQL connects to a Oracle Directory, and for each file in the directory, uses LOADFROMFILE() to read the binary file contents into a BLOB... then there is an INSERT statement that inserts the BLOB into the table. My procedure does a COMMIT for every 1000 records.
Everything works great as long as I don't load more than 20,000 files (or so) at a time. If I load 5,000, or 7,000, or 20,000 --- everything works as expected. My PL/SQL procedure can load 20,000 records in less than 2 minutes.
If I try to load 25,000 (or more) files -- I can see that all 25,000 records have been loaded into my table; furthermore, I can query the table and read the contents of the BLOB column... which tells me that the table load has completed OK. HOWEVER, my PL/SQL procedure still has not completed yet.... SQL*Plus sits there for 20 minutes or more, before it will finally return and say "PL/SQL procedure successfully completed".
I am wondering what the delay is about? Is Oracle doing something, behind the scenes, with the BLOB's? If so, I would expect to see this delay with 10,000 or 20,000 records too -- but I don't. Once I get over 25,000 (or so) is when this delay occurs. Furthermore, the delay appears to occur AFTER all the records are loaded... the delay is between the last "COMMIT" and when Oracle finally decides that the PL/SQL procedure is complete. Does Oracle PL/SQL have known issues when trying to read filenames/files from an Oracle Directory?
thanks in advance for any advice..!!!!