This discussion is archived
11 Replies Latest reply: Oct 26, 2012 10:43 AM by Mark Williams-Oracle RSS

Oracle Connection Timeouts not Working as Expected

464093 Newbie
Currently Being Moderated
Using ODP, we are testing the connection timeouts by introduce a network delay of 500ms in each direction using Wire-shark.

We tested 5 Inserts tested with a pool of size of 3. Connection Timeout=2, Command Timeout=2. The result shows the first transaction succeeds but not the last four transactions that are connection
timed out. The trace were turned on. The first transaction succeeded but above the timeout value and last four transaction time 14 seconds after they should have. The traces provided below show that all connections are almost established at the same time, but only the first transaction doesn’t time out even if it has a gap of near 13 seconds between the connection open and the insert command.

The connection was requested 9:43:21.521 with Data Source=denb1dbvast-02/SCPBLD1;User Id=scpdev1;Password=scpdev1;Pooling=True;Min Pool Size=3;Max Pool Size=3;Connection Lifetime=60;Connection Timeout=2;Incr Pool Size=1;Decr Pool Size=1;Statement Cache Size=10;Self Tuning=True;HA Events=False;Load Balancing=False

TIME:2012/09/24-09:43:21:771 TID:10c8 (ENTRY) OracleConnection::Open()
TIME:2012/09/24-09:43:21:771 TID:1ed4 (ENTRY) OracleConnection::Open()
TIME:2012/09/24-09:43:21:833 TID: 6ec (ENTRY) OracleConnection::Open()
TIME:2012/09/24-09:43:21:911 TID:12b8 (ENTRY) OracleConnection::Open()
TIME:2012/09/24-09:43:21:973 TID:1218 (ENTRY) OracleConnection::Open()

Followed by a series of messages exchange between DB server and Async_DB app. The total number of messages varies in the range [32, 38]

TIME:2012/09/24-09:43:34:517 TID:1ed4 (EXIT) OracleConnection::Open()

TIME:2012/09/24-09:43:34:532 TID:1ed4 OpsSqlPrepare2(): SQL: INSERT INTO NSC_Usage( Origin, MDN, Session_ID_PK, Off_Hook_TS, On_Hook_TS, Flag, Degraded_Status, Degraded_Filename, CCR_Origin_Host, CCR_Destination_Host, CCR_Origin_Realm, CCR_Destination_Realm, CCR_Auth_Application_ID, CCR_Service_Context_ID, CCR_Last_Request_Type, CCR_Last_Request_Number, CCR_Event_Timestamp, CCR_Subscription_ID_Type, CCR_Subscription_ID_Data, CCR_Req_Serv_Unit_CC_Time ) Values ('A','5052035787','VIP02.101624612' ,'18-MAY-11 12.00.00.000000000 PM','18-MAY-11 12.01.00.000000000 PM',128,0,'Filename','vip0101.cricket.com','realtime_A.convergys.com','cricket.com','convergys.com',4,'voice@cricket.com',3,2,'20110308192815',0,'8888888888',180 )
TIME:2012/09/24-09:43:35:609 TID:1ed4 (ENTRY) OpsSqlFreeCtx()
TIME:2012/09/24-09:43:35:609 TID:1ed4 (EXIT) OpsSqlFreeCtx(): RetCode=0 Line=163
TIME:2012/09/24-09:43:35:609 TID:1ed4 (EXIT) OpsSqlExecuteNonQuery(): RetCode=0 Line=877
TIME:2012/09/24-09:43:35:609 TID:1ed4 (EXIT) OracleCommand::ExecuteNonQuery()
TIME:2012/09/24-09:43:35:640 TID:1ed4 (ENTRY) OracleConnection::Close()
TIME:2012/09/24-09:43:35:655 TID:1ed4 (ENTRY) OpsConCheckConStatus(): (311cf8)=3218680
TIME:2012/09/24-09:43:36:576 TID:12b8 (ERROR) ODP error code=-1000; ODP message=Connection request timed out
TIME:2012/09/24-09:43:36:576 TID:1218 (ERROR) ODP error code=-1000; ODP message=Connection request timed out
TIME:2012/09/24-09:43:36:576 TID:10c8 (ERROR) ODP error code=-1000; ODP message=Connection request timed out
TIME:2012/09/24-09:43:36:607 TID: 6ec (ERROR) ODP error code=-1000; ODP message=Connection request timed out

The first Insert was sent and executed successfully.

• Why did the first transaction succeed instead of timing out?
• Why did it take 14 seconds for the other 4 connections to timeout?

Nathan
  • 1. Re: Oracle Connection Timeouts not Working as Expected
    Johnsung Pro
    Currently Being Moderated
    Hello Nathan,

    Can you post the source that we can see that demonstrates the scenario described?
    Ideally if it's minimal code and we can run it to test here.

    All I can say from the short text is it looks like the connection "1ed4" is taking about 13 seconds to complete OracleConnection.
    The rest are timed out as expected.
    I'm wondering if the delay is caused by load - how long does it take to make one connection - e.g. set min pool size =0 and only open a single connection in your test?
    Does that also show about 13 seconds?


    Thanks
    John
  • 2. Re: Oracle Connection Timeouts not Working as Expected
    958859 Newbie
    Currently Being Moderated
    When does the connection timer start at? Does it start at the moment when the OracleConnection::Open() function starts as below?

    TIME:2012/09/24-09:43:21:911 TID:12b8 (ENTRY) OracleConnection::OracleConnection(2)
    TIME:2012/09/24-09:43:21:911 TID:12b8 (EXIT) OracleConnection::OracleConnection(2)
    TIME:2012/09/24-09:43:21:911 TID:12b8 (ENTRY) OracleConnection::Open()
    TIME:2012/09/24-09:43:21:911 TID:12b8 (ENTRY) OpsConAllocValCtx()
    TIME:2012/09/24-09:43:21:911 TID:12b8 (EXIT) OpsConAllocValCtx(): RetCode=0 Line=400
    TIME:2012/09/24-09:43:36:576 TID:12b8 (ERROR) ODP error code=-1000; ODP message=Connection request timed out
    TIME:2012/09/24-09:43:36:607 TID:12b8 (ENTRY) OracleConnection::Dispose()
    TIME:2012/09/24-09:43:36:607 TID:12b8 (ENTRY) OpsConFreeValCtx()
    TIME:2012/09/24-09:43:36:607 TID:12b8 (EXIT) OpsConFreeValCtx(): RetCode=0 Line=1329
    TIME:2012/09/24-09:43:36:607 TID:12b8 (EXIT) OracleConnection::Dispose(

    Thanks.
  • 3. Re: Oracle Connection Timeouts not Working as Expected
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    ConnectionTimeout actually represents the minimum amount of time the connection will remain open before timing out. It's possible for a connection to exist past the timeout value without actually timing out. Just that when it exceeds its timeout lifetime value, the connection could be timed out at any time.

    The doc is incorrect when it refers to the timeout as the maximum value. It should actually be the minimum value. I'll have the doc corrected for the next release.
  • 4. Re: Oracle Connection Timeouts not Working as Expected
    464093 Newbie
    Currently Being Moderated
    Alex,

    Thank you for your reply.

    The fact that it represents the Minimum Amount of Time explains the behavior we are seeing for Connection Timeouts.

    *Is this the case with the Command Timeouts as well?*

    We need to provide a Maximum Amount of Time in our application. We are executing multiple SQL command requests on multiple threads and we need to set a limit to the Connection and Transaction Timeouts.

    *Can ODP.NET provide for Maximum Amount of Time ?*

    I am hoping the answer is yes.

    If not, it canget complicated as I would need to create 2 timers in the application for each thread; one for Connection Timeouts and one for Command Timeouts, Doing this can be difficult as there can be race conditions, i.e., the timer expires at the same time the connection or command succeeds.

    Would closing a connection in progress cause an issue?

    Would canceling a transaction in -progress cause an issue?

    Are there examples of the recommended way to handle the various race conditions for both types of timeouts?

    Nathan
  • 5. Re: Oracle Connection Timeouts not Working as Expected
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    Is this the case with the Command Timeouts as well?
    Yes

    Can ODP.NET provide for Maximum Amount of Time ?
    No, because how long the connection or command resource exists on the server side is not within ODP.NET's control. In an ideal world, a connection timeout would represent the exact amount of time the connection would exist before it timed out. But, ODP.NET only has control over its own client side resources. ODP.NET cannot override what the network admin has set nor what the DBA has set.

    During a timeout ODP.NET tells other Oracle resources (i.e. network, server) we are timing out. How quickly these upstream resources react is not within our control. If you want Oracle SQL*Net to enforce a specific timeout, you can set tnsnames.ora and/or sqlnet.ora with respective CONNECT_TIMEOUT values. There are few different Oracle Net timeout values you can set depending on how long you want a connection resource to wait at each specific step of the connection process.

    Command timeouts follow a similar method, though I'm not aware of any network settings that can guarantee a maximum amount of time. We try to execute the cancel as quickly as possible at a high priority, but if the resources we are using (i.e. network, server) are busy, it's not immediate. I would recommend talking with your DBA to discuss a strategy to best handle your timeout strategy. It cannot be done only on the ODP.NET side if you want a max setting.

    You can try closing/canceling the ODP.NET resource yourself if you'd like, but you would be doing the same thing ODP.NET would be doing essentially.
  • 6. Re: Oracle Connection Timeouts not Working as Expected
    464093 Newbie
    Currently Being Moderated
    Alex,

    Thanks for the prompt reply.

    Not having Maximum times in ODP will make things complicated and a myriad of race conditions can occur.

    There is an involved example of a Transaction Timeout Cancel that I could implements with a an application Maximum timer on page 5-27 of the Oracle Data Provider for .NET Developer's Guide and it is not easy to implement

    However, it is not described anywhere on how to implement a Maximum Connection Timeout.

    In the example below from the docs the application will block at the Open() call:

    // Connect
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    con.Open();

    So do we do the following:

    // Connect
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    *//Set_Application Timer*
    con.Open();

    OnApplicationTimer()
    con.Close()
    //Do application timeout processing

    What will the Close() do for us here? What will happen if the Connection open is still in progress when we try to close it?

    Should we forget attempting to Close() and wait until Oracle decides to return in the first method as shown below?

    OnApplicationTimer()
    //Do application timeout processing but don't explicitly send Close()

    // Connect
    string constr = "User Id=scott;Password=tiger;Data Source=oracle";
    OracleConnection con = new OracleConnection(constr);
    *//Set_Application Timer*
    con.Open();
    //Eventually it will timeout. If succesful, clo0se immediatly since it is too lpate.

    Nathan
  • 7. Re: Oracle Connection Timeouts not Working as Expected
    Alex_Keh - Oracle_Product_Manager Expert
    Currently Being Moderated
    One thing I want to impress upon is under normal circumstances, the connect timeout happens at exactly the time the developer sets connect timeout to. The only time that it doesn't is when the client machine is so busy that it can't get to executing the connect timeout without a delay.

    Thus, the easiest solution for ODP.NET to adhere closest to the connect timeout value is to add more client machines. The key thing is to ensure that the mid-tier machines are not over loaded, especially if you need ODP.NET to execute tasks at precise timing. If you can't add any more machines, then you can reduce the connection pool max size until you reach a point that guarantees the service level you seek.

    If the purpose is so that the OracleConnection.Open call doesn't block for more than a specified amount of time, you can create a separate thread and execute the Open asynchronously as well.

    One thing earlier I said that I want to correct. For ODP.NET connections, timeouts can be delayed based on mid-tier/client activity, not anything on the network or DB side. The network and DB activity can affect command timeouts, however. Sorry about that earlier misstatement.
  • 8. Re: Oracle Connection Timeouts not Working as Expected
    464093 Newbie
    Currently Being Moderated
    Alex,

    We are getting acceptable results under normal conditions. It is under degraded conditions that we see an issue.

    This is being deployed in a telephony based application and long delays cannot be tolerated and need to be handled. E.g., an incoming call cannot expect the caller to wait > 2 seconds. To simulate degraded conditions, we use Wireshark to introduce a network delay. We have found in some cases up to 14 second delays on some transactions. That is why we cannot relay on a minimum delay but must provide a maximum delay timeout. What makes thing a little more difficult is that ODP never implemented an Async version of OracleConnection Open() and on Transaction like some other .NET providers do. This also includes transactions, i.e., oraCMD:ExecuteReader(), oraCMD:ExecuteNonQuery() and oraCMD:ExecuteScalar() as well. Therefore, the application blocks on the Open() or the above transaction commands.

    We have accounted for this by doing each DB SQL transaction in a seperate thread thereby not blocking the main thread and this works well. However, since ODP cannot be relied on to terminate at the maximum timeout value specified, under degraded conditions, we need to have an application based timer in yet another thread that will tell the main thread that DB transaction failed and continue processing. That is where things get a little complicated since a connection or transaction could be along way or just about to complete. If ODP supported a polling mechanism, this would be easier to accomplish since we would not require a timer in another thread.

    E.G.:

    Async_Open()

    WHILE ! Complete()

    //Check elapsed time

    ENDDO

    Nathan
  • 9. Re: Oracle Connection Timeouts not Working as Expected
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Apologies for a tangential question here...

    How are you using Wireshark to introduce a delay? Is it just the act of monitoring with Wireshark?

    In the past I have looked at Wireshark for doing such activities but have not found a way to do so.

    I have, instead, used a Linux host and the tc (traffic control) command to introduce a delay and/or limit bandwidth.

    For example, something like this:

    # introduce a 500 ms delay on eth0
    tc qdisc add dev eth0 root netem delay 500ms

    Can you do something similar with newer releases of Wireshark?
  • 10. Re: Oracle Connection Timeouts not Working as Expected
    464093 Newbie
    Currently Being Moderated
    Mark,

    I was incorrect about using Wireshark for the actual delay.

    We are using an software called Network Delay Simulator.

    Nathan
  • 11. Re: Oracle Connection Timeouts not Working as Expected
    Mark Williams-Oracle Employee ACE
    Currently Being Moderated
    Hi Nathan,

    No worries, thanks for the follow-up!

    Mark

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points