OracleDataSource 与 Oracle UCP PoolDataSource
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1427890/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
OracleDataSource vs. Oracle UCP PoolDataSource
提问by Gandalf
I was researching some JDBC Oracle Connection Pooling items and came across a new(er) Oracle Pool implementation called Universal Connection Pool (UCP). Now this uses a new class, PoolDataSource, for connection pooling rather then the OracleDataSource [with the cache option enabled]. I am debating whether to switch to this new implementation but can't find any good documentation of what (if any) fixes/upgrades this would buy me. Anyone have an experience with both? Pluses/Minuses? Thanks.
我正在研究一些 JDBC Oracle 连接池项目，并遇到了一个新的（更）Oracle 池实现，称为通用连接池 (UCP)。现在它使用一个新类 PoolDataSource 用于连接池，而不是 OracleDataSource [启用缓存选项]。我正在争论是否要切换到这个新的实现，但找不到任何好的文档来说明这会给我带来什么（如果有的话）修复/升级。任何人都有这两者的经验？优点/缺点？谢谢。
采纳答案by Gandalf
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 驱动程序 (11.2.0.1.0) 明确指出 Oracle 隐式连接缓存（即使用 OracleDataSource 的缓存）已弃用：
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.
Oracle JDBC 驱动程序 11.2.0.1.0 版生产 Readme.txt
此版本有哪些新功能？
通用连接池 在此版本中，不推荐使用 Oracle 隐式连接缓存特性。强烈建议用户改用新的通用连接池。UCP 具有 ICC 的所有功能，以及更多功能。UCP 在单独的 jar 文件 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...
所以我认为最好开始使用UCP，但文档不是那么好。例如，我没有找到将 UCP 与 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
关键是指定正确的工厂类和正确的工厂方法
回答by Andrew not the Saint
PDS is 'universal' as it provides the same level of pooling functionality you get in ODS for non-Oracle databases, e.g. MySQL.
PDS 是“通用的”，因为它为非 Oracle 数据库（例如 MySQL）提供了与 ODS 中相同级别的池功能。
See UCP Dev Guide, an article on Oracle websiteand UCP Transition Guide
请参阅UCP 开发指南，Oracle 网站上的一篇文章和UCP 转换指南
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.
我没有看到从 ODS 迁移到 UCP (PDS) 的任何直接好处，但也许将来 Oracle 会弃用 ODS 中的某些功能。我使用了 ODS 一段时间，目前我对它很满意，但如果我重新开始，我会选择 PDS。
回答by MRalwasser
I did an extensive evaluation of UCP and decided to NOT use UCP - please have a look at this postfor details.
我对 UCP 进行了广泛的评估，并决定不使用 UCP -请查看这篇文章了解详细信息。
回答by Wm. Bainbridge
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.
我使用 Spring JMS 侦听器容器和 Spring 管理的会话和使用 @Transactional 批注的事务测试了 UCP 并在 Spring 3.0.5 Hibernate 应用程序中将其部署到生产环境中。由于单独的侦听器线程尝试更新同一记录，数据有时会导致 SQL 约束错误。发生这种情况时，由@Transactional 注释的一个方法抛出异常，并使用@Transactional 注释的另一种方法将错误记录到数据库中。无论出于何种原因，此过程似乎导致游标泄漏，最终累积并触发 ORA-01000 打开游标限制超出错误，导致线程停止处理任何内容。
OracleDataSource running in the same code doesn't seem to leak cursors, so it doesn't cause this problem.
在相同代码中运行的 OracleDataSource 似乎不会泄漏游标，因此不会导致此问题。
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.
这是一个非常奇怪的场景，但它向我表明在具有这种结构的应用程序中使用 UCP 还为时过早。
回答by JavaDev03
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.
我也在测试 UCP 并发现自己在基于线程池的应用程序中遇到了性能问题。最初，我尝试了 OracleDataSource，但在将其配置为批处理时遇到了问题。我一直在连接中收到 NullPointerExceptions，这让我相信我有某种连接泄漏，但仅对于某些应用程序，我们管理的其他应用程序不是面向批处理的，OracleDataSource 运行良好。
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.
基于这篇文章和我发现的其他一些研究这个主题的文章，我尝试了 UCP。我发现通过足够的调整，我可以摆脱连接样式错误上的关闭连接/NullPointerExceptions，但是垃圾收集受到了打击。长期 GC 会很快填满，并且在应用程序完成运行之前似乎永远不会释放。如果负载真的很重，这有时可能需要长达一天或更长时间。我还注意到，处理数据也需要更长的时间。我将它与现在已贬值的 OracleCacheImpl 类（我们目前在生产中使用，因为它仍然“正常工作”）进行比较，在那里它使用了 UCP 所做的三分之一的 GC 内存，并且处理文件的速度要快得多。在所有其他应用程序中，UCP 似乎工作得很好，几乎可以处理我扔给它的所有东西，
回答by Brett Okken
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 好很多。这对应于错误 16723836，计划在 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.
随着并发负载的增加，UCP 池获取/返回连接的成本越来越高。测试对比oracle隐式连接缓存、tomcat的池化、UCP。所有 3 个都配置为允许最多 200 个连接，最少 20 个连接和初始大小为 2。所有 3 个都配置为在从池中删除连接时验证连接。tomcat 池使用语句“select sysdate from dual”进行验证。
These results on a 64bit RedHat node with 64 logical cores (32 physical) and 128 GB of ram.
这些结果在具有 64 个逻辑内核（32 个物理内核）和 128 GB 内存的 64 位 RedHat 节点上运行。
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:
在 5 个并发线程时，UCP 最慢，但总连接管理时间（获取和关闭）平均不到 1 毫秒。随着并发性的增加，UCP 越来越落后于其他解决方案：
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
回答by Montanita
I tried ucp and the performance is better... May be the key is using this
我尝试了 ucp 并且性能更好......可能是关键是使用这个
oracle.ucp.jdbc.PoolDataSource ds = (oracle.ucp.jdbc.PoolDataSource)envContext.lookup(url_r);
MyConnectionLabelingCallback callback = new MyConnectionLabelingCallback();
ds.registerConnectionLabelingCallback( callback );
Properties label = new Properties();
label.setProperty(pname, KEY);
conn = ds.getConnection(label);
This helps to borrow the connection and never closing it.. so the performance is great
这有助于借用连接并且永不关闭它..所以性能很棒
The code for the callback class is
回调类的代码是
public class MyConnectionLabelingCallback
implements ConnectionLabelingCallback {
public MyConnectionLabelingCallback()
{
}
public int cost(Properties reqLabels, Properties currentLabels)
{
// Case 1: exact match
if (reqLabels.equals(currentLabels))
{
System.out.println("## Exact match found!! ##");
return 0;
}
// Case 2: some labels match with no unmatched labels
String iso1 = (String) reqLabels.get("TRANSACTION_ISOLATION");
String iso2 = (String) currentLabels.get("TRANSACTION_ISOLATION");
boolean match =
(iso1 != null && iso2 != null && iso1.equalsIgnoreCase(iso2));
Set rKeys = reqLabels.keySet();
Set cKeys = currentLabels.keySet();
if (match && rKeys.containsAll(cKeys))
{
System.out.println("## Partial match found!! ##");
return 10;
}
// No label matches to application's preference.
// Do not choose this connection.
System.out.println("## No match found!! ##");
return Integer.MAX_VALUE;
}
public boolean configure(Properties reqLabels, Object conn)
{
System.out.println("Configure################");
try
{
String isoStr = (String) reqLabels.get("TRANSACTION_ISOLATION");
((Connection)conn).setTransactionIsolation(Integer.valueOf(isoStr));
LabelableConnection lconn = (LabelableConnection) conn;
// Find the unmatched labels on this connection
Properties unmatchedLabels =
lconn.getUnmatchedConnectionLabels(reqLabels);
// Apply each label <key,value> in unmatchedLabels to conn
for (Map.Entry<Object, Object> label : unmatchedLabels.entrySet())
{
String key = (String) label.getKey();
String value = (String) label.getValue();
lconn.applyConnectionLabel(key, value);
}
}
catch (Exception exc)
{
return false;
}
return true;
}
}
回答by Az.MaYo
There are two possible ways to use UCP in Spring Bean.xml.
在 Spring Bean.xml 中使用 UCP 有两种可能的方法。
For db.properties set by some file then load this then Use one of them:
对于由某个文件设置的 db.properties 然后加载它然后使用其中之一：
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location">
<value>classpath:resources/db.properties</value>
</property>
</bean>
First one wiht oracle.ucp.jdbc.PoolDataSourceImpl :-
第一个使用 oracle.ucp.jdbc.PoolDataSourceImpl :-
<bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceImpl">
<property name="URL" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="validateConnectionOnBorrow" value="true"/>
<property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
<property name="connectionPoolName" value="TEST_POOL" />
<property name="minPoolSize" value="10" />
<property name="maxPoolSize" value="20" />
<property name="initialPoolSize" value="12" />
</bean>
Second one with oracle.ucp.jdbc.PoolDataSourceFactory :-
第二个与 oracle.ucp.jdbc.PoolDataSourceFactory :-
<bean id="dataSource" class="oracle.ucp.jdbc.PoolDataSourceFactory"
factory-method="getPoolDataSource">
<property name="URL" value="${jdbc.url}" />
<property name="user" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
<property name="validateConnectionOnBorrow" value="true"/>
<property name="connectionFactoryClassName" value="oracle.jdbc.pool.OracleDataSource" />
<property name="connectionPoolName" value="TEST_POOL" />
<property name="minPoolSize" value="10" />
<property name="maxPoolSize" value="20" />
<property name="initialPoolSize" value="12" />
</bean>
That's It :) Here is the link for Detail Documentation : https://docs.oracle.com/cd/E11882_01/java.112/e12265/connect.htm#CHDDCICA
就是这样:) 这是详细文档的链接：https: //docs.oracle.com/cd/E11882_01/java.112/e12265/connect.htm#CHDDCICA
