ODP.NET Connection request timed out

I have a web application hosted on IIS 7, using Oracle.DataAcess.dll for .NET to perform the connection and queries to the Oracle Data base. In the last week, we suddenly encountered numerous errors thrown by this component, saying: "Connection request timed out.".

After few hours of debugging, I saw that the error is really saying that the connection request has timed out, and throwing an error with code number: -1000 (Of oracle.DataAccess, no ORA- error was thrown). At the Oracle Data Base, I saw that there was only one connection for that machine and it was in an 'INACTIVE' state. Although there was only one connection, when debugging it actually timed out when trying to open the connection.

We do not use a connection pool strategy, but as I was seeing in debug mode, there is a default connection pooling (Even when not specifying the values) that saying that the maximum connections are 100, the timeout is 15 seconds, and that the lifetime is 0. That means that the application on that machine can handle 100 connections.

With that said, I can't figure out why I'm getting the "Connection request timed out." error, with error code=-1000, when my application can create up to 100 connections (according to the settings of the default connection pool), but my DB showing only few connections (happened when my app had one connection and also 5 connections - out of 100).

I will note that I was able to solve this problem specifically after recycling my app, but that already happened more than once so this isn't the solution for this...

Is that something I'm missing?

Is my application actually uses more connections than I saw?

*Update: I've found during debug that there are many threads stuck at the 'PopulatePool' method of Oracle.DataAccess and many other threads that are stuck at the 'WaitForRegularConnection' method...

回答1:

Pay attention to Oracle calls, what queries were sent for execution, connection timeout doesn't depends of connection pool max count , permanently your request serves one connection. Log your requests and try then too simulate them, check execution duration in DB.

回答2:

What your experiencing might be that your server load becomes too big for one or another reason, leading it to take too much time to handle new queries.

Look into the load log of your server if you can, to see what could have been happening on it at the time you experienced the timeouts. If you only hosts this database with the server (better choice), see second option
It is also possible your applications use packages that slowly overcrowds the SGA, and because the session isn't revoked, holds on too much resources. So a solution would be to track this "long lasting" session, and see what it is trying to do.