Troubleshooting
Problem
The setting defaultRowPrefetch is used to help increase the performance of queries to Oracle databases and we sometimes find customers who want to enable this feature for Oracle database connections to increase the prefetched rows. Oracle JDBC drivers have a feature built in as an extension that allows you to set the number of rows that are prefetched while the full result set is being formulated. This is to reduce the number of trips back and forth to the database.
Symptom
If defaultRowPrefetch is set as a custom property on the datasource, this will not work as the defaultRowPrefetch is not a datasource property but a property on the connection itself .
Cause
defaultRowPrefetch is a property of the connection and not of the datasource
Environment
WebSphere Application Server using an Oracle JDBC driver
Diagnosing The Problem
Customer sets this property and does not see it work. This may result in longer times to getting the full result set and more round trips to the database.
Resolving The Problem
The Oracle defaultRowPrefetch can be set in an attempt to speed up queries to a database that return multiple rows. But, it cannot be set as a custom property in a datasource. Please see below:
The way to set this connection property is as follows:.
You cannot set defaultRowPrefetch as a JVM property. It would have to be named
oracle.jdbc.defaultRowPrefetch for that to work. You can only use this property
by loading it into a Properties object in the code and then calling
getConnection with the Properties object.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21417765