We have a task running every day dumping results of a query into a CSV-file -- as the stdout of
sqlplus running a query.
The number of records is many millions (don't ask), and the job takes over two hours.
In trying to improve the timings, I wrote a C-program (using Oracle's
ofetch() call), but the performance didn't change... Bumping up the Session Data Unit (SDU) size (to 1GB! on the client -- not sure, where the server would clamp it) didn't help.
I then did some more research and found, that
ofen(), if used to capture multiple rows at a time, can improve performance dramatically -- and changed my C-program to use it instead.
Fetching 4K rows at a time, the timings went down to 21 minutes -- a factor of 10x reduction -- causing much rejoicing.
Before we go through the trouble of installing my new executable on production servers, maybe, the stock
sqlplus can also do the same multi-row fetching -- if supplied with the proper command-line option(s)?
Can it? We're using Oracle-11 although, if this feature is available in the client version 12, we could upgrade the client-side...