pooled connection timed out

Question

On the production server sometimes randomly the connection fails to the ORacle database. I get a lot of

Oracle.DataAccess.Client.OracleException 
Pooled connection request timed out
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck, Int32 isRecoverable)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
   at Oracle.DataAccess.Client.OracleConnection.Open()
   at ws.DataConnection() in path. 

I searched for some solutions but no luck. It's strange that the Exception has no Identifier like Ora-123... I use OracleDataAccess client. Sometimes this problem is for 5-10 seconds, sometimes I have to restart the IIS (6.1, Windows Server 2008 R2) to solve the problem. Is frustrating... I cannot set pooling=false because we have a huge website. Any solutions?

Answer 1

The most common cause of this that I am aware of is failing to properly handle IDisposable objects associated with Oracle.DataAccess.Client.

There is probably some code that you have out there that is not properly disposing of some objects. This will cause Oracle to hold on to connections that are not actually in use causing you to run out of available connections in the pool. Restarting IIS solves it because it kills all those connections.

Review your code carefully and make sure that all of the IDisposable objects are being properly disposed of or encapsulated in using statements.

Answer 2

The most common connection issues that I have seen are:

• The application is leaving connections open. This is using up all the available connections and so connections are randomly being refused. The easiest code solution for this is to make sure the application closes the connections as quickly as possible (rather than waiting for variables to leave scope). A "using" statement in .NET works well for this.
• Your connection pool may need to be recycled. You might refer to this site for more information: http://docs.oracle.com/cd/E11882_01/java.112/e12265/manage.htm#BABICIII
• You may just not have a large enough connection pool (default max size is 100). Try increasing this.

One other site that might help is this one: http://blog.ilab8.com/2011/09/02/odp-net-pooling-and-connection-request-timed-out/