Oracle Data Provider for .NET: Connection request timed out

Question

0.00/5 (No votes)

See more:

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 27-Sep-15 2:34am

ddas-edEn

Updated 23-Nov-21 17:33pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Answer 1 · 2015-09-27T02:56:00

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.

Jörgen Andersson · Answer 2 · 2015-09-29T21:38:00

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());
    }
}

manikjeyam · Answer 3 · 2021-11-23T17:33:00

Solution 3

  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

Posted 23-Nov-21 17:33pm

manikjeyam

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.

Oracle Data Provider for .NET: Connection request timed out

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0