5 Replies Latest reply: Mar 14, 2013 2:25 PM by Mark Williams-Oracle RSS

    ODP.NET connection pooling for multithreading open connection same time

    996787
      My application is very critical on timing, so it forks 15 threads to go to oracle 11g server to submit query to read data parallelly. Here comes the problem, 15 threads call oracle connection open at the same time, I config the pooling be Min Pool Size=15; Connection Timout=60. When I run select * from v$session right after 15 threads run and I notice the 15 connections in the pool for this application account created slowly, one by one. So if 15 thread open connection at the same time, it is very slow, the last connection need wait for 50 seconds.
      How to config the connection pooling if multithread trying to open connection at the same time? I tried Incr Pool Size=15; same thing: the connections in the pool generated slowly one by one. Or connection pooling not fit for this situation? Can I keep pool in oracle exist forever so that only first time multithread run is slow?

      Thanks,
        • 1. Re: ODP.NET connection pooling for multithreading open connection same time
          Tridus
          Unless the connections are just taking a long time to open, I'm not sure why this would happen. Are you sure you don't have any code in your threads that is blocking?
          • 2. Re: ODP.NET connection pooling for multithreading open connection same time
            996787
            Thread doesn't block. 15 threads go to oracle connection open at the same time, just when the first thread open connection, odp.net connection pooling trying to create 15 size(minimum) of the pool, but it not created all 15 connections all at once, it creates one by one, not sure if this is the design of odp.net pooling, do you know exactly how the design is?

            Thanks,
            • 3. Re: ODP.NET connection pooling for multithreading open connection same time
              996787
              OK, when i run the application in the office network, the multiple thread open connection is quick and I see the 15 size is created in 5 seconds, so I should run the application is the office later on.

              Thanks,
              • 4. Re: ODP.NET connection pooling for multithreading open connection same time
                Tridus
                I'd guess the connection pool is opened sequentially, yeah. They all have to go into one collection in the driver at some point (so it can pool them), so there's going to be some synchronization there. 15 seconds is a really long time though.
                • 5. Re: ODP.NET connection pooling for multithreading open connection same time
                  Mark Williams-Oracle
                  Hi,

                  I suspect what you are seeing is a side-effect of the ThreadPool use by the connection pooling.

                  The thread pool has a built-in delay of 500 ms between creating/adding threads to the pool (I believe this is the case for current versions, someone can correct if inaccurate).

                  So if a bunch of new threads need to be created/added to the pool to support the connections in the pool this could be a gating/limiting factor (such as during a "logon storm").

                  For cases such as this I have effectively used the ThreadPool.SetMinThreads method to pre-create a number of threads before the Oracle connection pool is created.

                  Perhaps that is something you can use in this case.

                  Edited by: Mark Williams on Mar 14, 2013 3:23 PM:

                  Here's a snippet of code from a simple console demonstration application so you can get an idea of what I mean:
                  ...
                  // set to minimum number of threads / connections
                  const int numberOfThreads = 16;
                  
                  // will use these to get and set number of threads
                  int workerThreads, iocThreads;
                  
                  // get current number of minimum threads for the pool
                  ThreadPool.GetMinThreads(out workerThreads, out iocThreads);
                  
                  Console.WriteLine("Got {0} Worker Threads, and {1} IOC Threads", workerThreads, iocThreads);
                  
                  // here we simply try to set the minimum number of threads equal to numberOfThreads
                  // and display result
                  if (ThreadPool.SetMinThreads(numberOfThreads, iocThreads))
                  {
                    ThreadPool.GetMinThreads(out workerThreads, out iocThreads);
                    Console.WriteLine("Set {0} Worker Threads, and {1} IOC Threads", workerThreads, iocThreads);
                  }
                  else
                  {
                    Console.WriteLine("Failed to set minimum number of threads in pool.");
                  }
                  ...
                  Regards,
                  Mark