ScrollableResults fetch size on a Hibernate 6 StatelessSession: hint or guarantee?

Answer to the three core questions

1. Is the per‑query setFetchSize() guaranteed to reach the JDBC Statement?
   Confirmed: In Hibernate 6 the call to Query.setFetchSize(int) is executed before the PreparedStatement is created. The Dialect’s prepareStatement helper passes that value unchanged to the JDBC driver. However, the value is a *hint*; Hibernate does not enforce it. A dialect or a JDBC wrapper can decide to ignore or adjust it (e.g. OracleDialect only applies it when the value is >0, PostgreSQLDialect applies it only when autocommit is false).
   So, while the value does reach the driver, it is not a hard guarantee that the driver will honour it.
2. Does the ScrollMode affect fetchSize behavior on PostgreSQL vs Oracle?
   Confirmed: ScrollMode.FORWARD_ONLY maps to ResultSet.TYPE_FORWARD_ONLY, ScrollMode.SCROLL_INSENSITIVE to ResultSet.TYPE_SCROLL_INSENSITIVE. The fetchSize hint is set on the PreparedStatement in both cases.
   For PostgreSQL, forward‑only streaming works only when autocommit is off; using SCROLL_INSENSITIVE can cause the driver to buffer rows, making the hint ineffective.
   Oracle honours the hint for both scroll modes, treating it as a row‑prefetch limit.
3. How to confirm the effective fetchSize without driver‑level tracing?
   Confirmed: Hibernate exposes the value it passes via the org.hibernate.resource.jdbc.spi.StatementInspector API. Register a custom inspector that logs statement.getFetchSize() after preparation. Alternatively, wrap the DataSource with a proxy such as p6spy or FlexyPool and look for the last PreparedStatement.setFetchSize call before executeQuery(). The logged value is the hint Hibernate sent to the driver.

Practical steps for a PostgreSQL + Oracle stream

1. Ensure autocommit is off for PostgreSQL – start a transaction before creating the StatelessSession.
2. Set the fetch size on the query:

   ScrollResult sr = session.scroll("select …", ScrollMode.FORWARD_ONLY)
       .setFetchSize(10_000);
   

3. Register a StatementInspector to log the value:

   configuration.addStatementInspector(new StatementInspector() {
       @Override
       public String inspect(String sql) { return sql; }
       @Override
       public void afterPrepare(PreparedStatement ps) {
           System.out.println("Hibernate sent fetchSize=" + ps.getFetchSize());
       }
   });
   

4. Run the scroll and observe heap usage; a flat profile indicates the fetchSize hint was respected.

What to check if things don’t behave as expected

• Is autocommit truly disabled when you start the transaction?
• Did a dialect override the hint (e.g. OracleDialect’s >0 check)?
• Is the driver configured to buffer rows (e.g. PostgreSQL’s prepareThreshold or Oracle’s prefetch settings)?

Summary

Hibernate 6 will pass the fetchSize hint to the JDBC driver, but it is not a guarantee of how the driver behaves. The scroll mode only influences the ResultSet type; fetchSize handling is dialect‑specific. Use a StatementInspector or a JDBC proxy to verify the value that actually reaches the driver.