Can sqlplus fetch more than one row at a time?

Question

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...

Answer 1

SET ARRAYSIZE has a default of 15

"Sets the number of rows that SQL*Plus will fetch from the database at one time.

Valid values are 1 to 5000. A large value increases the efficiency of queries and subqueries that fetch many rows, but requires more memory. Values over approximately 100 provide little added performance. ARRAYSIZE has no effect on the results of SQL*Plus operations other than increasing efficiency."

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqpug/SET-system-variable-summary.html#GUID-773AEA4E-060E-4C1E-B1A0-52F7DE084AE8

There are additional tweaks that can be done (including the SDU) in either the sqlnet.ora (changing the DEFAULT_SDU_SIZE ) or in the connection string. That can be defined in tnsnames.ora but you can also put it as part of a versbose connection string such as

sqlplus usr/pwd@(DESCRIPTION=(SDU=11280)(ADDRESS=(PROTOCOL=TCP)(HOST=svr)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=svc)))

Put in your appropriate values for usr,pwd,svr and svc https://docs.oracle.com/cd/E18283_01/network.112/e10836/performance.htm

Answer 2

I think Gary's advice is sound. You might also try testing with utl_file or a Perl script that writes the data to a file directly. I would hesitate to write a C program as that will require a higher level of expertise to maintain.