Re: Extracting large volumes of data from oracle to flat files - Poor Performance

You may well have a hardware or network bottleneck. Have you checked to see if you are pushing the physical disks to the max i/o rate? Where is the flat file - on the said disk,the same scsi channel, or raid array as the database? on a different server, in which case have you looked at network utilization while writing the flat file? I guess what I am saying is that you need to give us some more information.

Finally, I agree with Sybrand's sentiments.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tvo43c6dfgqcef_at_corp.supernews.com...
>
> "mark" <mark_knisely_at_yahoo.com> wrote in message
> news:8d852b26.0111211025.62ca1e49_at_posting.google.com...
> > We are working on a data mart and we need to extract large
> > volumes of transactional data from the source system (also
> > Oracle DB) to flat files. We then use SQL Loader with the
> > direct option to load the data into our staging tables. While
> > the Load performs very well (less than 10 minutes to load 4
> > million records), the extract is Extremely slow. The method we
> > are currently using for the extract is an sql script (SQL Plus)
> > spooling to a comma separated flat file. For 4 million records,
> > the process is taking ~4+ hours to complete. We moved to a dual
> > processor machine and that didn't make any difference - in fact,
> > both CPU remain over 90% idle while the extract is running. Is
> > there a more effective than SQL Plus to extact data to a flat
> > file?
>
> I fail to understand why you are using flat files if you can use a
database
> link and sql*plus COPY to accomplish this.
>
> Hth
>
> --
> Sybrand Bakker
> Senior Oracle DBA
>
Received on Wed Nov 21 2001 - 21:38:23 CST