Oracle performance on large loads

We have several 250 MB Asci Text Files with snapshots of weekly data from another system
that we would like to load into an oracle database. Each Text File contains around 800,000
records and each record represents a fairly complicated relation betweenabout 8 entities.
Several records can also relate to each other.

We've written code (about 2000 Lines) in PL/SQL to perform the insertions, lookups etc
and make some logical decissions like : Do we even consider this record, have we seen it
before etc.

The file is read by a external program with about a 1 hour overhead to read the file
and call the SQL Code.

The actual loading of the 1 file take's about 140 Hours on a 133Mhz pentium with 128 MB of
memory and 3 4GB harddrives running NT 3.51. Datafiles are spread out as good as possible
to minimize contention. All the obvious parameters like db_block_buffers, shared pool size
etc seem to be set ok.

Does anyone have any experience with loading very large amounts of fairly complicated data
and if so does 140 hours seem normal ? I've tried SQL Loader but because of the complexity
of the relations in one record and the possible relations to other records it seems almost
impossible to use.

Is there anyone out there (CONSULTANT MAYBE) that has lots of experience with tuning loads
like this, or are there more advanced tools available that might help me in loading data.

What would be the optimal hardware solution for a load like this ? Should we consider x parallel servers and more hardrives for each datafile ? Or should we
consider a totaly different operating system.

Assuming that I can not use the direct load path in SQL Loader would it make sense to even try to make it work in SQL Loader, or will I come to the conclusion
that all the time is spend in the PL/SQL code and my time spend was in vane ?

Any input would be greatly appreciated.

Mike. Received on Mon Dec 09 1996 - 00:00:00 CST