also, ensure that your pl/sql procedure to generate dimension keys is
optimized. How much processing does this take? can they be 'pre-generated'
and stored in a global temp table?
"Ryan" <rgaffuri_at_cox.net> wrote in message
news:sOcTa.865$5b7.805_at_lakeread01...
> 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:31:47 CDT
