0

I am writing an application which use oracle UCP for database connection pooling. Initially i set the maximum size to 50. But assuming all the connections are going to be acquired at a given time, how can i avoid the below exception when the applications tries to get another db connection and increase the size of the UCP automatically?

Caused by: java.sql.SQLException: Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: All connections in the Universal Connection Pool are in use

tharuwa_st
  • 1
  • 3
  • 1
    The purpose of setting the **maximum** pool size is to constraint the total number of connections to that value. If you need a higher number of simultaneous connections you need to increase the value of `MaxPoolSize`. Alternatively you need to look at better harvesting of inactive sessions, fiercer timeout values, etc. – APC Mar 03 '19 at 17:06
  • Perhaps you have confused the meaning of `MaxPoolSize` with `InitialPoolSize`?If so, you should be executing `setInitialPoolSize(50)` and `setMaxPoolSize(200)` (or whatever). – APC Mar 03 '19 at 17:09
  • So does that mean if i use setInitialPoolSize(50) to and setMaxPoolSize(200) it will create new connections whenever borrowed connection count exceeds 50? What will happen if it exceeds 200? – tharuwa_st Mar 08 '19 at 04:34
  • *"it will create new connections whenever borrowed connection count exceeds 50"*. Yes. *" What will happen if it exceeds 200?"* `Exception occurred while getting connection...All connections in the Universal Connection Pool are in use`. – APC Mar 08 '19 at 07:20
  • You seem to be struggling with the concept of **maximum** pool size. We set a maximum limit to prevent our system being swamped by too many concurrent users. If we have more users than our system can handle comfortably all our users will suffer a degraded service, or in the worst case the system will freeze. You need to consider the total resources of your system, establish how much an average user will consume, then set the `MaxPoolSize` appropriately. – APC Mar 08 '19 at 07:24
  • Having said which, the official Oracle advice is to use static connection pools. That is, `setInitialPoolSize(50)` and `setMaxPoolSize(50)` (or whatever value your system can support). This avoids the risk of connection storms. [Find out more](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adfns/connection_strategies.html#GUID-1B9A21E9-B8E6-4F75-AC9D-1F5D13A1F6F9) – APC Mar 08 '19 at 07:27
  • Thank you @APC for the information. I have used connection live timeout and max pool size to gracefully handle the DB connections within my application. – tharuwa_st Mar 15 '19 at 02:59

0 Answers0