9 Replies Latest reply: Oct 22, 2013 7:58 AM by Billy~Verreynne RSS

    Inserting BLOB's into table - odd problem

    KSandbergNGC

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

        • 1. Re: Inserting BLOB's into table - odd problem
          Mike Kutz

          CREATE TABLE statement would have been nice.

           

          more specifically:

          partitioning?

          securefiles?  compression? dedupe? encryption?

          triggers?

          Are you doing Oracle TEXT indexing at the end?

          etc.

          • 2. Re: Inserting BLOB's into table - odd problem
            KSandbergNGC

            The OS source directory is a Solaris 10 directory, mounted as an "external table" inside the DB.

            The target table was created with:

             

            /** I tried using both BasicFile and SecureFile BLOB's -- same behavior. **/

            create table myTargetTable (filename varchar2(100), fileSize number, fileContents  blob);

             

            The PL/SQL I got from Oracle's examples.. which I wrapped up into a LOOP to do many files:

             

            DECLARE

              /* OS_DIR is a Oracle Directory object pointing to my OS dir, ie "/tmp/datafiles" */

              cursor getFilenames is select FileName from OS_DIR_TABLE;

              strFilename  VARCHAR2(100);

              fhDataFile   BFILE;

              TempBLOB BLOB;

              vFileSize     NUMBER;

              nCommitCounter  NUMBER;

            BEGIN

              open GetFilenames;

              loop

               fetch GetFilenames into strFilename;

               exit when GetFilenames%notfound;

             

               DBMS_LOB.createTemporary(TempBLOB, true);

               DBMS_LOB.open(TempBLOB, DBMS_LOB.READWRITE);

               fhDataFile := BFILENAME('OS_DIR', strFilename);

               vFileSize := DBMS_LOB.getLength(fhDataFile);

               DBMS_LOB.open(fhDataFile, DBMS_LOB.READONLY);

               DBMS_LOB.LoadFromFile( TempBLOB, fhDataFile, DBMS_LOB.LOBMAXSIZE);

             

                insert into MyTargetTable (filename, fileSize, fileContents)

                  values ( strFileName,

                              vFileSize,

                              TempBLOB);

             

                DBMS_LOB.close(TempBLOB);

                DBMS_LOB.close(fhFileData);

             

                 nCommitCounter:=nCommitCounter+1;

                 if nCommitCounter = 1000 then

                     commit;

                     nCommitCounter:=0;

                end if; 

              end loop;

              close GetFilenames;

              commit;
            END;

             

            When I load 24,000 (or so) files... everything works great!  It can load 24,000 (or so) files in 2 minutes or less.  But when I get over 25,000 files - the files get loaded but the PL/SQL procedure never completes....  SQLPLus (and SQLDeveloper) just sit there blinking at me, I never get control back (I've tried waiting over an hour!).  I have to kill the session at the database.

             

             

             

             

             


            • 3. Re: Inserting BLOB's into table - odd problem
              KSandbergNGC

              I read thru Oracle docs on DBMS_LOB, and I think I stumbled across my solution.  I think I was creating too many Temporary BLOB's in Oracle memory or something, using the DBMS_LOB.CreateTemporary() procedure.

               

              Using the code sample above, if I make the code changes (highlighted below) the PL/SQL runs (and completes!) as expected.  Note that, instead of creating a new Temporary BLOB for every file -- I now create just one Temporary BLOB and reset it using the DBMS_LOB.trim() command.

               

              I have loaded 400,000 files into my DB in 28 minutes... roughly 14k files per minute.   At first glance, it appears the contents of the BLOB column in my table match the contents of the files on the OS.  I think I am good now.

               

               

              DECLARE

                /* OS_DIR is a Oracle Directory object pointing to my OS dir, ie "/tmp/datafiles" */

                cursor getFilenames is select FileName from OS_DIR_TABLE;

                strFilename  VARCHAR2(100);

                fhDataFile   BFILE;

                TempBLOB BLOB;

                vFileSize     NUMBER;

                nCommitCounter  NUMBER;

              BEGIN

               

                 DBMS_LOB.createTemporary(TempBLOB, true);

                 DBMS_LOB.open(TempBLOB, DBMS_LOB.READWRITE);

                 open GetFilenames;

                 loop

                  fetch GetFilenames into strFilename;

                  exit when GetFilenames%notfound;

                

                  fhDataFile := BFILENAME('OS_DIR', strFilename);

                  vFileSize := DBMS_LOB.getLength(fhDataFile);

                  DBMS_LOB.trim(TempBLOB,0);

                  DBMS_LOB.open(fhDataFile, DBMS_LOB.READONLY);

                  DBMS_LOB.LoadFromFile( TempBLOB, fhDataFile, DBMS_LOB.LOBMAXSIZE);

               

                  insert into MyTargetTable (filename, fileSize, fileContents)

                    values ( strFileName,

                                vFileSize,

                                TempBLOB);

               

                  DBMS_LOB.close(fhFileData);

                   nCommitCounter:=nCommitCounter+1;

                   if nCommitCounter = 1000 then

                       commit;

                       nCommitCounter:=0;

                  end if; 

                end loop;

                close GetFilenames;

              DBMS_LOB.close(TempBLOB);

                commit;
              END

              • 4. Re: Inserting BLOB's into table - odd problem
                Billy~Verreynne

                How about getting rid of that idiotic commit counter?

                • 5. Re: Inserting BLOB's into table - odd problem
                  BluShadow

                  Completely agree with Billy.

                  Committing every "N" rows is just stupid.  It doesn't improve anything.... but what it does do is...

                   

                  a) prevents transactional consistency (e.g. if a problem occurs, you cannot rollback)

                  b) creates more writer process threads on the server creating a greater chance of physical I/O contention, thus degrading performance, and using up server resources which are not freed up again until Oracle or the server is restarted.

                   

                  There's absolutely no need for periodic commits in code.  Get rid of it.

                  • 6. Re: Inserting BLOB's into table - odd problem
                    Billy~Verreynne

                    And to add to Blu's comments - if resource utilisation or management of the load process (by breaking it down as smaller processes), then DBMS_PARALLEL_EXECUTE should be used. Not incremental commits.

                     

                    Fact. Incremental commits in PL/SQL processing is flawed processing. It is ALWAYS wrong.

                    • 7. Re: Inserting BLOB's into table - odd problem
                      KSandbergNGC

                      hmmmm.... interesting responses, although none of them answer my original question.  I was hoping for an explanation of why the PL/SQL seemed to "hang" once I tried loading over 25,000 records. 

                       

                      The incremental commits were added when the odd behavior began.. I thought maybe having 25,000 uncommitted BLOB records were the cause of the problem, so I thought I'd try committing every 1000 to see if it cleared things up... it didn't. 

                       

                      Moving the DBMS_LOB.createTemporary() outside of my loop seemed to do the trick.  Anyone know why? 

                      • 8. Re: Inserting BLOB's into table - odd problem
                        BluShadow

                        There wasn't really a need for any other answer as you'd already answered it yourself.

                        You were creating thousands of temporary lob locators and not freeing them up when you were done with them.

                        Inside your loop you want to create a temporary lob, use it, and then at the end of the loop, dispose of it properly.

                        Remember that temporary lobs use temporary tablespace.

                         

                        Re read the documentation on Temporary lobs:

                         

                        http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_lob.htm#ARPLS600

                         

                        and take note of procedures like FreeTemporary. 

                        • 9. Re: Inserting BLOB's into table - odd problem
                          Billy~Verreynne

                          KSandbergNGC wrote:

                           

                          hmmmm.... interesting responses, although none of them answer my original question.  I was hoping for an explanation of why the PL/SQL seemed to "hang" once I tried loading over 25,000 records.

                          Well, cr@p code is just that - and I tend what that dealt with, before tackling underlying problems in the code. And for good reason as well designed and written code is easy to instrument, test, troubleshoot and debug. Unlike cr@p code.

                           

                          The incremental commits were added when the odd behavior began.. I thought maybe having 25,000 uncommitted BLOB records were the cause of the problem, so I thought I'd try committing every 1000 to see if it cleared things up... it didn't. 

                           

                          Moving the DBMS_LOB.createTemporary() outside of my loop seemed to do the trick.  Anyone know why?

                           

                          Sound like a resource related/leakage issue. Have you read Operations Specific to Persistent and Temporary LOBs?

                           

                          I would create a single code unit (proc in package) that receives a filename as input and loads the file into a table. It should not have external moving parts (such as resources being managed somewhere else).

                           

                          I would then use DBMS_PARALLEL_EXECUTE to fire up 2 or 3 processing queues for 2 or 3 distinct lists of files to process - and have these run in parallel. This provides for scalability as it can make use of increases in performance in the I/O fabric layer - or be throttled down to 1 queue when the I/O layer is under pressure.