Re: Oracle performance on large loads

In article <32ACAAE7.211E_at_ix.netcom.com>, thielm <thielm_at_ix.netcom.com> wrote:

> We have several 250 MB Asci Text Files that we would like to load into an
> database. We've written code (about 2000 Lines) in PL/SQL to perform the
> insertions, lookups etc
> The file is read by a external program with about a 1 hour overhead to
What kind of program ?

> The actual loading of the 1 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

How about Redolog files,how big and where are they compared to the datafiles?

> 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.

SQLLoader is VERY fast though. Try loading the records in a (temp) table and then processing them. Get the storage of the temp table right before you start the load. After loading put index(es) on the table to speed up data processing. Then start your PL/sql.

> What would be the optimal hardware solution for a load like this ?
> Should we consider x parallel servers and more hardrives for each
> datafile ?

More disk drives, more disk drives, more.... You have a lot of disk-IO.

> Or should we consider a totaly different operating system.

Unix on a HP or RS-6000 ?

> 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 ?

Dont know about that. Testing SQLloader+temp table is really easy. Check your indexes. And good luck.

R. Schierbeek, DBA                         ---|--- 
I'd rather be flying    _____________________ | _____________________
email: bytelife_at_worldonline.nl               (_)