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.
closed as too broad by MDCCL, RDFozz, Colin 't Hart, mustaccio, Md Haidar Ali Khan Nov 6 '18 at 3:46
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
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