Tune fetch size to optimize large query performance in Oracle Database Service for Azure

You have a multicloud approach with a split stack architecture using Oracle Database Service for Azure (ODSA), connecting from a database client in Azure to a database in Oracle Cloud Infrastructure (OCI). When migrating from an environment where all the components are running locally, to a split stack architecture, you might have to tune network parameters to optimize performance. One of your first actions should be looking at the fetch size of the queries.

Background

Receiving many rows from a query requires multiple network round trips. The number of round trips is a function of the total amount of rows in the result set and the fetch size, which is the number of rows returned in each trip to the database. So, for the same query result, you can influence the number of round trips by adjusting the fetch size. In an environment where the client and the database coexist on the same network, you often don’t need to consider the influence of the fetch size because the number of network round trips isn’t usually a determining factor for the performance.

Network round trips are important to consider in an environment with some latency, such as when you use a split stack architecture between different clouds. When a query requires many network round trips, even a slightly increased latency on every round trip can greatly impact the performance. So, it’s important to reduce the number of round trips as much as possible in this situation.

Fetch size tuning

If a result set has 10,000 rows and the fetch size is 10, you have 1,000 network calls to get the data. If we change the fetch size to 200, only 50 network calls are needed. These differences can be negligible in an environment with low latency. But in an environment with some latency, you must tune the fetch size correctly, especially when dealing with queries that return many rows.

If you increase the fetch size, the client application uses more memory to store all the rows returned in one fetch. You can influence the fetch size in different ways depending on the tools and connectivity you use, such as the following examples:

• Using SQL*Plus, you can control the size of the fetches by either the arraysize or rowprefetch parameters. Our example shows the dramatic impact that using rowprefetch can have.

• To make changes to your own application using JDBC, use the setFetchSize method as documented in Setting the Fetch Size.

• To make changes to your own application using the Oracle Call Interface (the other OCI), use the OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_PREFETCH_MEMORY parameters as documented in About Setting Prefetch Count.

• To make changes to your own application using different environments, third-party drivers have ways to set fetch sizes. For example, in Perl the DBD::Oracle driver for DBI from MetaCPAN provides a way to tune row prefetching.

• If you’re using a third-party application written using the Oracle Call Interface that doesn’t give you direct access to adjust these parameters, you can use the prefetch parameter in the oraaccess.xml file to overwrite the settings of the application. This method is documented in About Specifying Global Parameters in oraaccess.xml.

Reproducible example

The following simple example shows the large impact that tuning fetch size can have. We created a database user perftest, and as that user, we created a simple test table with 2 million rows:

CREATE TABLE TEST (COL_A VARCHAR2(10))
/
BEGIN
  FOR c IN 1..2000000 LOOP
    INSERT INTO TEST VALUES (’aaaaaaaaaa’);
  END LOOP;
END;
/
COMMIT;

On the client, we create a test.sql file:

set pagesize 0
set rowprefetch 1
connect perftest/[PASSWORD]@[connectstring]
select * from test;
exit

Then, we time the running of this script, ensuring that we don’t measure the time it takes to print the results on screen so that we measure only the time the database and the network take:

time sqlplus /nolog @test.sql >/dev/null

We then adjust the rowprefetch in the test.sql file to 15, 50, 100, 500, and 1,000 and run the same again for each of those values.

Test case results

We ran this test case using ODSA in the London region. We used Base Database on a VM.Standard2.2 shape and connected to it from an Azure Standard DS1 v2 virtual machine (VM) with Oracle Linux 8.5. We ran these tests three times and averaged the results. In different regions and with different shapes, you might get slightly different results. However, the overall average remain the same.

These results show that small fetch sizes can have an exponentially large effect. In our example, we see that optimizing rowprefetch setting improves the query response time from 340 seconds (just under 6 minutes) to just 11 seconds.

Conclusion

When you’re moving to a multicloud environment and use queries that return a large result set, you can tune the fetch size of the queries to optimize the performance.

For more information about Oracle Database Service for Azure and Oracle Cloud Infrastructure, see the following resources:

• Oracle Database Service for Azure

• About Oracle Database Service for Azure

• Deploy split application architecture using Oracle Database Service for Azure

• Deploy multicloud Oracle Database Service for Microsoft Azure in a hub and spoke topology (Reference architecture)

• Oracle Cloud Infrastructure multicloud