KNOWLEDGE BASE

How to adjust Oracle JDBC Fetch Size


Published: 29 Sep 2022
Last Modified Date: 30 Sep 2022

Issue

How to adjust Oracle JDBC Fetch Size to get better query speed.

Environment

  • Tableau Desktop 2021.2 and later
  • Oracle

Resolution

1. Create an oracle.tdc file as below
<?xml version='1.0' encoding='utf-8' ?>
<connection-customization class='oracle' enabled='true' version='8.10'>
<vendor name='oracle'/>
<driver name='oracle'/>
<customizations>
<customization name='CAP_JDBC_USE_ADAPTIVE_FETCH_SIZE' value='no' />
</customizations>
</connection-customization>

2.Create oracle.properties as below.
defaultRowPrefetch=10000
NOTE: the user should adjust the value "10000" to suit their own environment.

3. Put oracle.properties and oracle.tdc into C:\Users\xxxx\Documents\My Tableau Repository\Datasources.

4. Put the Oracle JDBC driver (e.g. ojdbc8.jar) into C:\Program Files\Tableau\Driver.

5. Start Tableau Desktop and use Oracle connector to connect. Tableau will use JDBC to connect to Oracle and the customized Fetch Size.

6. For quicker tuning suitable Fetch Size, after connecting to Oracle in Tableau Desktop, click Data - Extract data and choose top 2000,000 rows. Then enable performance recording to check how much time elapsed for the query. (Help - Settings and performance)
 

Cause

Since 2020.4 Tableau has introduced adaptive fetch size method.
Did this article resolve the issue?