Oracle data source connection pooling not working

Question: Lot of active unclosed physical connections with database even with connection pooling. Can someone tell me why is it so?

I configured the connection pool settings using oracle.jdbc.pool.OracleDataSource. However it seems the physical connections are not getting closed after use. I thought, Since it is connection pooling, the connections will be reused from the pool, so so many physical connections will not be made, but thats not what is happening now!

There are 100+ active physical connections in the database generating from the application [not from plsql developer or any such client tools], due to which it kicks off TNS error while trying to do write operations on database, where as read operations are fine even with large number of active connections.

Here is the Spring configuration,

<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
                                p:URL="${url}"
                                p:user="${username}"
                                p:password="${password}"
                                p:connectionCachingEnabled="true">
                                <property name="connectionProperties">
                                   <props merge="default">
                                      <prop key="AutoCommit">false</prop>
                                   </props>
                                </property>
</bean>

<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"
                                p:dataSource-ref="oracleDataSource" />

<bean id="transactionManager"
                                class="org.springframework.jdbc.datasource.DataSourceTransactionManager"
                                p:dataSource-ref="oracleDataSource">
</bean>

The SQL that returned the 100+ active connections is ,

select username, terminal,schemaname, osuser,program from v$session where username = 'grduser'

回答1:

You should configure connection cache, the default value of max connections for implicit connection cache is the max number of database sessions configured for the database.

回答2:

Thanks to @Evgeniy Dorofeev.

Solution in detail :

The connectionCache was enabled, but the properties were not set. Set the properties as written below,

<bean id="oracleDataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close"
                                        p:URL="${url}"
                                        p:user="${username}"
                                        p:password="${password}"
                                        p:connectionCachingEnabled="true">
                                        <property name="connectionProperties">
                                           <props merge="default">
                                              <prop key="AutoCommit">false</prop>
                                           </props>
                                        </property>
         <property name="connectionCacheProperties">
                    <props>
                        <prop key="MinLimit">5</prop>
                        <prop key="MaxLimit">10</prop>
                        <prop key="InactivityTimeout">2</prop>
                    </props>
                </property>
     </bean>

Now, for every operation in the application that requires a connection, it will try to get from the pool if available and ready to use, but is guaranteed that the database will have maximum of only 10 active physical connections. Any attempt to get a extra physical connection will lead to a database error at the application side.

Even if you have set the connectionCache, make sure your application is not explicitly trying to get a connection, like

Connection connection = getJdbcTemplate().getDataSource().getConnection();

This is alarming, JDBCTemplate doesnt manage the closing of this connection. Hence you have to close by yourself after use, otherwise the physical connection will still be active and unclosed even after use. So next time you call this again, it try to get a new physical connection , and remain unclosed, resulting in piling up of active connections until the maxLimit is reached.

The connection might be explicity needed when you want to pass it as a parameter to some other function, say in the case of an ArrayDescriptor [if you talk to PLSQL Stored procedures that has IN parameter to accept an array of values , an array of Varchar or array of RAW]. If you need to create a ArrayDescriptor,

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor(
                           "SOME_TYPE_NAME", connection );
ARRAY SQLArray= new ARRAY(arrayDescriptor, connection , arrayString);

Hence do a connection.close() explicity here.

Additional info:

Connection connection = getJdbcTemplate().getDataSource().getConnection()

This attempts to establish a connection with the data source that this DataSource object represents.

Calling this line of code - once, will attempt to establish a new connection. Calling again, will establish a second connection. For each request, it will create a new connection!.So If your maxLimit is 10, Until there are 10 active physical connections in the database, the call will be successful, but note that all the connections are active [not closed].

So lets say now there are 10 active db connections, as maxLimit is set to 10.

So any requests that requires a database operation, that would go through the normal route of accessing a connection via the JDBCTemplate will be picking up the already established connection [from the 10 connections]

However any request that calls this code getJdbcTemplate().getDataSource().getConnection() to access a connection will attempt to establish a NEW connection, and will fail, resulting in exception.

The only way to resolve this is to explicitly close the connection when we explicitly create the connection. ie calling connection.close() When we don't explicitly create the connection, and when it is managed by Spring, then Spring will take care of closing the connections too. In the case of using Oracle Data Source pooling along with JDBCTemplate, closing the connection[returning the connections to the pool] is managed by Spring.