Oracle Data Provider for .NET: Connection request timed out

[bluredEn]

We have a WCF web service hosted on Windows 2008 SP2/IIS 7 accessing an Oracle database. Usually data access works fine but during load testing, it often times out and logs an exception saying:

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 confident that the stored procedure that is being invoked is not taking all the time. It is a pretty simple procedure that quickly checks if the P_Staff_Id exists in the table and returns the result.

Additionally, this occurs only during load testing. During normal operations things are fine but during heavy loads with 1 message per second, this occurs after running smooth for some time.

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

We have the same application running on a development server and it works fine. We never encountered this problem there.

Any suggestions as to what to try would be appreciated. It looks like I am running out of options.

[Arjay]

Not familiar with the Oracle provider but try wrapping the OracleConnection in a using block. Also, not that it's the problem but the OracleCommand should have a CommandTimeout property.