Possibility to fetch single rows from pipelined table function

[Elias481]

Currently odpi can not be used to fetch rows from pipelined table functions at the time they are piped.
As I found some time to investigate the issue I found that the reason is the fixed prefetch value. (At first I was unsure whether this just not works as sqlplus showed similiar behaviour.)

I would need to

• either have the prefetchSize configurable so that prefetching can be completely disabled for example in cx_Oracle
• or (what I did now to have to change only one function) a logic in dpiStmt__execute that sets the prefetchSize not higher than configurable fetchArraySize and disables prefetching for the fetch calls that follow the exec... (enable prefetch only for execute itself and limit to fetchArraySize Elias481/odpi#3)

(See below a link to gisthub with expected vs. current output, whereby expected could be changed to have "zero" wait on execute and expected wait on first fetch...)

Answer the following questions:

1. What version of ODPI-C are you using (see dpi.h)?
   current (3.0.0) / master from github

2. What exact command caused the problem (e.g. what command did you try to install with)? Who were you logged in as?
   Trying to get rows from pipelined table function as soon as they are piped...

3. What error(s) you are seeing?
   https://gist.github.com/Elias481/39786d3bc0983c338cc73da414683e74 -> current.out
   Note the "time to wait for row" (difference between piperow and fetched timestamp).

4. What OS (and version) is your application executing on?
   Linux

5. What is your version of the Oracle client (e.g. Instant Client)? How was it installed? Where it is installed?
   Different (11.2.0.4, 18.3.0.0, ORACLE_HOME and Instant-Client)

6. What is your Oracle Database version?
   Issue exists with 11.2.0.4 and 18.3.0.0 and probably all the versions between..

7. What is the PATH environment variable (on Windows) or LD_LIBRARY_PATH (on Linux) set to? On macOS, what is in ~/lib?
   The one needed for the client I want to use...

8. What environment variables did you set? How exactly did you set them?
   Depends on client and setup I want to use. Either ORACLE_HOME or LD_LIBRARY_PATH or nothing at all if I use Instant-Client with ld.conf.so setup for the client and use Python ctypes to preload libclntsh.so*. Sometimes I also set NLS_LANG... How I set them depends on how I execute the Python scripts...

9. What compiler version did you use?
   For example gcc 4.8.5 20150623 (Red Hat 4.8.5-28)

[anthony-tuininga]

Thanks @Elias481. I'll take a closer look after the conference. I know that setting the prefetch size to be the same size as the fetch array size resulted in significant performance reductions under certain circumstances. But it may make sense to allow for that -- I'll look after the conference and get back to you.

[Elias481]

Yes there are possible cases where that causes performance reduction.

Possibly the overhead of using the OCI perfetch buffers for large prefetches (but that is nothing I changed..).
And for example doing single row fetches fetches with array size set to 1 as then possibly another roundtrip is needed to detect end of data or to notify the server the session should be closed..

And also to disable prefetching at all for the subsequent fetches could introduce some performance penalty in some cases as look-ahead for last row and automatic connection closing that should happen then(?) is missing.

Would be interesting to know which other cases would be negatively affected. I cannot really mind others but that does not mean anything...
For me it would be absoltely fine to have a property or method on the cx_Oracle cursor to disable the prefetch completely for such cases... (Could also be quite simple implemented making the DPI_PREFETCH_ROWS_DEFAULT a property of the statement which can be set to 0 before doing the exec...)

[Elias481]

I also came to thoughts that for best performance it could also make sense to define before exec if queryInfo is already known (for repatet statements and or statements run through cxo Cursor.parse()?) and then instead of (or additionally to..) prefetch use the fetch on exec options (iter parameter to exec) which should not introduce any performance penalty compared to exec+fetch even for full array size. But not sure whether this optimization is feasable with current code base and whether it would be worth to work on this...

And Btw. dpiStmt__execute function and performance. I found that all statements then returned error other than ORA-00001 get the deleteFromCache flag set. But for errors as 1400/1438/1461/2290/2291/2292/21525 one could also want not to drop the statements from OCI cache. Thought the "1" is the most common try&error case but similiar could apply to the other codes if application just tries the inserts/updates without previous validity checking and possibly deallocate the dpiStmt causing the deleteFromCache to apply before executing same statement with different values (which might not run into one of the errors).

[anthony-tuininga]

As you can see I moved the request to adjust the behavior of dpiStmt_execute() to issue #76. As for your other request, I understand that you are trying to disable prefetch completely. In theory that should be doable but I'll check internally to see if that would be an issue and get back to you.

[Elias481]

Not not to disable completely, but at least to allow to limit it to single row for execute and to completely disable on subsequent fetches to allow the use case of fetching single row.

At all I still think ideal solution would be to never use prefetch but on first execute of a statement where the define cannot be done before execute without addtional describe call - and use only array size during exec and fetch calls.
To disable prefetch on first exec or set it to one single row I see the two possibilities: make it separately configurable or just set it to maximum the array fetch size and keep telling users to not use array fetch size of one for performance reasons, even not if they expect single row results - as long as they are not having such use case where they need the exec/fetch to return after each single row received from server.

[anthony-tuininga]

Looking at your example the only way you are going to get the results you are requesting is to disable prefetch and set the fetch array size to 1. The behaviour that currently takes place is as follows:

1. call dpiStmt_execute(); round-trip performed to execute statement and fetch two rows into prefetch buffer.
2. call dpiStmt_fetch(); no round-trip performed as row is retrieved from prefetch buffer.
3. call dpiStmt_fetch(); no round-trip performed as row is retrieved from prefetch buffer.
4. call dpistmt_fetch(); round-trip performed and 1 row is placed in fetch array and two rows are fetched and placed in prefetch buffer.

You can see that as in your example where each row is being generated after two seconds that there is a delay of 4 seconds initially (for two rows) and then there is a delay of 6 seconds (for three rows). If you have your prefetch set to the value 1, then you will have a delay of 2 seconds initially (for one row) and then a delay of 4 seconds (for two rows). The only way you will get no additional delay is by setting prefetch to 0. You can get away with setting prefetch to 1 in the initial execute and then set it to zero thereafter, but that's it. I hope that explains things!

Is it just the case that you brought to my attention? Or do you have other cases where manipulating the prefetch size might be useful? If not, it might make sense to simply add a flag to dpiStmt_execute() that indicates that you want the rows as soon as they are available (no array fetch, no prefetching). Thoughts?

Note that the way ODPI-C is structured you can't actually define memory areas until after execute has taken place.

[anthony-tuininga]

ODPI-C 4.0 was just released containing these changes.