Hibernate 存储过程调用导致 OutOfMemory
数据库是 Oracle 11g
我正在使用 Hibernate 的命名查询来执行一个存储过程，返回一个非常大的数据集（超过 200 万行），例如 ： Query query = session.getNamedQuery(procName);
我从hibernate文档中知道，你不能使用setFirstResult/setMaxResult，如上所述 http://docs.jboss.org/hibernate/core /3.3/reference/en/html/querysql.html。
对于 100,000 行这样的较小数据集，一切都很好。然而，一旦我用 1,000,000 进行测试，我就会收到 OutOfMemory 错误。
从查询对象中，我得到一个 listIterator。我假设数据只获取一次，我迭代列表迭代器（query.list().listIterator()）
我没有配置二级缓存。 在处理 Oracle 存储过程时，这些设置是否有帮助？
query.setCacheMode(org.hibernate.CacheMode.IGNORE); query.setFetchSize(1000);
query.scroll(org.hibernate.ScrollMode.FORWARD_ONLY);
基本上，如何使用 Hibernate 中的存储过程管理大型数据集检索。
万分感谢
I am using Hibernate's named Query to execute a stored procedure returning a very large dataset ( over 2 million rows ) The DB is Oracle 11g
for instance:Query query = session.getNamedQuery(procName);
I know from the hibernate documentation, you cannot use setFirstResult/setMaxResult as stated on
http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html.
All is well, on a smaller dataset like 100,000 rows. However, as soon as I test with a 1,000,000 I get the OutOfMemory error.
From the query object, I get a listIterator. I assumed that the data was only fetched once, I iterate over the listiterator ( query.list().listIterator())
I do not have a 2nd level cache configured.
Will these settings help at all, when dealing with an Oracle Stored Procedure.
query.setCacheMode(org.hibernate.CacheMode.IGNORE);
query.setFetchSize(1000);
query.scroll(org.hibernate.ScrollMode.FORWARD_ONLY);
Basically, how do I manage large dataset retrieval using stored procs in Hibernate.
Million thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这完全取决于您如何处理从查询返回的数据。如果您将其视为典型的查询（即对其调用 list() 并将结果存储在集合中），那么您将因这么多行而突破内存限制。关键是批量获取并处理它们。我相信使用 Hibernate 执行此操作的典型方法是将结果作为 ScrollableResults 并记住 flush() 和 < a href="http://docs.jboss.org/hibernate/core/3.5/javadocs/org/hibernate/Session.html#clear%28%29" rel="nofollow">clear() 定期清除会话，因为它充当一级缓存并将存储所有正在加载的对象。四处寻找 Hibernate 中批处理的描述，但要小心不要与 Hibernate 的 批量获取及相关概念。它们是两种不同的动物。
It depends entirely on how you handle the data returned from the query. If you treat it as a typical query--that is, call list() on it and store the result in a Collection--then you're going to bust your memory limit with that many rows. The key is to get and process them in batches. I believe the typical way of doing so with Hibernate is getting the results as a ScrollableResults and remembering to flush() and clear() the session periodically since it acts as the first-level cache and will store all the objects being loaded. Look around for descriptions of batch processing in Hibernate, but be careful not to get confused with Hibernate's batch fetching and related concepts. They're two different animals.
您的问题与其说是 Hibernate 的问题，不如说是内存使用问题的问题。我也遇到过类似的情况，JMS 消息在处理少量数据时运行良好，但随着数据量增加，一切都崩溃了。由于我怀疑您一次需要内存中的所有 2mm 记录，请查看是否可以更新存储过程以获取限制和偏移量，以便它可以返回数据切片而不是仅返回整个数据集。
Your issue is less one of Hibernate than one of memory usage. I had a similar situation where a JMS message worked fine with a small amount of data, then everything blew up with more. Since I doubt you need all 2mm records in memory at once, see if you can get the stored proc updated to take a limit and an offset so that it can return slices of data as opposed to only the whole dataset.