1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Oracle Data Provider for .NET: Connection request timed out

Discussion in 'Database' started by EmptyFolder, Sep 27, 2015.

  1. EmptyFolder

    EmptyFolder New Member

    Dec 16, 2010
    Likes Received:
    Trophy Points:
    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)))";
    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.


Share This Page