Re: Slow Load Performance

it is NOT faster to first fetch a large amount of records to a pl/sql table then forall inserting them. You only use this if you need to do sometyhing with the pl/sql table.

Also, it is NOT faster to bulk collect 100,000 records at once. Use the 'limit' clause and set it to 1,000 records at a time. This is on asktom. So is the code. Your better off doing an insert select. Its faster and MUCH faster. You should be able to assign the dimension keys with a sql statement. Make a function based index to speed this up.

Also, disable all of your indexes. These kill insert performance.

If your using a partitioning optioning use the parallel option with as many pqslaves as you can afford at the time. This is VERY fast.

when you re-enable your indexes use the parallel option. Submit each index as a job with DBMS_JOB and do all 9 indexes at once. This is VERY fast and uses up a higher percentage of your CPUs than doing it one at a time(thereby increasing efficiency).

also use index /*+ append */, make sure nologging is on, and turn off archivelog mode.

if you were doing the insert without append, without nologging and with the indexes on you can probably knock an hour or more off your load doing it this way. Possible alot more if you can use alot of PQslaves and you do not have alot of other I/O going on at the same time. "Barry Hensch" <barryh_at_quadrus.com> wrote in message news:5757a2ca.0307211359.4a6450dc_at_posting.google.com...
> 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:30:40 CDT