0 Replies Latest reply on Oct 23, 2012 9:31 AM by 970097

    Performance problem with Blob

      I have a problem with Blob parameter, in my application I have a stored procedure PL/SQL and my app execute it through ODP.NET. Procedure does an upsert to table.
      Calling to the procedure is done concurrently in a few threads.
      Application execute this procedure in a loop (while(true)) as fast as possible, after some time performance decreases.
      When I change parameter to long raw the problem disappears, even when column in table is still Blob.
      Why Blob parameter drops performance ?

      I have also created small application to look for a problem:

      public void Test()
      using (OracleConnection conn = new OracleConnection(_connectionString))
      using (OracleCommand command = new OracleCommand("BlobTest", conn) { CommandType = CommandType.StoredProcedure })
      byte[] blob = new byte[] {33,54,245,65,67,5,9,224,6};
      OracleParameter par = new OracleParameter("data_in", OracleDbType.Blob, ParameterDirection.Input) { Value = blob };

      create or replace PROCEDURE BlobTest(data_in blob )
      tempChangeTimeStamp NUMBER(1):=0;
      IF 0 = 1 THEN
      END IF;
      END BlobTest;

      When I run above test in more than one thread, performance graph in Enterprise Manager shows that a lot of time goes to Configuration (free buffer waits) and performance drops down.
      But why Oracle has to wait for free buffer, when procedure does nothing?

      I'm using Oracle 11g and 64-bit Oracle.DataAccess

      Best regards!
      Thanks for Your help.