Oracle UCP pool connections limit exceeded

Question

My application listens to few tables using Oracle UCP. In production environment application works fine, but In test environment, Application runs out of connection s from pool. I am closing and returning used connections in every query as well. Please refer the Connection close method.

public static synchronized void closeConnection(Connection con) throws Exception {
    String print;
    try {
        if (con != null && !con.isClosed()) {
            con.close();
            con =null;
            Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
            Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
            Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;      
        } else {
            Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
            Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
            Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;              
        }
    } catch (Exception e) {
        Logger.errorLog(e);
    } finally {
        Config.DB_POOL_COUNT_AVAILABLE = pds.getAvailableConnectionsCount();
        Config.DB_POOL_COUNT_BUSSY = pds.getBorrowedConnectionsCount();
        Config.DB_POOL_COUNT_OPENED = Config.DB_POOL_COUNT_AVAILABLE + Config.DB_POOL_COUNT_BUSSY;  
        if(con != null && !con.isClosed()){
            con.close();
            con=null;
        } 
    }       
}

What is your connection pool? Hikari/Apache?what is its configuration? What are the diffs between dev and prod? — user7294900
It's oracle.ucp.jdbc.PoolDataSource Connection. max,min,initial pool sizes are accordingly 30,20,25. Only diff is prod and test environments are separated server. two application servers, with two DB servers. — jayark

Do Nhu Vy Do Nhu Vy · Accepted Answer · 2018-11-07T03:42:58

Because your application works fine in production environment, you don't need concern about connection pool too much. In development environment, closing connection after query is not bad.

For good practice with Oracle UCP you can see at https://docs.oracle.com/cd/E18283_01/java.112/e12265/optimize.htm or

https://docs.oracle.com/en/database/oracle/oracle-database/18/jjucp/optimizing-ucp-behavior.html

Oracle UCP pool connections limit exceeded

1 Answers