OCI Prefetch not working

Question

We have an application that is using OCI, but we cannot seem to make prefetch working.

We have a select query on a table with a dozen of columns (amongst those is one CLOB column and one MDSYS.GEOMETRY column). The CLOB column is in 99% of the cases smaller than 4000 bytes and even in 50% of the cases empty.

Process is as follows:

• We set OCI_ATTR_PREFETCH_ROWS to 500
• We bind the columns to output buffers, the CLOB is bind directly into a text buffer of 8000 bytes
• For the CLOB handle, we set OCI_ATTR_LOBPREFETCH_SIZE to 8000
• We use OCISTMTEXECUTE with iters 1 as one row is fetched into the buffer.

Now if we look with WireShark we see that the network packets are never filled (there is only one record per roundtrip).

If we execute the same select query with SQLDeveloper then the packets are nicely filled, so we can assume that row prefetching should work.

What are we missing here?

Answer 1

Since I ran into the same problem recently: OCI_ATTR_PREFETCH_ROWS and OCI_ATTR_LOBPREFETCH_SIZE don't work together.

From Oracle Docs:

Prefetching is not in effect if LONG, LOB or Opaque Type columns (such as XMLType) are part of the query.

Whenever you have LOBs in your result set, you can't use implicit prefetching via OCI_ATTR_PREFETCH_ROWS and instead you need to use explicit row-fetching by calling OCIStmtFetch2() with a rowcount > 1. Then of course you need to setup buffers in advance, keep track of the already consumed rows and especially need to call OCIDefineArrayOfStruct() to inform oracle about your buffer layout.