Using the default Oracle JDBC fetch size may lead to performance issues in Jira or longer application startup time
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
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. It can also cause longer startup times.
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 defaultRowPrefetchsetting, 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.
Open the
dbconfig.xmlfile on a text editor.Add the following property inside the
<jdbc-datasource>section:1<connection-properties>defaultRowPrefetch=200</connection-properties>Restart Jira.
Was this helpful?