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.

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
        }

    }

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.