| Spool Command [message #9120] |
Mon, 20 October 2003 12:58  |
Ernie Anderson
Messages: 1
Registered: October 2003
|
Junior Member |
|
|
Hi All,
I have a spool command that is running very slow.
I have 8 million records in a table. I am trying to spool those records to a flat file. It is taking 8 hours to spool this out.
Is that long? If so what can I do to speed it up.
The table is a 2 column table called DATA.
Column 1 is: ID Char(4)
Column 2 is: REST_OF_RECORD Varchar(400).
I have no index on the table. There a 4 type of ID's.
Below is my spool command:
whenever oserror exit failure
whenever sqlerror exit failure
set newpage 0
set space 0
set linesize 200
set pagesize 0
set echo off
set feedback off
set heading off
set termout off
set wrap off
set trimspool on
********************************
*** Generate flat file
********************************
select recid || '|' ||
rtrim(rest_of_record)
from exdata
where id != 'PNR1'
order by recid,rest_of_record;
select recid || '|' ||
rtrim(rest_of_record)
from exdata
where recid = 'PNR1'
order by recid,rest_of_record;
|
|
|
|
|
|
| Re: Spool Command [message #9122 is a reply to message #9121] |
Mon, 20 October 2003 14:22  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Make that a Concatenated index on ID,RECID,REST_OF_RECORD for an Index Fast Full scan.
Just curious,why PL/SQL utlfile compared to a straight Spool/SQL ? He is concerned about speed and hence Sqlplus/Spool/SQL will be faster than the UTL_FILE approach...
-Thiru
|
|
|
|
| Re: Spool Command [message #9123 is a reply to message #9122] |
Mon, 20 October 2003 15:07 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
|
Hmm...I never thought of this. We write very large files from huge,I man huge tables and we never had any performance problems. But maybe you are right and SPOOL is faster. I just cannot stand a look of millions of rows running on my screen...
|
|
|
|
| Re: Spool Command [message #9124 is a reply to message #9123] |
Mon, 20 October 2003 15:14 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Well, if on screen display is the only issue, just
set TERMOUT OFF in the sqlplus script and it goes straight to the spool file...
-Thiru
|
|
|
|
| Re: Spool Command [message #9125 is a reply to message #9124] |
Mon, 20 October 2003 15:17 |
sverch
Messages: 582
Registered: December 2000
|
Senior Member |
|
|
And in order for SELECT statement not to show in the file I guess I have to run the statement and then
/
?
Again, I may be wrong but I like proc better.
|
|
|
|
| Re: Spool Command [message #9126 is a reply to message #9125] |
Mon, 20 October 2003 15:25 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
set echo off termout off feedback off...etc in the script.
Right, Pro*c I believe is faster than both SQL/Spool and PL/SQL/Utl_File for data unload... but I havent touched Proc and am happy with simple and easy sqlplus/spool :-)
-Thiru
|
|
|
|
| Re: Spool Command [message #9136 is a reply to message #9126] |
Tue, 21 October 2003 12:48 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
Also in case you haven't already set it,
SET TRIMSPOOL ON PAGESIZE 0
The default for TRIMSPOOL is OFF, so if you don't have this set you could be writing out MBs of trailing blanks to the file. Also SET TERMOUT OFF as previously suggested is a good idea - a lot of the I/O could be the client process redrawing the screen.
You might give some of the other settings a look e.g. SET ARRAYSIZE 100.
Also bear in mind SQL*Plus is a client/server app (and the drive you are spooling to might be networked as well in some configurations) and you could be pulling all that data around the network.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
