ODP .NET behaves weird

Question

The application itself described in my previous question. On the DAL side I use

Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342

Here is connection string:

User id=usr;Password=pwd;Data Source=database1;Validate connection=True;Connection timeout=8;Pooling=false

The starange is that sometimes ODP raises out following exception:

Oracle.ManagedDataAccess.Client.OracleException (0xFFFFFC18): Connection request timed out
   in OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName)
   in OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   in OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   in OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   in Oracle.ManagedDataAccess.Client.OracleConnection.Open()
   in MySyncApp.DBRepository.GetChangedDataDB(DateTime startPeriod) in D:\MySyncApp\MySyncApp\DB.cs:line 23
   in MySyncApp.Program.<>c__DisplayClass30.<>c__DisplayClass32.<Synchronize>b__2f(ID id) in D:\MySyncApp\MySyncApp\Program.cs:line 441

But after this exception, when I look on sessions in Oracle, I see that actually connection is being alive and just marked as INACTIVE! So, such connections will continue hanging on server side, gradually exhaust a count of available sessions.

There is nothing special in my code, just

public List<DataObj> GetChangedDataDB(DateTime startPeriod)
{
    List<DataObj> list = new List<DataObj>();
    using (OracleConnection conn = new OracleConnection(this._connstr))
    {
        conn.Open();

        using (OracleCommand comm = new OracleCommand("select data from table(usr.syncpackage.GetChanged(:pStart))", conn))
        {
            comm.CommandTimeout = 10;
            comm.Parameters.Add(":pStart", startPeriod);

            using (OracleDataReader reader = comm.ExecuteReader())
            {
                // ..omitted
            }
        }

    }
    return list;
}

This code runs in Parallel.ForEach loop for pulling out data from a lot of databases simultaneously. Even may be three parallel connections to same database (pull out data from different parts of schema, for instance, from a three different departments of enterprise).

The Oracle is

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

The synchronization process itself fires on timer in 10 seconds interval. If there is already runnning task then next task is being stopped:

    public static void Synchronize(object obj)
    {            
        // ... omitted
        log.Info("ITERATION_COMMON_START");

        if (Program.State == "Running")
        {
            log.Info("ITERATION_COMMON_END_BY_EXISTING");
            return;
        }

        lock (Program.StateLock)
        {
            Program.State = "Running";
        }                   

        Parallel.ForEach(Global.config.dbs, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (l) =>
        {
            Console.WriteLine("Started synchronization for {0}", l.key);
            DBRepository db = new DBRepository(l.connectionString);

            Parallel.ForEach(l.departments, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (department) =>
            {                       
                DateTime ChangesFromTS = GetPreviousIterationTS;
                List<DataObj> cdata = db.GetChangedDataDB(ChangesFromTS);
                // ... doing the work here
            }

        }

        // Finishing work

        GC.Collect();            

        lock (Program.StateLock)
        {
            Program.State = "";
        }

    }

Here is timer for invoking synchronize tasks periodically:

Program.getModifiedDataTimer = new Timer(Program.Synchronize, null, (int)Global.config.syncModifiedInterval * 1000, (int)Global.config.syncModifiedInterval * 1000);

Global.config.syncModifiedInterval is in seconds

ODP behaves itself the same way when I turn on pooling. It's created more connections than allowed by Max pool size directive in connection strings with same exception.

Please tell me your thoughts and experience on that stuff.

---

UPDATE

Here is a piece of Oracle trace when exception is raised:

(PUB) (ERR) OracleConnection.Open() (txnid=n/a) Oracle.ManagedDataAccess.Client.OracleException (0xFFFFFC18): Connection request timed out
   in OracleInternal.ConnectionPool.PoolManager`3.CreateNewPR(Int32 reqCount, Boolean bForPoolPopulation, ConnectionString csWithDiffOrNewPwd, String instanceName)
   in OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   in OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
   in OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
   in Oracle.ManagedDataAccess.Client.OracleConnection.Open()

---

UPDATE #2

Seems like this connections shows up because of lag connection, like request to establish oracle connection is sent but its response ignored. Or data transmitted to/from server is being corrupted while it goes to destination.

Connections keeps hanging in server's session list even when I shutdown the application. When I kill a session it is keeping hanging in list with "KILLED" label.

---

UPDATE #3

Here is the demo application that makes same issue. As I previously told, it appears on bad connection, you could simulate such connection using WANem emulator. Here is the same component I use for database connectivity. Hope for your help.

Ichthyoid

Could you provide a dump of your application when it happens ? – Epinasty

@Epinasty Please see my third question update. Hope it makes you easier to reproduce the problem. But yes, surely, I can make a dump. – Ichthyoid

Were you ever able to resolve it? – Rigi

@ScottDowding No, still having problems. – Ichthyoid

I suggest a more descriptive title for this question. :) – Massachusetts

Answer 1

Your connection timeout is extremely low, 8 seconds in general and 10 seconds for the commands you are issuing, try increasing it to maybe a minute to see what happens. I have no idea how heavy your algorithms are, but it is enough that one thread somewhere fails with a query in less than 8 seconds and you will get that exception.

Also, I cannot find documentation stating that ODP.net is not thread-safe, but I cannot find any documentation saying it is either, so if nothing else helps try this change:

    Parallel.ForEach(Global.config.dbs, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (l) =>
    {
        Console.WriteLine("Started synchronization for {0}", l.key);

        Parallel.ForEach(l.departments, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (department) =>
        {                       
            // Now local to the executing thread.
            DBRepository db = new DBRepository(l.connectionString);

            DateTime ChangesFromTS = GetPreviousIterationTS;
            List<DataObj> cdata = db.GetChangedDataDB(ChangesFromTS);
            // ... doing the work here
        }

    }

Cown

Answer 2

I had the same problem with my F# code. It spawns many connections at the same time and don't uses Task Parallel Library. I agree with flindeberg, Motomoto Pink and your own conclusion that problem is that connection confirmatoin response from Oracle comes after connection request timeout exception.

As others, I suggest you to increase connection request timeout. But additionaly you can consider using connection pool with Min Pool Size parameter set to number of parallel spawned threads which will open connections. Using connection pool could considerably increase performance both on client and server side when number of departments is big.

Asclepiadaceous

Answer 3

Did you try to incraese the command timeout (code line in the method GetChangedDataDB)? Something like this

 comm.CommandTimeout = 360;

I've created project which manage a huge data and I also got error message as you, so I add more and more CommandTimeout value, then it works, but I'm not sure if you met the same case as me.

In the other way, I saw you have Parallel.ForEach scope in Parallel.ForEach scope, and I think you may try to modify the second scope with using Task Parallel Library. you could learn it more here http://msdn.microsoft.com/en-us/library/dd537609.aspx

Then your second Parallel.ForEach scope should be like this

Task task = new Task(() =>
Parallel.ForEach(l.departments, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (department) =>
{
    // Now local to the executing thread.
    DBRepository db = new DBRepository(l.connectionString);

    DateTime ChangesFromTS = GetPreviousIterationTS;
    List<DataObj> cdata = db.GetChangedDataDB(ChangesFromTS);
    // ... doing the work here
}
)); //close lambda expression

task.Start();

Cheerio

Answer 4

Indeed it's a weird situation. I've experienced it and was able to reproduce it using a very small program.

The OracleConnection Open() method tends to get very slow if you use parallelism with a high number of tasks (e.g.: over 50, sometimes less).

One workaround is to use Threads instead of Tasks.

UPDATE: after careful investigation I found out that when you use a high number of Tasks, the ThreadPool worker threads queued by the Oracle driver become slow to start, which ends up causing a (fake) connect timeout.

A couple of solutions for this:

Solution 1: Increase the ThreadPool's minimum number of threads, e.g.:

ThreadPool.SetMinThreads(50, 50);  // YMMV

OR

Solution 2: Configure your connection to use pooling and set its minimum size appropriately.

var ocsb = new OracleConnectionStringBuilder();
ocsb.DataSource = ocsb.DataSource;
ocsb.UserID = "myuser";
ocsb.Password = "secret";
ocsb.Pooling = true;
ocsb.MinPoolSize = 20; // YMMV

IMPORTANT: before calling any routine that creates a high number of tasks, open a single connection using that will "warm-up" the pool:

using(var oc = new OracleConnection(ocsb.ToString()))
{
    oc.Open();
    oc.Close();
}

Note: Oracle indexes the connection pools by the connect string (with the password removed), so if you want to open additional connections you must use always the same exact connect string.

Nelle