blog. aregger.io

A blog about Oracle, SQL and Java

twitter github

JDBC LOB Prefetching

20 Dec 2018

In Oracle JDBC version 11.2 LOB prefetching was introduced. The whole point of LOB prefetching is to reduce the number of roundtrips needed to get the LOB data.

The goal of this post is to show how LOB prefetching works and point out which variables influence the optimal prefetch value.

Basics of LOB access

With JDBC driver versions older than 11.2 the behaviour when fetching LOB data is as follows: The client fetches the amount of rows, depending on the set fetch size. If the rows include a LOB column, the client is provided with a LOB locator, which is basically a pointer to the LOB in the database. There is no actual LOB data sent to the client at this point. At the point where the LOB is accessed, e.g. by calling ResultSet.getClob, the client requests the LOB metadata from the database (like LOB length and chunk size). Then in a second step, the LOB data is requested from the database.

So, for every row 2 additional roundtrips are necessary to get the LOB data.

Since JDBC version 11.2 LOB prefetching is available, which can minimize the number of roundtrips. It can be set on the DataSource, Connection, Statement or even on column level by using one of the overloaded methods OracleStatement.defineColumnType. Check the documentation for more information.

LOB Prefetching

In JDBC version 11.2 the default value for LOB prefetching is -1 which disables LOB prefetching. Since 12.1 the default is 4000.

Metadata only

By setting LOB prefetching to 0, you enable metadata prefetching. For every LOB column, the metadata is already fetched with the rest of the row data. This avoids one extra roundtrip for each row when accessing the LOB, as described earlier.

LOB prefetching

Any value higher than 0, sets the amount of LOB data to be prefetched for each LOB. For CLOB data it defines how many characters, for BLOB data how many bytes that are prefetched. So for each LOB smaller than the set prefetch size, no additional round trip to the database is necessary.

Performance

As already mentioned, the whole point of LOB prefetching is to reduce the number of round trips to the database to get the LOB data. To measure the runtime of a simple query in different scenarios, I created a small benchmark where I always fetch the same data, but the following parameters are varying:

Benchmark

All tests are done with Oracle JDBC 18.3 and Oracle Database 18.3. I’m not aware of any changes to how LOB prefetch work since version 11.2, but memory management has changed in the 12c driver, so it’s probably worth to execute the benchmark on your own if you use driver version 11.2.

First, we create a table with some LOB data

create table lobtest (id number(10), data clob);

insert into lobtest
select rownum, lpad('*', 4000, '*')
from dual
connect by rownum <= 1000;

commit;

The benchmark itself is done with JMH. The test class can be seen here, but here’s a wrap-up what’s going on there.

For all combinations of benchmark parameters, the following steps are performed:

The parameters are as follows:

Results

Roundtrips

Let’s first have a look at the number of roundtrips needed. The chart simply shows how many times the database waited for SQL*Net message from client. Benchmark Roundtrips

We see that

Runtime

We saw the impact the different scenarios have on the number of roundtrips. The following chart shows the average runtime out of 10 runs for the various scenarios. The benchmark was performed with 2 different average network latencies (~0.5ms and ~1.5ms).

Benchmark Runtime Note that the runtime was obfuscated and does not correspond to a real unit of time. The lowest runtime is set to 1, and the others are aligned accordingly.

We see that

Benchmark with 10MB LOBs

To get a feeling for what impact the size of the LOB has, I did a second benchmark with the following parameters:

Here’s the result: Benchmark Runtime Note that the runtime was obfuscated and does not correspond to a real unit of time.

We see that

Memory Consumption

I didn’t do any measurements on client-side memory consumption but you should be aware that row fetch size and lob prefetch size affect memory consumption. If you for example, set LOB prefetch size to 4000 and row fetch size to 1000, the driver needs at least 10 times more memory than setting the fetch size to 100.

Conclusion

References