This discussion is archived
0 Replies Latest reply: Oct 23, 2012 2:31 AM by 970097 RSS

Performance problem with Blob

970097 Newbie
Currently Being Moderated
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))
{
conn.Open();
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 };
command.Parameters.Add(par);
command.ExecuteNonQuery();
}
}
}


create or replace PROCEDURE BlobTest(data_in blob )
AS
tempChangeTimeStamp NUMBER(1):=0;
BEGIN
IF 0 = 1 THEN
tempChangeTimeStamp:=1;
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 2.112.1.0.

Best regards!
Thanks for Your help.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points