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.

  • What problem are you getting? I've never had any problem just running the queries and spooling the output. 50Mb isn't so much data these days. – Colin 't Hart Nov 5 '18 at 17:16
  • @Colin'tHart The script takes over half an hour to run, and the 50 MB SELECT takes most of that time. Thanks for contributing your experience. – bitoolean Nov 5 '18 at 17:23
  • Maybe you could store the query results in temporary tables and just spool those with select *? Materialized views could be a good solution for this -- simply refresh, and then run the script to spool. – Colin 't Hart Nov 5 '18 at 17:26
  • 1
    What makes you think that SQL*Plus is the bottleneck that you can improve by setting its options? – mustaccio Nov 5 '18 at 19:45
  • OP said the connection is made through internet. When fetching large amount of data, the default value 15 of SQL*Plus arraysize parameter can be a serious bottleneck on a local network, but on high latency (internet compared to LAN is high latency) channels, it is even worse. With that setting the client fetches the results in 15 row chunks, doing network roundtrips on every 15 rows. arraysize can be set as high as 5000. – Balazs Papp Nov 5 '18 at 20:16

Instead of the 11g client, you should use at least 12.2, because:

  1. 11g is outdated
  2. 12.2 has builtin support for generating CSV output (MARKUP), sqlplus -M "CSV ON" user/password@address
  3. you can simply start 12.2 with the -fast option (-F[ast]), that sets (which you can set and tune further manually as well):
PAGESIZE = 50000
| improve this answer | |
  • No, 11g is the Oracle server I'm connecting to. I see I did get the 11.2 SQL Plus, for compatibility concerns, probably. I'll check if I can use a newer one. Thank you so much! – bitoolean Nov 5 '18 at 17:19
  • @bitoolean 12c client is compatible with 11g databases, go for it. – Balazs Papp Nov 5 '18 at 20:08
  • @bitoolean 12.2 client can be used with or 11.2.3 databases according to note 207303.1. Client 12.1 2ith lower 11g version, too. – miracle173 Nov 5 '18 at 21:09
  • I've tried this with SQL*Plus 12.2 and even 18.3, and even SQLCl... I even tried a combination of ArraySize 5000 and ROWPREFETCH 5000. After 10 minutes, it's still not done running. Thanks for the pointers nonetheless - I realize this should have helped. I guess the problem must be with the server. This seems to have helped others. Or maybe this is normal with Oracle software. – bitoolean Nov 7 '18 at 14:32

Not the answer you're looking for? Browse other questions tagged or ask your own question.