Slow Load Performance

Hello experts:

We are experiencing very slow insert performance in one of our DW fact tables. The fact table is just under 100 millions rows currently, is partitioned by month and has 6 bitmap and 3 B-Tree indicies. (The Oracle database is v8.1.7.3).

The basic process that we are using to load the table is:

1. Fetch 100,000 rows using a BULK COLLECT (i.e. using PL/SQL Tables for the array)
2. Assign the dimension keys through PL/SQL functions
3. Insert the 100,000 rows into the fact table
4. Delete the arrays
5. Start again

With this we can't seem to get over 60 rows/second on a 16 CPU SUNFIRE!!Yikes.

We have also tried a simple INSERT AS SELECT where we have preassigned the dimension keys during a staging step and the INSERT took 2.5 hours for 450,000 rows.

Any ideas on what could be slowing these inserts down . . . I am currently suspicious of the indexes.

Thanks very much.

Barry Received on Mon Jul 21 2003 - 16:59:32 CDT