Re: Slow Load Performance

Barry Hensch wrote:

> 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

IIRC, V8.1.7 had/has BAD problems involving bit mapped indexes & "large" INSERTs.

If you have the opportunity, I suggest trying the following.

1) DROP the BM indexes
2) Load the data
3) CREATE the BM indexes

What I saw in the past is the BM grow at an exponential rate when "lots" of rows are inserted into the table and the INSERTs took a long time to complete.

A TOTALLY different approach is to enable SQL TRACE for the session loading the data. Use TKPROF to show you where the time is actually being spent during the load. Received on Mon Jul 21 2003 - 18:53:06 CDT