oracle sql plus spool

Question

Hi.

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

With a typical query, 14M records is at least several hundred megabytes of data to fetch out of the server, pass across the connection and save to the disk.

Given this, 12 minutes does not sound too much to me.

However, it is still possible that your query can be optimized. Could you please post it here?

Answer 2

So is this going over the wire or are you logged into the box that has the database? If you have access, maybe you can run your sqlplus session on the box where the database lives and zip the file up then send the file to your local machine. It might be faster to send a big file over the wire instead of sending millions of smaller records. Of course this won't make it super fast, but might shave some time off.

Also with that much data do you really need to spool it to file? Can you do an export instead?

Answer 3

You can enable output buffering by adding to you script

SET FLUSH OFF

But result depends on your OS.

Answer 4

You might find it quicker to use UTL_FILE, but probably not that much faster.

in my test it was slightly faster over about 20k of rows, blow that out over 14 million though and it might be worth it.

I believe if you want to get any quicker than this, the way to go would be pro*c.. but I haven't got into that, so can't really advise.

set pagesize 1000
set FLUSH OFF
drop user usera cascade;
create user usera default tablespace users identified by abc123;
grant create session to usera;
grant resource to usera;

create or replace directory testdir as '/tmp';
grant read,write on directory testdir to usera;
grant execute on UTL_FILE to usera;

connect usera/abc123;

set timing on

spool /tmp/spooltest.txt
select object_name from all_objects;
spool off

DECLARE
 v_file UTL_FILE.FILE_TYPE;
 TYPE t_col is table of all_objects.object_name%type index by PLS_INTEGER;
 v_object_names t_col;

BEGIN
  v_file := UTL_FILE.FOPEN('TESTDIR','utlfiletext.txt','w');

  select object_name BULK COLLECT INTO v_object_names
  from all_objects;

  for idx IN 1 .. v_object_names.COUNT LOOP
    UTL_FILE.PUT_LINE(v_file, v_object_names(idx), FALSE);
  END LOOP;

   UTL_FILE.FCLOSE(v_file);
END;
/

The results. The top result being from sqlplus only, the bottom using UTL_FILE

23931 rows selected.

Elapsed: 00:00:06.60

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.45

Answer 5

When getting a lot of results from a query in SQL*Plus, I've found that one thing that takes a lot of time is the actual displaying of the data. If you're spooling the data to a file, you can SET TERMOUT OFF, and the query runs much faster since it doesn't have to spend the time to write it to the screen.

Answer 6

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!

Answer 7

Some options are available from Tom Kyte, who is a real guru.