4

We have a simple select query with only single column in SELECT clause. No WHERE clause condition (cause application demands this). Query fetches around 5000000 rows at a time. DBAs sometimes see a increased load on the database (Oracle 12c) and they have identified this select query as the cause behind DB load. Index has been created properly on this single column. Query returns results within short time.

My Java (Java 8) application sets fetch size (org.hibernate.fetchSize) as 5000. It is to improve the query performance. My Java app server has good memory config, so we don't see any issue on the application server side.

I am wondering if setting higher fetch size on the application side would load the database somehow. We are planning to lower the fetch size on the application side and monitor the DB performance, but I though of checking with you all first.

I referred to this memory management white paper from Oracle, but it doesn't have anything helpful related to DB load: https://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

Please let me know your thoughts.

Improve this question
6
  • 2
    The higher the fetch-size, the less roundtrips the jdbc driver needs to do to get all the data. So lowering it will decrease performance, especially with a slow network connection.
    – M. Deinum
    Commented Nov 18, 2021 at 13:08
  • Do you mean, lowering the fetch size will decrease the DB performance? @M.Deinum
    – user613114
    Commented Nov 18, 2021 at 13:11
  • No it decreases your application performance. Because more roundtrips over the network to the datbase need to be made. If your network is slow/high latency etc. that will be quite noticable. You need to find the sweetspot between the size of your fetch-size and memory usage (if you have spikes you can have excessive GC again).
    – M. Deinum
    Commented Nov 18, 2021 at 13:13
  • 4
    Too large fetch sizes can also cause an application to wait longer for more rows, effectively reducing throughput. So just a blind "increase fetch size == better performance" is not necessarily true.
    – Mark Rotteveel
    Commented Nov 18, 2021 at 13:15
  • 1
    @MarkRotteveel true, hence you need to test and verify (with a reasonable dataset ofcourse). I have seen too many applications just using the default (which generally is 10) and wondering why the performance drops with large resultsets...
    – M. Deinum
    Commented Nov 18, 2021 at 13:20

1 Answer 1

Reset to default
2

Setting the JDBC fetch size higher will almost certainly not cause database performance problems. Before you consider lowering the value, you should ask your DBA for clarification on exactly how your query is causing database load.

Oracle does not experience result-set size problems in the same way as an application. While an application might run out of memory storing a million rows in memory, most Oracle systems can easily handle that amount of data because large intermediate results are written to disk in the temporary tablespace. And while there are occasionally temporary tablespace problems, those problems happen based on the size of the intermediate results, not the size of the results fetched per batch.

Ask your DBA for more information about the query and maybe some query tuning. What kind of problems is the query causing, what are the database waits for the query, what's the execution plan/SQL Monitor report, etc. Fetching 5,000,000 rows at a time for an application is a bit unusual, and you may have to adjust some of your expectations. For example, there's a good chance that you don't want to use an index for a query that returns 5 million rows since full table scans are more efficient for retrieving a large portion of a table.

Improve this answer
2
  • If a table scan is more efficient shouldn't the optimizer be able to figure that out for itself?
    – Nathan Hughes
    Commented Nov 21, 2021 at 3:56
  • @NathanHughes The optimizer can usually figure it out, but there are always exceptions. Like if the table statistics aren't gathered, or there are weird conditions that look like they would return few rows but actually return many rows, or if someone mistakenly thought full table scans are always bad and used an index hint to force the plan.
    – Jon Heller
    Commented Nov 21, 2021 at 17:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.