Oracle JDBC row prefetch driver tuning

defaultRowPrefetch - almost an easy win

If you work in a large enterprise context, sooner or later you will connect to an Oracle database.

I wanted to share a tip on tuning the Oracle row prefetch settings. The default prefetch value is 10, which means the JDBC client will do one round-trip to the database to fetch 10 rows, rather than round-trip to the DB for every row fetch. To reduce the round-trips over the network, you can increase the row prefetch, which is especially effective if you are extracting large row volumes.

This is a common trick provided to increase JDBC performance especially where there will be a large ResultSet returned. The row prefetch can be set in two ways -

• On the Statement object, call the setFetchSize before executing a query. This works if you are using JDBC directly.
• Alternatively, you can set a default fetch size when the setFetchSize method cannot be called. On the connection properties object, use the defaultRowPrefetch value and set it to an int.

So an easy way to automatically improve your application performance is to increase the fetch size. Great.

Except for the caveat

Oracle JDBC client seems to pre-initialise some memory structures to hold the full prefetch size. So, if you set a prefetch size of 500, you allocate 50x as much memory than if you had prefetch size = 10. This is a huge extra demand on GC especially if you are not actually reading those rows. To think, you might be running a GC 50x more often than needed if you normally only fetch a few rows; this will be a big impact on your application responsiveness.

Recommendation

If possible, I recommend using the setFetchSize on a per-query basis. For example, if you know a particular query will only ever return a few rows, then set the fetch size to say 5. If you know a query will return 1000 rows, use a fetch size of 100.

As a heuristic, there are limited benefits from going over 50-100.