OracleR2dbcOptions doesn't support defaultRowPrefetch

[kano5252]

It seems 1.2 Oracle r2dbc doesn't support defaultRowPrefetch ConnectionPropety

here's the issue: Oracle version: 19c spring boot r2dbc 3.2.5 I have a table (80k rows ), select all data using R2dbcTemplate which needs nearly 63 seconds, on DB visualizer Tool after set JDBC Fetch size to 5000, it only needs 0.6s, As far as I know, JDBC fetch Size is defaultRowPrefetch in Oracle, but seems it's not supported yet, please advise how to set it.

[Michael-A-McMahon]

All R2DBC drivers, including Oracle, support Statement.setFetchSize(int) https://r2dbc.io/spec/1.0.0.RELEASE/api/io/r2dbc/spi/Statement.html#fetchSize-int- This is what I'd recommend using, because I think you'll see the best performance when fetch sizes are configured on a per-statement basis. While 5000 might be the right value for one statement, it might be the wrong one for other statements.

The trade-off is memory usage. Oracle R2DBC passes the value of setFetchSize down to the Oracle JDBC driver. Oracle JDBC will allocate memory proportional to the given size. So if we set a size of 5000, then Oracle JDBC will be allocating arrays that are large enough for a 5000 row query. As you've observed, 5000 is great when a statement is actually fetching that many (or more) rows. The increased memory usage is worth it when we're getting a 63 second reduction in latency.

But if we're fetching just 1 row, and we set the same fetch size of 5000, now we'll be wasting a lot of memory. Of course, the database is only sending 1 row of data, so it's not as if we're storing 5000 rows of data in memory. However, Oracle JDBC will still be allocating several arrays to store metadata for each column, and these arrays are going sized at 5000. If we're fetching just 1 row, these arrays really could have been sized at 1, so we've wasted a lot of memory in this situation.

This is just for your consideration: You can consider the trade-off between performance and memory usage. If you think a connection property is the right way to go, there are some ways to do that:

1. You can configure oracle.jdbc.defaultRowPrefetch as a JVM system property.
2. You can configure oracle.jdbc.defaultRowPrefetch in an Oracle JDBC connection properties file

I hope this is helpful. Please let me know.

[kano5252]

All R2DBC drivers, including Oracle, support Statement.setFetchSize(int) https://r2dbc.io/spec/1.0.0.RELEASE/api/io/r2dbc/spi/Statement.html#fetchSize-int- This is what I'd recommend using, because I think you'll see the best performance when fetch sizes are configured on a per-statement basis. While 5000 might be the right value for one statement, it might be the wrong one for other statements.

The trade-off is memory usage. Oracle R2DBC passes the value of setFetchSize down to the Oracle JDBC driver. Oracle JDBC will allocate memory proportional to the given size. So if we set a size of 5000, then Oracle JDBC will be allocating arrays that are large enough for a 5000 row query. As you've observed, 5000 is great when a statement is actually fetching that many (or more) rows. The increased memory usage is worth it when we're getting a 63 second reduction in latency.

But if we're fetching just 1 row, and we set the same fetch size of 5000, now we'll be wasting a lot of memory. Of course, the database is only sending 1 row of data, so it's not as if we're storing 5000 rows of data in memory. However, Oracle JDBC will still be allocating several arrays to store metadata for each column, and these arrays are going sized at 5000. If we're fetching just 1 row, these arrays really could have been sized at 1, so we've wasted a lot of memory in this situation.

This is just for your consideration: You can consider the trade-off between performance and memory usage. If you think a connection property is the right way to go, there are some ways to do that:

1. You can configure oracle.jdbc.defaultRowPrefetch as a JVM system property.
2. You can configure oracle.jdbc.defaultRowPrefetch in an Oracle JDBC connection properties file

I hope this is helpful. Please let me know.

Thanks Michael, really appreciate your kind explanation, I decided to set Fetch size at statement level now, since I'm using Spring R2dbcEntityTemplate, code looks this way: r2dbcEntityTemplate.getDatabaseClient().sql("select * from xxx).filter(statement –> statement.fetchSize(5000))

Also I've tried set JVM system property with oracle.jdbc.defaultRowPrefetch to 5000, it'll work at connection level.

but in connection properties due to I'm using Spring boot R2dbc connection url in yaml:

spring:
  r2dbc:
     url: r2dbc:oracle://host:port?oracle.jdbc.defaultRowPrefetch=5000

seems it's not working.

I've checked OracleReactiveJdbcAdapter which will configure standard and extended options for dataSource based on OracleR2dbcOptions

but no idea where this oracle.jdbc.defaultRowPrefetch is set and functioned in Source code. would you plz give a clue?

[Michael-A-McMahon]

Oracle R2DBC only supports a subset of the Oracle JDBC connection properties. (There's a list here). We can add support for defaultRowPrefetch, it just takes a tiny bit of code.

You're welcome to create a PR for the change if you want to, otherwise I'll get to it fairly soon. The changes should be similar to this PR: https://github.com/oracle/oracle-r2dbc/pull/110 Checkout CONTRIBUTING.md if you're interested.