How to Increase JDBC fetchSize in ORDS and Improve Response Time for Large Resultsets?

We are looking at using Oracle REST Data Services to return some large datasets, but the response time is really slow.

We have the ORDS server installed in a different region compared to the Oracle Database, so there is some latency involved.

We have an endpoint where we need to have a big page of 10000 items for the result set:

BEGIN
ORDS.define_handler(
     p_module_name    => 'default',
     p_pattern        => 'programs/',
     p_method         => 'GET',
     p_source_type    => ORDS.source_type_collection_feed,
     p_source         => 'SELECT * FROM v_programs',
     p_comments       => 'Get a list of programs in the system.',
     p_items_per_page => 10000);
  COMMIT; 
END;
/

When accessing the endpoint, it takes over 35 seconds to get the result:

The database execution time is actually quick,~ 10 seconds! The main issue seems to be that many roundtrips are done from the server to the database due to the JDBC fetchSize parameter. The default value for this setting is 10 in Oracle, causing a slow response in this case. 1001 fetches were done when accessing the endpoint with a page size of 10000:

We wonder if its possible to change the value of the JDBC fetchSize from 10 to a higher value in ORDS? If we could change it to say 1000, it would have a dramatic performance benefit in this case.

The obvious solution is to move the ORDS server closer to the database, but in this case we are not able to do that.

TL;DR - add this to your pool config.

<entry key="defaultRowPrefetch">200</entry>

Longer answer -

Your observation is correct, we do not deviate from the default jdbc/ucp settings for the pools/connections, at least not for JDBC fetchSize.

But.

1. This will get better. I just accepted an enhancement request for this to be more intelligent. For example, as the pagesize for your Module & their REST APIs increase, we can increase the jdbc fetch size. Also, 10 is too low, Our default payload for a REST API is 25 rows, so that's a more reasonable default.

2. You can define this for the pool. Add the entry key line from above to your pool config xml file.

Once I restart ORDS, I can easily test with a REST API that brings back 10,000 records.

I set the fetch size to 200, and it took 50 (+1) fetches to retrieve the 10,000 records for the GET Request's JSON response.