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.
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.