OracleDataSource vs. Oracle UCP PoolDataSource

Question

Latest Oracle jdbc driver (11.2.0.1.0) explicit states that Oracle Implicit Connection cache (which is that one that use OracleDataSource) it's deprecated :

Oracle JDBC Drivers release 11.2.0.1.0 production Readme.txt

What Is New In This Release ?

Universal Connection Pool In this release the Oracle Implicit Connection Cache feature is deprecated. Users are strongly encouraged to use the new Universal Connection Pool instead. The UCP has all of the features of the ICC, plus much more. The UCP is available in a separate jar file, ucp.jar.

So I think it's better to start using UCP, but the documentation it's not that good. For example I didn't find a way to use UCP with spring...

UPDATE: I've found the correct spring configuration: OK I think I've found the right configuration:

<bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory" factory-method="getPoolDataSource">
    <property name="URL" value="jdbc:oracle:thin:@myserver:1521:mysid" />
    <property name="user" value="myuser" />
    <property name="password" value="mypassword" />
    <property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
    <property name="connectionPoolName" value="ANAG_POOL" />
    <property name="minPoolSize" value="5" />
    <property name="maxPoolSize" value="10" />
    <property name="initialPoolSize" value="5" />
    <property name="inactiveConnectionTimeout" value="120" />
    <property name="validateConnectionOnBorrow" value="true" />
    <property name="maxStatements" value="10" />
</bean>

The key is to specify the right factory class and the right factory method

PDS is 'universal' as it provides the same level of pooling functionality you get in ODS for non-Oracle databases, e.g. MySQL.

See UCP Dev Guide, an article on Oracle website and UCP Transition Guide

I don't see any immediate benefit of moving to UCP (PDS) from ODS, but perhaps in the future Oracle will deprecate some of the functionality in ODS. I used ODS for a while and I'm quite happy with it for the time being, but if I started fresh I'd go with PDS.

I did an extensive evaluation of UCP and decided to NOT use UCP - please have a look at this post for details.

I tested the UCP and deployed it to production in a Spring 3.0.5 Hibernate app using Spring JMS listener containers and Spring-managed sessions and transactions using the @Transactional annotation. The data sometimes causes SQL constraint errors, due to separate listener threads trying to update the same record. When that happens, the exception is thrown by one method annotated by @Transactional and the error is logged into the database using another method annotated by @Transactional. For whatever reason, this process seems to result in a cursor leak, that eventually adds up and triggers the ORA-01000 open cursor limit exceeded error, causing the thread to cease processing anything.

OracleDataSource running in the same code doesn't seem to leak cursors, so it doesn't cause this problem.

This is a pretty weird scenario, but it indicates to me that it's a little too early to be using the UCP in an application with this kind of structure.

I too am testing UCP and am finding myself that I am having performance issues in a Thread Pool based application. Initially, I tried OracleDataSource, but am having trouble configuring it for batch processing. I keep getting NullPointerExceptions in the connections, leading me to believe I have some sort connection leak, but only with some application, there are other applications we manage that are not batch process oriented that OracleDataSource works well.

Based on this post and a few others I found researching this subject, I tried UCP. I found that with enough tweaking, I could get rid of closed connections/NullPointerExceptions on connections style errors, but Garbage Collection was taking a beating. Long-Term GC fills up fast and does not ever seem to free up until the application finishes running. This can sometimes take as long as a day or more if the load is really heavy. I also notice that it takes progressive longer to process data as well. I compare that to the now depreciated OracleCacheImpl class (that we currently use in production because it still "just works"), where it used a third of the GC memory that UCP does and processes files much faster. In all other applications UCP seems to work just fine and handles just about everything I throw at it, but the Thread Pool Application is a major app and I could not risk GC Exceptions in production.

The implicit connection caching performs quite a bit better than UCP if you use the connection validation. This corresponds to bug 16723836, which is scheduled to be fixed in 12.1.0.2.

UCP pooling becomes increasingly more expensive to get/return connections as the concurrent load increases. The test compares the oracle implicit connection caching, tomcat's pooling, and UCP. All 3 are configured to allow a max of 200 connections, a minimum of 20 connections and an initial size of 2. All 3 are configured to validate the connections as they are removed from the pool. The tomcat pool uses the statement "select sysdate from dual" for validation.

These results on a 64bit RedHat node with 64 logical cores (32 physical) and 128 GB of ram.

At 5 concurrent threads, UCP is the slowest, but total connection management time (get and close) is under 1 ms on average. As the concurrency is increased, UCP falls further and further behind the other solutions:

25 Threads:
Implicit: 0.58ms
Tomcat: 0.92ms
UCP: 1.50ms

50 Threads:
Implicit: 0.92ms
Tomcat: 1.60ms
UCP: 6.80ms

100 Threads:
Implicit: 2.60ms
Tomcat: 3.20ms
UCP: 21.40ms

180 Threads:
Implicit: 13.86ms
Tomcat: 15.34ms
UCP: 40.70ms