Improving Oracle LOB Performance

If you’re using LOBs with Oracle and want to trade memory footprint for performance there are two options you have.

On the database side if you’re frequently reading and writing LOBs you can enable LOB caching this causes them to be placed in the buffer cache like any other row data. This can improve performance by reading LOBs directly from memory rather than storage.

On the client (JDBC) side you can increase LOB prefetching by setting the oracle.jdbc.defaultLobPrefetchSize connection property. This can improve read performance by reducing the number of database round trips when reading LOBs. By default fetch size rows are read in a single database round trip. However if the rows contain LOBs an additional round trip has to be performed for every LOB. Which this option a certain amount of LOB data is prefetched so that no additional round trips have to be performed for this data.