Ability to set OCI_ATTR_LOBPREFETCH_SIZE

[daviessm]

I'd like to be able to configure the LOB prefetch size for a statement. It appears to be hard-coded to 1 in ODPI-C: https://github.com/oracle/odpi/blob/main/src/dpiStmt.c#L523

[anthony-tuininga]

The LOB prefetch size is not hard-coded to 1 -- that attribute refers to a boolean indicating whether or not to prefetch the LENGTH of the LOB! You can see that in the documentation. What you are asking for is the ability to set the attribute OCI_ATTR_LOBPREFETCH_SIZE. Yes, I know it is confusing! Are you hoping to set this for all LOBs fetched from a particular query? Or do you want the ability to specify different values for different columns?

Are you familiar with the ability to fetch LOBs as strings/bytes directly? This performs significantly better than fetching LOBs directly and doesn't require additional memory being allocated for the prefetch.

[daviessm]

Thanks, yeah I spent ages poring over the docs yesterday trying to figure it out.

I'm not familiar with direct interfaces to LOBs at all; I'm mainly a Java developer transitioning to Rust and trying to figure out why selecting lots of CLOB columns is so slow in rust-oracle. In the JDBC driver I setLobPrefetchSize to a size just greater than the average CLOB size and that seems to work well.

Of course I could be barking up completely the wrong tree - my equivalent Java code also uses REF_CURSORs to select all the data which seemed to also give a speed boost but isn't yet implemented in the Rust library. It also uses the JDBC CLOB type and frees the LOB pointer manually in a separate thread.

[daviessm]

I managed to profile my application earlier: for the case I'm investigating it queries all columns from all 69,000,000 rows in a table with two CLOBs and 85 scalar columns, in parallel, with each thread using BETWEEN <rowid> AND <rowid> (the rows are guaranteed not to change during processing). One of the LOBs is NULL about 50% of the time and the other is always null. My code does not try to read from NULL values.

33% of the runtime is taken inside the WSARecv function which seems to be a win32 blocking network wait, inside a call from dpiLob_readBytes() -> dpiLob__readBytes() -> OCILOBRead2(). That sounds to me as though for every row the LOB is having to be read separately - would setting OCI_ATTR_LOBPREFETCH_SIZE allow these to be streamed to the client more quickly?

For the remaining slowdowns I can see dpiConn_newVar() -> dpiVar__allocate() -> dpiVar__initBuffer() -> malloc_base()/calloc_base(), presumably reading the other columns, taking up another 22% of the runtime. Can you think of a better way to read all the columns in bulk?

[anthony-tuininga]

Yes, if you use LOBs, each read is an independent round trip to the database -- which can dramatically increase the amount of time queries with LOBs take. One option to reduce this is to set OCI_ATTR_LOBPREFETCH_SIZE -- which will eliminate round-trips for LOBs that are up to that size -- at the cost of allocating that additional memory for each LOB. This option is not currently available with ODPI-C but the second option is better anyway, I think! That option is to tell the server you don't want LOBs but want strings instead. You can see that in this sample. This will allocate memory in chunks of 64K each, but only up to the number of chunks needed to house the actual LOB, no matter the size. This approach has been demonstrated to be considerably faster. If I remember correctly as much as 2-3 times faster. I'd recommend this approach over the use of OCI_ATTR_LOBPREFETCH_SIZE -- which is why I haven't implemented that option yet...

[kubo]

That option is to tell the server you don't want LOBs but want strings instead.

@daviessm uses rust-oracle. However it cannot in rust-oracle at present. I'll add Statement.define() to rust-oracle.

EDITED: I added StatementBuilder::lob_locator instead of Statement.define().

[daviessm]

@kubo Fantastic, thanks. I was just about to open an issue pointing you towards this. It'll be useful for me for both CLOBs as strings and BLOBs as byte arrays (or Vec<u8>).

@anthony-tuininga you can close this issue if you like. This way of working will fit my needs.

[anthony-tuininga]

Sounds good. Thanks @daviessm.

[akostadinov]

One question about this. It's super cool to fetch LOBs as string/bytes but does this impose limitations over the size of LOBs that can be fetched? Will OCI_ATTR_LOBPREFETCH_SIZE allow huge lobs to be read while this approach only up to 2GB or some other limit?

[anthony-tuininga]

You can still fetch LOBs if you wish up to any size that is supported. If you fetch the LOB as string/bytes, though, you are limited to about 1 GB (although I have seen success past 1 GB, too).

[akostadinov]

Thank you again for the insight to use LONG piecewise fetching. FYI, I tested that is can go almost to 2GB. While standard LOB read2 can go practically unbound. For anybody interested to double-check this finding

bench_lob_fetch.c

It is AI generated so excuse sloppiness also excuse a bunch of other tests that I've run. What you might be interested in is:

gcc -I/usr/include/oracle/21/client64 -Wall -O2 -o bench_lob_fetch bench_lob_fetch.c -L/usr/lib/oracle/21/client64/lib -lclntsh
./bench_lob_fetch test_sizes 102400 524288000 # try 100MB and 500Mb LOB using LONG vs standard method

Also for my other performance findings(attempts) in the different modes, you can check kubo/ruby-oci8#271 and I will appreciate any corrections because there might be many issues with the test code.