0

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.

  • 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
1

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):
ARRAYSIZE = 100
LOBPREFETCH = 16384
PAGESIZE = 50000
ROWPREFETCH = 2
STATEMENTCACHE = 20
  • 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 11.2.0.4 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.