Adjusting Oracle row prefetch settings

In certain specific use cases, you can adjust performance for your Oracle XA and Non-XA data sources for an object store by updating the defaultRowPrefetch parameter.

About this task

Changing the value of the Oracle defaultRowPrefetch parameter in the WebSphere Application Server datasource properties can improve performance in some use cases with smaller workloads. For example, this update might be effective when some larger row set system queries are more important. Note that the setting does not affect adhoc user searches.

The default setting for this parameter is 10. You can increase the setting to determine the impact on your performance. However, setting the value higher than the default of 10 might degrade response times depending on the size of the workload.

Update the setting for each object store data source where you want to use the larger defaultRowPrefetch size.

Procedure

To update the Oracle row prefetch setting in WebSphere Application Server:

  1. In the WebSphere Application Server navigation pane, expand Resources > JDBC, and click Data Sources, then select the data source that you want to update.
  2. Click Custom properties > New to create a new custom property.
  3. For the Name field, specify connectionProperties.
  4. In the Value field, specify defaultRowPrefetch=your_value, for example, defaultRowPrefetch=100.

Results

The setting you specify is added to the WebSphere Application Server resource.xml file:
<resourceProperties xmi:id="J2EEResourceProperty_1618433693370" name="connectionProperties" type="java.lang.String" value="defaultRowPrefetch=1000" required="false"/>