2

I'm trying to export a moderately complex query to a CSV file. I've been doing this manually via SQL Developer during development, but now I'm setting up an automated process so I switched to using SQL*PLUS to export. For some reason the SQL*PLUS version is meaningfully slower than the manual SQL Developer export! How can this be? I thought SQL*PLUS was made to be faster.

Here are the headings I'm using:

set termout off;
set verify off;
set flush off;
set pagesize 0;

column tm new_value file_name noprint
select to_char(sysdate, 'YYYYMMDD') || '_filename.csv' tm from dual ;

SET markup csv on;
set echo off;
set feedback off
-- set rowprefetch 1000
-- set arraysize 100

SPOOL &file_name

SELECT ....*querygoeshere*
spool off;
exit

I'm calling this query from a windows machine using a batch script sqlplus -l -F -M "CSV ON" username/pass@server @script.sql

I have tried turning the arraysize on and setting it anywhere from 100 to 10,000, tried rowprefetch to 1000 or off, and tried removing both the -F and -M flags in my calling script. My line lengths vary a LOT so I likely couldn't set the linesize less than 7500 so I haven't done that, but no other article I find has other ideas that seem to help.

The bizarre thing is I can see the file loading slower. If I just refresh on the file itself in explorer an export from SQL Developer simply grows at a much faster rate than one from SQL*PLUS. The SQL Developer export in progress jumps up over 100k/s as I hit refresh; the SQL*PLUS export in progress generally needs a few seconds at least per 100k. These are both happening on the same machine though; what's the difference?? What am I missing?

3
  • 1
    sqlplus generally outputs to the terminal screen. even though you are spooling to a file, you're also probably also outputting to the terminals output, and that will be the point that slows you down. you could try piping the output of sqlplus to a separate file, or try using the ctrl-O to turn off the output, if you're on a system that supports that, and see if that resolves in a speed increase Commented May 20, 2021 at 8:04
  • Hi Jad, I was under the impression that set termout off; stopped that; not so? If not then good to know about the piping. What is ctrl-0? Commented May 20, 2021 at 14:33
  • ctrl-(letter o) ... it turns off the output to the screen ... I might be wrong if it's not bringing anything up in the terminal window, but it wouldn't surprise me if it was the terminal IO that was causing the problem Commented May 21, 2021 at 9:37

1 Answer 1

2

It turns out, after several days of testing different settings, that a better approach was to switch to SQLcl. It supports sql*plus scripts but seems to work MUCH faster; comparable to SQL Developer export speed, or even better (which is the performance I'd hoped for in the first place!). Switching to it, and replacing SET markup csv on; in my scripts with SET SQLFORMAT CSV kept the same result but resulted in a massive speed increase with no further changes. Apparently I was wrong to start with sql*plus and should have just begun with SQLcl.

It's worth noting for others that follow that the many questions, blogs, and forums that discuss sql*plus being faster seem to have been for earlier versions of oracle. As of this writing, in mid 2021, SQLcl seems likely to be the better way to go for speed. Or at the least, it very much was in my case.

Sign up to request clarification or add additional context in comments.

3 Comments

I thought the same way too, but it seems SQLcl is missing some set options that new SQLPlus already has: SP2-0158: unknown SET option "markup" SP2-0158: unknown SET option beginning "lobprefetch..." SP2-0158: unknown SET option beginning "rowprefetch..." No matter what arraysize I pass (and that one is supposedly accepted) it seems to always pass arrayzise=15 (default), I don't understand ...
may i know the version of Oracle DB you are using that you experience this much faster performance on? since you mentioned sqlplus being faster for earlier versions. I am also experiencing slow performance of sqlplus, using Oracle 12c DB
after trying both sqlplus and sqlcl, i have to say this answer is not correct for me. sqlplus is faster than sqlcl based on my usage. I refer to this video where the creator, who seems to be an experienced Oracle practitioner, says sqlplus is faster for sure as it is not Java based unlike sqlcl.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.