Forum Stats

  • 3,770,203 Users
  • 2,253,081 Discussions
  • 7,875,363 Comments

Discussions

OracleDataAdapter, no command timeout and fill method hang.

User_7NXUZ
User_7NXUZ Member Posts: 2 Green Ribbon

I have a VB program using .NET Framework 3.5 and ODP.NET managed driver (Oracle.DataAccess.dll 2.112.3.0) that connects to a remote server (pooling is default) to query data.

I have assigned CommandTimeout to 5 seconds and tried to query data that takes about 10 seconds to finish. But OracleDataAdapter does not throw SQL time out error when connect to a remote server. Meanwhile OracleDataAdapter do throw SQL time out error when connect to a local database. It looks like the CommandTimeout did not work to a remote server. How to make CommandTimeout work to a remote server?

Another problem is program stuck in the fill method when internet connection lost during execution of method. Setting the CommandTimeout to 60 seconds did not work. Why CommandTimeout no working when connection lost during query data?

Included code:

Dim connection As OracleConnection = New OracleConnection(connectionstring) 

Dim oracleDataAdapter As New OracleDataAdapter(connection.CreateCommand())

Dim dataset as New DataSet

Try

oracleDataAdapter.SelectCommand.AddToStatementCache = False

oracleDataAdapter.SelectCommand.NotificationAutoEnlist = False

oracleDataAdapter.SelectCommand.CommandTimeout = 60

oracleDataAdapter.SelectCommand.CommandText = str_SQL

Dim int_Count As Integer = oracleDataAdapter.Fill(dataset, table)

Return int_Count

Catch

Throw

Finally

Dataset.Dispose()

oracleDataAdapter.Dispose()

End Try

Tagged:
«1

Answers

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,968 Employee
    edited Jun 10, 2021 12:23AM

    The top thing I would recommend is to migrate to the latest ODP.NET release. You can find unmanaged ODP.NET 19.10 xcopy version on OTN. You are using the 11.2.0.3 version, which is about 10 years old.

    With respect to your command timeout issue, cancelling command execution on a remote DB server requires ensuring that your network isn't inadvertently preventing the cancel message from propagating. Some routers or firewalls may drop this network activity. You can learn more about configuring ODP.NET command timeout based on your network topology here.

    For your second situation, the command timeout is not designed for situation in which you lose a connection to the Oracle DB.

  • User_7NXUZ
    User_7NXUZ Member Posts: 2 Green Ribbon

    Alex Keh-Oracle Thanks for the answer.

    For command timeout issue, there is no any lose connection to the Oracle DB. In this condition, commandTimeout no working to a remote server. So how to make CommandTimeout work to a remote server if there is no any lose connection?

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,968 Employee

    You can learn more about configuring ODP.NET command timeout based on your network topology here. That's the most common reason I see Command Timeout failing.

  • user1698609
    user1698609 Member Posts: 7 Green Ribbon

    Hi @AlexKeh-Oracle,

    User_7NXUZ is my colleague. Let me explain a little bit more on the situation that we encountered.

    We tested the SELECT and UPDATE sql statement with CommandTimeout=5 in local database XE, it worked fine. Oracle exception "user request cancel.... " was thrown after 5 seconds.

    But SELECT sql statement CommandTimeout doesn't work after switching the database connection point to the remote database resided in the same network.

    The UPDATE sql statement CommandTimeout still working fine in the remote database.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,968 Employee

    Which ODP.NET version and which DB version are you working with in the remote DB situation?

    If the SELECT statement is executed within five seconds or so, then there won't be a cancellation. CommandTimeout is more like a minimum amount of time for the SQL statement to execute, not a maximum. Moreover, it only measures the time it takes to execute the SELECT. It does not include the time it takes to return results to the client.

  • user1698609
    user1698609 Member Posts: 7 Green Ribbon

    We are using Oracle.DataAccess.dll 2.112.3.0. The DB is XE 10 for both local and remote DB.

    Understand the CommandTimeout will not trigger exactly the when it reach the time set (for example 5sec), but I would expect it will thrown anytime after 5sec as the SELECT query taking 10+ sec to complete.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,968 Employee

    That does seem to be taking a long time for the command timeout to occur. A DB trace could tell us more exactly why the command timeout doesn't trigger. However, it would be fruitful to upgrade to the latest DB and ODP.NET releases to ensure you are not hitting a bug that's already been fixed. The Oracle stack you're using is 10 or so years old.

  • user1698609
    user1698609 Member Posts: 7 Green Ribbon

    I did an upgrade the Oracle Client to 19, the database server maintain at 11g.

    We're trying to simulate oracle client and db server communication packet loss by unplugging the network cable at the app server during executing of the query and plug it back few second later. In some cases, the program just hang at the line of data fetching infinitely (as highlighted below).

    Few minutes later, the program try to break the query by invoking OracleCommand.Cancel but nothing happened (I would expect ORA-01013 or other exception will be thrown). Also tried to close the connection but it hang at the Connection.Close() infinitely.

    We did same test by pointing database to XE 18c. We can't simulate the above hang issue but rather ORA 03113 error was thrown when unplugging and plunging back of network cable during executing query (usually it happened within 60 second). This is still ok as the program able to continue instead of hanging and error handling can do a retry and resume gracefully.

  • Alex Keh-Oracle
    Alex Keh-Oracle Posts: 2,968 Employee

    For network outage recovery, Oracle Application Continuity (AC) is needed to recover the session.

    AC actually solves a larger class of HA recovery scenarios than just losing the network, which is why it's part of RAC, RAC One Node, and Active Data Guard, but not part of XE. Unmanaged ODP.NET supports AC.

    ODP.NET command cancellation works if no software nor hardware outage occurs.

  • user1698609
    user1698609 Member Posts: 7 Green Ribbon

    We developed program (in .NET) to try to reproduce the hang issue. The program just keep fetching data from database. In normal execution, the query will take ~4 seconds to fetch ~140000 records. The hang able to simulate 4 out of 15 times (~30%).

    Below the steps to reproduce the hang

    1. Run the application. The app just keep querying data from the database server. The function as show below.
    2. Observe the query execution, at ~ 2nd or 3rd second (based on the log display on the screen), unplug the network cable
    3. Plug back the network cable at ~15th second
    4. The program occasionally just hang at the line oracleDataAdapter.Fill(dataset)

    We tested against different version of unmanaged and managed Oracle Client 11, 12 and 19 and database server 11 XE and 18 XE, hang issue able to reproduced.

    Is this the behavior or the Oracle Client? Any suggestion to break the execution? Or any suggestion to handle this behavior?