Optimize Oracle 11g SQL*Plus connection for large remote SELECTs 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 -fast option (-F[ast]), that sets (which you can set and tune further manually as well):



ARRAYSIZE = 100
LOBPREFETCH = 16384
PAGESIZE = 50000
ROWPREFETCH = 2
STATEMENTCACHE = 20