Search code examples
oracle-databaseodp.netoracle12codac

Client application hangs when inserting into table on Oracle using ArrayBinding

Here is our environment:

.Net version: 4.5

Database: Oracle 12.1.0.2 (odp.net)

We are using LLBL "Adapter" but I don't think that has anything to do with the issue

LLBLGen Pro version: 4.1

Llbl Gen Pro Runtime: 4.1.13.1213

When we do an Insert(always into different tables which we are using for the short period and then removing) we use the following code:

int numRecords = strings.Count();
var insertCmd = "insert into " + tableName + " (StringField) values (:StringField)";

var oracleCommand = new OracleCommand();    
oracleCommand.CommandText = insertCmd;
oracleCommand.CommandType = CommandType.Text;
oracleCommand.BindByName = true;
oracleCommand.ArrayBindCount = numRecords;

oracleCommand.Parameters.Add(":StringField", OracleDbType.NVarchar2, strings.ToArray(), ParameterDirection.Input);               

// this is an LLBL adapter.  Like I said, I think the issue is below the LLBL layer.
this.adapter.ExecuteActionQuery(new ActionQuery(oracleCommand));

When the database is getting hit hard with multiple of these inserts in parallel, we get the following error and the insert call never returns from the database.

  • WG_6.Index_586.TVD: An exception was caught during the execution of an action query: ORA-24381: error(s) in array DML

ORA-12592: TNS:bad packet

ORA-12592: TNS:bad packet

ORA-12592: TNS:bad packet

ORA-12592: TNS:bad packet

ORA-03111: break received on communication channel

ORA-03111: break received on communication channel

ORA-03111: break received on communication channel

On the database, using Toad's session browser, I can see that the "Current Statement" is correct. insert into schemaX.tableY(StringField) values(:Stringfield)

Under the Waits tab in Toad, there is the following message: “Waiting for SQL*Net more data from client - waited X hundred seconds, so far” and the X keeps incrementing until we hit our database timeout.

We tried with batches of 1 million and this gave us the best performance for our scenario. However, this hanging issue arose. I then decrease the ArrayBindCount to 500K, 100K, 50K, 10K and then 5K. Only when I used 5K did it stop happening.

A couple of notes:

  1. This happens more frequently when the database is on a different physical machine than the client. When using a local VM, it rarely happens. The network that we are using is generally very reliable with no other noted issues.

  2. From the error message(ORA-12592: TNS:bad packet), it seems that the issue might be on the client and perhaps related to code in the "Oracle.DataAccess.Client"(ODAC) dll.

My next steps for troubleshooting are to use Reflector to debug the call from the ODAC code and also to get more reliable client side tracing while forcing this error to occur.

Solution

  • I had the same situation when trying to insert into an Oracle table using the ArrayBinding.

    Using a small number for oracleCommand.ArrayBindCount seemed to improve the frequency of the errors (same like yours) but not completely. The solution was to use the Managed data access. I suggest you get the latest ODP.NET, add a reference to ManagedDataAccess and change to:

    using Oracle.ManagedDataAccess.Client;

    using Oracle.ManagedDataAccess.Types;

    This fixed problem in my case and with no need to change anything in the code.