Re: Oracle performance on large loads

Hello Mike,

thielm wrote:
...snip
> 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.

Doesn't sound anywhere near fast enough. I would guess your SQL validation checks are your bottleneck.

You really need to know where and why its slow before you can get better performance. "Seem to be set ok" is only half the solution.

Some places to look:

1. Where is your bottleneck? Disk I/O (reads or writes), CPU, Paging? Why is this bottleneck occurring? What pattern can you see ie does it do lots of reads then lots cpu then lots of writes etc. Patterns can help identify bottlenecks.
2. Does the loader spend most of its time checking the 8 relationships or doing the inserts? Why does it spend so much time in that resource hungry component?
3. Have you checked the efficiency of your SQL code? ie are you doing any full table scans or large sorts. Turn trace on and check it out now. PL/SQL is also NOT renowned for its procedural (non-database) performance. Are there any indexes you could REMOVE before the load and rebuild afterwards that would speed things up.
4. You mentioned an "external program" so how much network traffic is there?
5. Are you using batch inserts once you've validated that the new rows should be inserted or are you doing single inserts?
6. Do you have archiving turned on? Does your database hang while REDO Logs are being archived? Is REDO Log activity a bottleneck?

Thoughts on data loads.

1. SQL*LOADER is very efficient. Consider running your "external program" just to validate the rows and produce a validated file for sql*loader to do bulk inserts with.
2. Split your input file and run multiple "external programs" simultaneously. This will grind your machine but will give better performance provided everything else is tuned. If you use (a) above then your "external program" becomes mostly a reader so running MANY of them should give very good performance.
3. Consider downloading smallish lookup tables into your "external program" rather than having the database check them. Process records in "batches" rather than a row at a time.

Hope that's of some help.

Others will have to guide you further as I'm going on holidays for 4 weeks :)

Enjoy.

--
  -----------------
   T T T T T T T T        Bruce Pihlamae
   I I I I I I I I        bpihlama_at_nla.gov.au
   I I I I I I I I        National Library of Australia
   T T T T T T T T        Phone: +616 262-1575
  -----------------       Fax:   +616 273-2116
 ===================
 "If you swallow a live frog first thing in the morning;
  nothing worse will happen to either of you that day."