oracle sql plus spool

Question

I'm using sql plus to execute a query (a select) and dump the result into a file, using spool option. I have about 14 millions lines, and it takes about 12 minutes to do the dump. I was wondering if there is something to make the dump faster?

Here below my sql plus options:

whenever sqlerror exit sql.sqlcode
        set pagesize 0
        set linesize 410
        SET trimspool ON
        set heading on
        set feedback off
        set echo off
        set termout off
        spool file_to_dump_into.txt 
        select * from mytable;

Thanks.

Answer 1

Are you concatenating & delimiting your columns, or are you exporting fixed-width?

See this documentation on SQL*Plus Script Tuning. Specific to your script, here are a few possible ways to speed it up:

1. Make sure LINESIZE is as small as possible. Add your max column lengths (plus delimiters if not fixed-width). This can have a dramatic effect on performance, as SQL*Plus allocates that amount of memory for every exported line. 410 isn't that big, but if you can decrease it that would help. This has made a big difference, in my experience.
2. Don't turn TRIMSPOOL on. This can also have a big impact. Each line will then be padded out to LINESIZE, but with an optimal linesize, and depending on how you're using the file, that may be acceptable. However if you want to elminate trailing spaces entirely, it can often be faster to trim them using other methods post-export.
3. Play around with ARRAYSIZE. It may help (a little). It sets the fetch size for SQL*Plus. Default is 15 rows. Bumping to, say, 100 may help, but going too large might decrease speed.

Hope this helps!