Re: SQL*Loader vs Import performance

Have you tried extracting a subset of the data in fixed width format to test your parsing theory? My bet is that you are right and the performance hit is occurring at file parse. One way around this would be to extract fixed width, gzip the file, ftp it and gunzip -c the file to a named pipe on the target machine and sqlload with that named pipe as your .DAT file.

In article <k12d6.41493$K8.2044260_at_news1.rdc1.ab.home.com>,   "Petr Schmidt" <petr.schmidt_at_home.com> wrote:
> We are trying to improve performance of DW ETL
> by replacing some import processes by SQL*Loader.
> Interestingly we are finding that direct path SQL*Loader
> takes almost twice as long to run than import. We can
> achieve some scalability by running SQL*Loader in
> parallel, but the whole process still takes more or less
> as long as the import.
>
> Test table load file is about 2.5GB comma delimited,
> approx 6 million records. This is required because the
> table contains mostly VARCHAR columns (131 columns).
> If the table is extracted using fixed size the extract file is
> about 12GB, which presents network transfer problems, etc.
> During the SQL*Loader session the CPU utilization is
> quite high compared to i/o load, which makes me wonder
> how CPU intensive is parsing of each comma delimited
> record.
>
> My past experiences with SQL*Loader have always been
> that it is the fastest method of loading data into an Oracle
> database although I've mostly used fixed size load records.
>
> Any insights into this situation?
> We are running 8.1.6.1 on NT 4.0.
>
> Thanks,
> Petr Schmidt
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Feb 01 2001 - 18:39:16 CST