Oracle JDBC prefetch: how to avoid running out of RAM
Using Oracle java JDBC (ojdbc6 11.2.0.4), loading a query with many rows takes forever (high latency environment. This is apparently the default prefetch in Oracle JDBC is default size "10" which requires a round trip time once per 10 rows. I am attempting to set an aggressive prefetch size to avoid this.
PreparedStatement stmt = conn.prepareStatement("select * from tablename");
statement.setFetchSize(10000);
ResultSet rs = statement.executeQuery();
This can work, but instead I get an out of memory exception. I had presumed that setFetchSize would tell it to buffer "that many rows" as they come in, using as much RAM as each row requires. If I run with 50 threads, even with 16G of -XMX space, it runs out of memory. Feels almost like a leak:
Exception in thread "main" java.lang.OutOfMemoryError: Java heap space
at java.lang.reflect.Array.newArray(Native Method)
at java.lang.reflect.Array.newInstance(Array.java:70)
at oracle.jdbc.driver.BufferCache.get(BufferCache.java:226)
at oracle.jdbc.driver.PhysicalConnection.getCharBuffer(PhysicalConnection.java:7422)
at oracle.jdbc.driver.OracleStatement.prepareAccessors(OracleStatement.java:983)
at oracle.jdbc.driver.T4CTTIdcb.receiveCommon(T4CTTIdcb.java:273)
at oracle.jdbc.driver.T4CTTIdcb.receive(T4CTTIdcb.java:144)
at oracle.jdbc.driver.T4C8Oall.readDCB(T4C8Oall.java:771)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:346)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:861)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1145)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3493)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
....
What can I do to still get prefetch but not run out of RAM? What is going on?
The closest related item on SO is this: http://stackoverflow.com/a/14317881/32453
Basically, oracle's default strategy for later ojdbc jars is to "pre allocate" an array per "prefetch" row that accomodates for the largest size conceivably possible to return from that query. So in my case I had some VARCHAR2(4000) in there, so 50 threads * 3 columns of varchar2's * 4000 was adding up to more than gigabytes of RAM [yikes]. There does not appear to be an option to say "don't pre allocate that array, just use the size needed." Ojdbc even keeps these preallocated buffers around between preparedstatements so it can reuse them. Definitely a memory hog.
The fix was to determine the maximum actual column size, then replace the query with (assuming 50 is the max size) select substr(column_name, 0, 50)
as well as profile and only use as high of setFetchSize as actually made significant speed
ments.
Other things you can do: decrease the number of prefetch rows, increase Xmx parameter, only select the columns you need.
Once we were able to use at least prefetch 400 [make sure to profile to see what numbers are good for you, with high latency we saw
ments up to prefetch size 3-4K] on all queries, performance
d dramatically.
I suppose if you wanted to be really aggressive against sparse "really long" rows you might be able to re-query when you run into these [rare] rows.
Details ad nauseum here
最新还有个解决方法,使用ojdbc8,已经是按需分配模式。
最新文章
- UGUI全面实践教程
- 检测到在集成的托管管道模式下不适用的ASP.NET设置的解决方法(转)
- 初识ReactJs(一)
- c#实现远程操作svn
- 每天一个linux命令(14):head 命令
- 使用migrate.exe执行EF code first 迁移
- Python网络编程(4)——异步编程select &; epoll
- Objective-C的内存管理
- 1.6.7 Detecting Languages During Indexing
- Spring AOP 性能监控器
- POJ 1556 - The Doors 线段相交不含端点
- 在iOS当中发送电子邮件和短信
- Linux下Modules的概念及使用详解[转贴]
- 开始翻译Disruptor
- 给eclipse设置自动补全的步骤
- PythonStudy——列表的常用操作 List of common operations
- NModbus类库使用
- Javascript扩展Date的prototype实现时间format函数 2017-06-29T09:10:00.000Z日期转换
- OSI 七层和五层
- Selenium自动化测试Python四:WebDriver封装