3 Replies Latest reply on May 31, 2012 7:59 PM by Udo

    All connections in the Universal Connection Pool are in use

    MatthiasHoys
    MatthiasHoys May 30, 2012 3:07 PM
      Hello,

      We are using the last version of the APEX listener (v1.1.3), together with APEX 4.1.1, Oracle 10.2.0.5 and GlassFish 3.1.1. There are 2 apex war files deployed on GlassFish pointing to 2 different databases instances on the same server. For both of them, Initial Pool Size is set to 3, Minimum Connections to 5 and Maximum Connections to 20.

      The problem is that on several occasions, users couldn't connect any more to APEX. On the database level, the connections taken by APEX_PUBLIC_USER reached the maximum of 20. When I try to increase them using the APEX listener admin page, I receive the following error:
      Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use
      The only way to solve the problem and to release the connections is to restart the GlassFish instance. The problem occurs randomly. The strange thing is that this is a development server, used by only 5 people at the maximum. So I believe 20 connections should be more than enough, no?

      Any idea on how to debug this? The random aspect makes it hard to simulate the problem. I now increased the maximum connections to 30, let's see if this helps...

      Thanks,
      Matthias Hoys
      http://matthiashoys.wordpress.com

      Edited by: mhoys on May 30, 2012 5:06 PM
        • 1. Re: All connections in the Universal Connection Pool are in use
          Udo
          Udo May 30, 2012 8:31 PM (in response to MatthiasHoys)
          Hello Matthias,
          Initial Pool Size is set to 3, Minimum Connections to 5 and Maximum Connections to 20.
          Could it be 5 / 3 / 20? That would make more sense...


          The only way to solve the problem and to release the connections is to restart the GlassFish instance. The problem occurs randomly. The strange thing is that this is a development server, used by only 5 people at the maximum. So I believe 20 connections should be more than enough, no?
          One developer can be enough to jam an instance. It is very reasonable to limit the number of connections and I wouldn't think about increasing that limit, because it won't solve your problem.
          The reason is more likely to be in the applications you have, or in the way your APEX developers work on them. A common mistake is not to communicate problems with an application function. If a certain page call/... produces a long running (hanging) query (or set of queries), a developer might tend to open a new window/tab or hit some non-reacting button again. Every client request (not every client!) will be serviced by the pool, even if the same client still has other requests pending (or even aborted on client side). In addition to restart your APEX Listener (and hence stop the database session) you could also "kill" the database session at database level.
          But I'd recommend to take a look at the 20 sessions your 5 people where keeping busy when you've hit the limit and try to find out which application/s is/are causing the hanging sessions.

          -Udo
          1 person found this helpful
          • 2. Re: All connections in the Universal Connection Pool are in use
            MatthiasHoys
            MatthiasHoys May 31, 2012 7:30 AM (in response to Udo)
            Ok, thanks for the feedback. I will check the sessions and SQL again the next time it happens. The strange thing is that we never had the problem when we were still working with Oracle's HTTP Server (Apache). It all started after we migrated to the APEX listener with GlassFish. One thing I was wondering: do you have to make some changes to the GlassFish connection pool to match the settings of the APEX listener?

            Matthias
            • 3. Re: All connections in the Universal Connection Pool are in use
              Udo
              Udo May 31, 2012 7:59 PM (in response to MatthiasHoys)
              The strange thing is that we never had the problem when we were still working with Oracle's HTTP Server (Apache).
              Could be a coincidence that a bug in an application appeared recently after migrating to APEX Listener.
              One thing I was wondering: do you have to make some changes to the GlassFish connection pool to match the settings of the APEX listener?
              No, APEX Listener doesn't use the GlassFish connection pool but brings its "own" (UCP). This is necessary to keep APEX Listener as independent from container (version) specific aspects as possible.

              -Udo