performance issue during loading

We are having a performance issue loading our fact table. We are only able to process 10 records per second when processing the PL/SQL code that transforms the fact table.

Simple facts

HP-UX 11
Oracle 8.?
2 CPUs
1G RAM
6 mirrored pairs disk
fact file = 120Meg = 400,000 rows

We load this data into a stage table first, and then use PL/SQL packages to validate business rules and load the fact table.

We have only been able to achieve 10 records per second loading the fact table. The size of the file mentioned above is not relevant. A file of 150K rows loads just as slow. We do not have much data in the fact table yet either. We just started loading.

Our PL/SQL is fairly straightforward, but does about 9 lookups into other tables for validation. We are thinking about making the smaller table lookups into array lookups. Will this help? Most of the lookups are on very small tables (< 20 rows). Two of the lookups are on larger tables (100K - 200K rows). Are we doing too many lookups per record?

Also, the cursor on the stage table is implicit. Should this be explicit for perf reasons?

We are also issuing a commit after each row. Should we change that to every 100, or every 1000 rows?

All of the things I can think about are code related since I am not a DBA. We can tweak the code, but I am looking for an order of magnitude of improvement that may not be possible tweaking code.

Does anyone have any other recommendations that may help, or any benchamrks to compare with? I think that 10 records a second is poor, even if we perform 9 lookups.

Other DB things we have tried
We moved data files around disk to spread i/o issue. We have not yet used table partitioning. We are dropping indexes on fact table.
A DBA has performed general DB tuning.

Thanks for any help on this.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Mar 10 1999 - 07:07:33 CST