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.