Oracle Data Provider for .NET: Connection request timed out

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

  1. EmptyFolder

    EmptyFolder New Member

    Joined:
    Dec 16, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    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:

    Code:
    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:

    Code:
    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.

    :confused:
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice