Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*Loader vs Import performance

Re: SQL*Loader vs Import performance

From: Petr Schmidt <petr.schmidt_at_home.com>
Date: Thu, 01 Feb 2001 05:03:08 GMT
Message-ID: <g66e6.52573$K8.2556760@news1.rdc1.ab.home.com>

Yes, we are dropping indexes prior to load. Someone has mentioned in a different thread doing a truncate with drop storage can affect performance. I can see having to load above the high water mark and wasting storage, but not clear how could that affect load performance (apart from negligible overhead of extent allocation).

--

Thanks,
Petr Schmidt
Matthew Fuller <matthewlf_at_my-deja.com> wrote in message
news:956rmu$ait$1_at_nnrp1.deja.com...

> 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
> >
> >
>
> Peter,
>
> I've never compared the two, but I was always told that a direct load
> SQLLDR session would beat an IMPort too. The only thing that comes to
> mind--and it sounds like you're saavy enough to have checked this--is
> to drop your indexes before loading the data and then rebuild them
> afterwards.
>
> Matt.
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Wed Jan 31 2001 - 23:03:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US