Re: Slow Load Performance

Some comments:

- Import 1000 rows at a time (100,000 is way too high).
- Commit only at the end, if you can afford rollback segments big enough).
- Disable ALL the indices before importing, and rebuild them afterwards.
- Don't use arrays, use nested tables (indexed by binary_integer).
- Use DBMS_PROFILER to see where the programs spends more time.

Daniel

> 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 Tue Jul 22 2003 - 10:01:35 CDT