Click here to Skip to main content
15,346,893 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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 and 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:

C#
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.
Posted
Updated 23-Nov-21 17:33pm

Based on the exception it seems that the problem is in establishing the connection so the procedure hasn't even been called yet:
at Oracle.DataAccess.Client.OracleConnection.Open()

Does the connection timeout in the connection string have effect? What I mean is that if you define 600, does it take 60 seconds to get the exception. What I'm after is to ensure that the correct connection string is modified.

Another thing is that do you use different user names? If you do the connection pool is user specific (well actually connection string specific) so this could cause a situation where there is no connection waiting for use.

Also as this happens only with high workload it's quite possible that the server just isn't capable of answering in sufficient amount of time. I would make sure that the server isn't paging and has enough resources to run operations. For example an oversized SGA could cause serious problems.

Also check from the server side if you see the connections in v$session and when connecting, are they waiting for something.

Just an additional though: You could also try defining Validate Connection to true to perhaps get more information about the situation, not sure if it reveals anything new but perhaps worth trying.
   
Comments
ddas-edEn 27-Sep-15 11:53am
   
Hi Mika. I added the Connection Timeout after this issue cropped up. In the DEV environment it was working fine without the Connection Timeout and Max Pool Size. Added these didn't help me resolve the issue however. I am yet to analyse if the exception is really arriving after 60 seconds.

Regarding the user names, I am using only a single user every time to connect to the database.

Problem with getting the answers to the paging and other configurations on the server is that it is managed by another person and I don't have access to it. I will however try to get that information.
Wendelius 27-Sep-15 13:04pm
   
Sounds good.

Based on the symptoms the problem is most likely on the server side so that's where I'd start looking.

60 seconds for a connection is a long time even via a slow network. However if the server is busy doing everything else it could be that it just can't give a new connection in time.

When you get any new info, please let us know.
A common reason for running out of resources like that is that previous connections hasn't been released.

One simple way to do it is to wrap the connection in a using statement.
C#
using (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
        var returnVal = int.Parse(cmd.Parameters["P_retvalue"].Value.ToString());
    }
}
   
  cmd.Parameters["P_retvalue"].Direction = ParameterDirection.Output;
Can you please change the output to Refcursor. Because in my application also we are facing same test during load testing. We are changed to output to Refcursor that time the issue resolved. Can you please try on this 
   
Comments
Dave Kreskowiak 23-Nov-21 23:42pm
   
I seriously doubt the OP is going to try your suggestion six years after asking the question.
manikjeyam 24-Nov-21 10:50am
   
Sorry Day before yesterday only we are facing same issue in load testing after we have changed the out parameter to ref cursor it is working fine for me so only is suggested this post.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900