Summary

The default Oracle JDBC driver fetch size (10) might cause more roundtrips to DB and this leads to a longer time to fetch results from queries executed on an Oracle DB, which results in slowness for users when loading pages and performing actions in Jira.

Environment

• Jira Server/Data Center
• Oracle Database

Cause

After executing the SQL query, Oracle DB doesn’t actually start materializing data until the client starts fetching data. It runs enough of the query to generate however many rows the client has asked to fetch. This can be traced to the default setting of the defaultRowPrefetch setting, which dictates how many rows from a query's results will be fetched at a time. By default, the driver will fetch 10 rows - this results in many round trips between the JDBC driver and the database server. Even in situations where there is low latency between the application server and the database server, a large result set will cause significant slowness in the application. 

As an example, in a situation where a table has 150k records, 15k rounds trips between the application and the database will be required for the entire data set to be retrieved by the thread waiting on this information - this translates to a total of 15 seconds if the latency between Jira and the DB server is at 1ms. An increased latency between the app server and the DB server will increase the time it takes for the full results of the query to reach the application proportionally.

Solution

Significant improvements can be observed by setting this value on the dbconfig.xml file with a value larger than the default 10, as seen on the graph below:

Our recommendation is to implement this setting with a value of 200, as we don't see much improvement after this point.

1. Open the dbconfig.xml file on a text editor.

2. Add the following property inside the <jdbc-datasource> section: 

   <connection-properties>defaultRowPrefetch=200</connection-properties>

3. Restart Jira.