title: ODP .NET behaves weird
body: <p>The application itself described in my previous <a href="https://stackoverflow.com/questions/18735577/architecture-of-real-time-iteration-application">question</a>. On the DAL side I use</p>
  
  <pre><code>Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342
  </code></pre>
  
  <p>Here is connection string:</p>
  
  <pre><code>User id=usr;Password=pwd;Data Source=database1;Validate connection=True;Connection timeout=8;Pooling=false
  </code></pre>
  
  <p>The starange is that sometimes ODP raises out following exception:</p>
  
  <pre><code>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.&lt;&gt;c__DisplayClass30.&lt;&gt;c__DisplayClass32.&lt;Synchronize&gt;b__2f(ID id) in D:\MySyncApp\MySyncApp\Program.cs:line 441
  </code></pre>
  
  <p>But after this exception, when I look on sessions in Oracle, I see that <strong>actually connection is being alive</strong> and just marked as <code>INACTIVE</code>! So, such connections will continue hanging on server side, gradually exhaust a count of available sessions.</p>
  
  <p>There is nothing special in my code, just </p>
  
  <pre><code>public List&lt;DataObj&gt; GetChangedDataDB(DateTime startPeriod)
  {
      List&lt;DataObj&gt; list = new List&lt;DataObj&gt;();
      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;
  }
  </code></pre>
  
  <p>This code runs in <code>Parallel.ForEach</code> 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).</p>
  
  <p>The Oracle is</p>
  
  <blockquote>
    <p>Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
    Production</p>
  </blockquote>
  
  <p>The synchronization process itself fires on timer in 10 seconds interval. If there is already runnning task then next task is being stopped:</p>
  
  <pre><code>    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) =&gt;
          {
              Console.WriteLine("Started synchronization for {0}", l.key);
              DBRepository db = new DBRepository(l.connectionString);
  
              Parallel.ForEach(l.departments, new ParallelOptions { MaxDegreeOfParallelism = -1 }, (department) =&gt;
              {                       
                  DateTime ChangesFromTS = GetPreviousIterationTS;
                  List&lt;DataObj&gt; cdata = db.GetChangedDataDB(ChangesFromTS);
                  // ... doing the work here
              }
  
          }
  
          // Finishing work
  
          GC.Collect();            
  
          lock (Program.StateLock)
          {
              Program.State = "";
          }
  
      }
  </code></pre>
  
  <p>Here is timer for invoking synchronize tasks periodically:</p>
  
  <pre><code>Program.getModifiedDataTimer = new Timer(Program.Synchronize, null, (int)Global.config.syncModifiedInterval * 1000, (int)Global.config.syncModifiedInterval * 1000);
  </code></pre>
  
  <p><code>Global.config.syncModifiedInterval</code> is in seconds</p>
  
  <p>ODP behaves itself the same way when I turn on pooling. It's created more connections than allowed by <code>Max pool size</code> directive in connection strings with same exception.</p>
  
  <p>Please tell me your thoughts and experience on that stuff.</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is a piece of Oracle trace when exception is raised:</p>
  
  <pre><code>(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()
  </code></pre>
  
  <hr>
  
  <p><strong>UPDATE #2</strong></p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <hr>
  
  <p><strong>UPDATE #3</strong></p>
  
  <p><a href="https://www.dropbox.com/s/7zbnyhugf0hmhwl/DemoPool.zip" rel="nofollow noreferrer">Here</a> is the demo application that makes same issue. As I previously told, it appears on bad connection, you could simulate such connection using <a href="http://sourceforge.net/projects/wanem/files/" rel="nofollow noreferrer">WANem</a> emulator. <a href="https://www.dropbox.com/s/r8ynyvp1tgu6d31/Oracle.ManagedDataAccess.dll" rel="nofollow noreferrer">Here</a> is the same component I use for database connectivity. Hope for your help.</p>