Oracle Data Provider for .NET: Connection Timeout

Question

We have a C # WCF web service hosted in Windows 2008 SP2 / IIS 7, access to an Oracle database. Data access usually works fine, but during load testing it often crashes, and the logs and exception say:

Error occurred when processing XXXXXXXX Web Service Oracle.DataAccess.Client.OracleException Connection request timed out at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src) at Oracle.DataAccess.Client.OracleConnection.Open() at MyWorkspace.WorkForceDataAccess.CheckStaffIdInRSW() at MyWorkspace.MyClass.MyFunction(MyDataType MyData) 

To query the database, we use something like this:

 OracleConnection orConn = new OracleConnection(); orConn.ConnectionString = "user id=xxx; password=xxx; Connection Timeout=600; Max Pool Size=150; data source= (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST.MYDOMAIN.com)(PORT = 1771)) (CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = MYSERVICE.MYDOMAIN.com)))"; orConn.Open(); using (var cmd = new OracleCommand("MY_UTIL.check_StaffIdInRSW", orConn) { CommandType = CommandType.StoredProcedure }) { cmd.Parameters.Add("P_Staff_Id", OracleDbType.Int32); cmd.Parameters["P_Staff_Id"].Direction = ParameterDirection.Input; cmd.Parameters["P_Staff_Id"].Value = Convert.ToInt32(MyDataObject.StaffId); cmd.Parameters.Add("P_retvalue", OracleDbType.Int32); cmd.Parameters["P_retvalue"].Direction = ParameterDirection.Output; cmd.ExecuteNonQuery(); // Execute the function //obtain result returnVal = int.Parse(cmd.Parameters["P_retvalue"].Value.ToString()); } 

I am pretty sure that the stored procedure that is being called does not take all the time. This is a fairly simple procedure that quickly checks if P_Staff_Id exists in the table and returns the result.

In addition, this only occurs during stress testing. During normal operation, everything is fine, but during heavy loads with 1 message per second, this happens after a smooth transition for some time.

As a workaround, I added "Connection Timeout = 600; Max Pool Size = 150" to the connection string, but this did not fix the problem.

We have the same application running on the development server, and it works great. We have never encountered this problem.

Any suggestions on what to try would be appreciated. Looks like I'm running out of options.

Answer 1

We had a similar problem and it took some time to debug this and fix it. Our code causes stress with many input files and many threads, each thread uses the Entity structure and opens an Oracle db connection, and also makes a bunch of db queries and inserts, which are sometimes used for writing. But it works most of the time.

I modified the DbContext constructor to explicitly open OracleConnection. I added code like this

 for (i = 0; i < 5; i++) try { oracleConnection.Open(); } catch (OracleException) { Sleep for 15 ms and retry. On last attempt I also do OracleConnection.ClearAllPools() } 

It improved, but still did not solve it completely. I broke the trick from the debugger and saw that many threads are trying to open, and several threads are being processed. In Open on an Oracle stack, Oracle executes ThreadPool.QueueUserWorkItem for its internal purpose and waits for it to complete. I can see from the stack of his expectations. Many unified connections are available here (the default is 100), I hardly use 10. So this is not a resource.

But the problem in our code was also used by ThreadPool.QueueUserWorkItem without additional regulation. I thought it was great to just queue up for all the tasks that we need to do, as much as we need, and let .NET take care of that. But this has a subtle question. All our jobs consume the full queue count. When OracleConnection wants to get a federated connection from the pool, it also queues the thread pool. But it will never end. Our jobs are waiting for OracleConnection.Open, and its Queued Thread queue will still be in the queue. Therefore, finally, the wait will end in a timeout. It is a pity that despite the fact that there is a lot of connection pool, we consume the entire ThreadPool thread, and Oracle threadpool did not even get a chance. Here, setting ThreadPool.SetMaxThreads won't help either. The question is the same. We run the entire thread pool resource and Orcale will not find it and will still be in the queue.

The fix should not rely solely on ThreadPool, but we are also adding our own throttling. I used BlockingCollection and sempahores and added only a certain number of parallel jobs to ThreadPool, say 5. Thus, OracleConnection will always find the ThreadPool thread and will not work.

Answer 2

try adding connection.close () to the end. I do not see the unlock connections in your code and explicitly return them to the connection pool. Therefore, the connection is returned to the connection pool only when the GC starts.

Answer 3

Even I used this problem more often, even after using Connection.Close ()

After a long analysis, I found out a little as follows

• Connection.Close () does not remove the db connection
• Connection timeout does not mean that the problem is only with the db request
• Connection timeouts can also be due to exhaustive connections in the connection pool (which was guilty for me because it reached the maximum db communication sessions).

Correction: - The analysis took a long time, but the correction is only 2 minutes

 using(DbConnection instance) { } 

For instance: -

 using (DbConnection objDbConnection = new DbConnection()) { ojDbConnection.PersistData(); } 

In PersistData (); All db operations like Open, close e.tc. will be executed

As we all know, "Usage" is a short form.

 try { } catch() { } Finally { Dispose objDbConnection; } 

Hope this helps as it helps me