oracle 如何找出选择查询的最佳提取大小

How to figure out the optimal fetch size for the select query

提问by KKO

In JDBC the default fetch size is 10, but I guess that's not the best fetch size when I have a million rows. I understand that a fetch size too low reduces performance, but also if the fetch size is too high.

在 JDBC 中，默认提取大小为 10，但我想当我有一百万行时，这不是最佳提取大小。我知道提取大小太低会降低性能，如果提取大小太高也会降低性能。

How can I find the optimal size? And does this have an impact on the DB side, does it chew up a lot of memory?

如何找到最佳尺寸？而且这对DB端有影响吗，会不会占用大量内存？

采纳答案by Justin Cave

As with (almost) anything, the way to find the optimal size for a particular parameter is to benchmark the workload you're trying to optimize with different values of the parameter. In this case, you'd need to run your code with different fetch size settings, evaluate the results, and pick the optimal setting.

与（几乎）任何事情一样，为特定参数找到最佳大小的方法是使用不同的参数值对您尝试优化的工作负载进行基准测试。在这种情况下，您需要使用不同的提取大小设置运行代码、评估结果并选择最佳设置。

In the vast majority of cases, people pick a fetch size of 100 or 1000 and that turns out to be a reasonably optimal setting. The performance difference among values at that point are generally pretty minimal-- you would expect that most of the performance difference between runs was the result of normal random variation rather than being caused by changes in the fetch size. If you're trying to get the last iota of performance for a particular workload in a particular configuration, you can certainly do that analysis. For most folks, though, 100 or 1000 is good enough.

在绝大多数情况下，人们选择 100 或 1000 的提取大小，结果证明这是一个合理的最佳设置。那时值之间的性能差异通常非常小——您会认为运行之间的大部分性能差异是正常随机变化的结果，而不是由获取大小的变化引起的。如果您想获得特定配置中特定工作负载的最后一点性能，您当然可以进行该分析。不过，对于大多数人来说，100 或 1000 就足够了。

回答by Jean de Lavarene

If your rows are large then keep in mind that all the rows you fetch at once will have to be stored in the Java heap in the driver's internal buffers. In 12c, Oracle has VARCHAR(32k) columns, if you have 50 of those and they're full, that's 1,600,000 characters per row. Each character is 2 bytes in Java. So each row can take up to 3.2MB. If you're fetching rows 100 by 100 then you'll need 320MB of heap to store the data and that's just for one Statement. So you should only increase the row prefetch size for queries that fetch reasonably small rows (small in data size).

如果您的行很大，那么请记住，您一次获取的所有行都必须存储在驱动程序内部缓冲区的 Java 堆中。在 12c 中，Oracle 有 VARCHAR(32k) 列，如果其中有 50 个并且它们已满，则每行有 1,600,000 个字符。Java 中每个字符为 2 个字节。所以每行最多可以占用 3.2MB。如果您以 100 x 100 的比例获取行，那么您将需要 320MB 的堆来存储数据，而这仅适用于一个 Statement。因此，您应该只为获取相当小的行（数据大小较小）的查询增加行预取大小。

回答by przemek hertel

The default value of JDBC fetch size property is driver specificand for Oracle driver it is 10 indeed.

JDBC fetch size 属性的默认值是特定于驱动程序的，对于 Oracle 驱动程序，它确实是 10。

For some queries fetch size should be larger, for some smaller.

对于某些查询，获取大小应该更大，对于一些更小。

I think a good idea is to set some globalfetch size for whole project and overwriteit for some individual querieswhere it should be bigger.

我认为一个好主意是为整个项目设置一些全局获取大小，并为一些应该更大的单个查询覆盖它。

Look at this article:

看看这篇文章：

http://makejavafaster.blogspot.com/2015/06/jdbc-fetch-size-performance.html

http://makejavafaster.blogspot.com/2015/06/jdbc-fetch-size-performance.html

there is description on how to set up fetch size globally and overwrite it for carefully selected queries using different approaches: Hibernate, JPA, Spring jdbc templates or core jdbc API. And some simple benchmark for oracledatabase.

有关于如何全局设置获取大小并使用不同方法为精心选择的查询覆盖它的描述：Hibernate、JPA、Spring jdbc 模板或核心 jdbc API。以及一些简单的 oracle数据库基准测试。

As a rule of thumb you can:

根据经验，您可以：

• set fetchsize to 50 - 100as global setting
• set fetchsize to 100 - 500(or even more) for individual queries

• 将 fetchsize 设置为50 - 100作为全局设置
• 对于单个查询，将 fetchsize 设置为100 - 500（甚至更多）

回答by Nirmala

JDBC does have default prefetch size of 10. Check out OracleConnection.getDefaultRowPrefetch in JDBC Javadoc

JDBC 的默认预取大小为 10。查看JDBC Javadoc 中的OracleConnection.getDefaultRowPrefetch