Re: bcp is faster than sqlldr???

jeffzhangusa_at_yahoo.com (jeff zhang) wrote in message news:<f4e9a265.0107311139.3ba64b75_at_posting.google.com>...
> Using sqlldr and Oracle8.1.6 on Unix box, I rewrite a program that
> used bcp and MS SQL server7.0 on NT box.
> It is to my surprise that bcp in and bcp out is much much faster than
> sqlldr and
> utl_file....at least 20 times faster.
> In my example, it takes 27 seconds to bcp in 110007 rows into a table,
> whereas sqlldr takes 6 minutes. When dump out 4000 rows of data to a
> file via bcp, it takes 1 seconds, whereas util_file takes 10 minutes.
>
> Is Oracle supposed to be faster than SQL SVR?
>
> Any one has better way to move data out of oracle database to a flat
> file according to a control file?
>
> Anyone know why?

You obviously have something wrong. First off, sqlldr should be very fast - way faster than anything Billionaire Bill every produced. I don't know what size machine you're using, but the machines I've worked on in the past 5 years would load 110,000 rows in about 5-7 seconds. I'm guessing of course, but it sure wouldn't be 6 minutes. I remember loading 60,000,000 rows in 45 minutes 7 years ago (actually a friend did it), so today it may complete in 20 minutes.

You need to have a dba check out the database. I'm guessing you have a lot of "issues" in your database - redo.log, sga, etc.

1. Take a look at the alert.log. If there are any entries with "checkpoint not complete", your redo.log is too small.
2. Do a bstat before a load and an estat after a load. It should tell you what cache hit ratios you are getting, but I doubt it'll tell you anything interesting. It sounds like you have an I/O issue, not a cache issue.

Sorry, this is too hard to diagnose without having the machine in front of me. Just suffice it to say that your 27 minute load takes about 26.8 minutes too long. ;-)

Good luck,
Will. Received on Fri Aug 03 2001 - 08:13:49 CEST