Which options should I set in SQL*Plus for a script that runs multiple SELECTs, (some are JOINs) to SPOOL the results as text (CSV) in order to optimize these SELECTs, specifically one resulting in a 50 MB file, so that they take shorter to run, while the connection runs over the Internet? There's only read-only access, so I'm wondering what the options may be.
Instead of the 11g client, you should use at least 12.2, because:
- 11g is outdated
- 12.2 has builtin support for generating CSV output (MARKUP),
sqlplus -M "CSV ON" user/password@address
- you can simply start 12.2 with the
-fastoption (-F[ast]), that sets (which you can set and tune further manually as well):
ARRAYSIZE = 100 LOBPREFETCH = 16384 PAGESIZE = 50000 ROWPREFETCH = 2 STATEMENTCACHE = 20