Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Slow Load Performance

Re: Slow Load Performance

From: Tanel Poder <tanel_at_@peldik.com>
Date: Tue, 22 Jul 2003 20:02:40 +0300
Message-ID: <3f1d6e30_1@news.estpak.ee>


Huh, get real man.

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

Hm, what if you got an index on 1,000,000,000 row table. Disable and rebuild it? Every night?

> 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).

Efficiency of what? Efficiency of CPU usage? If you got IO bottleneck, increased parallellism will make it worse.

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

And take full backup after each such operation? Append and nologging are great things, I use them in many places as well, but they have a major problem - unrecoverability. And in HA VLDBs it can be cheaper to take the performance impact of logging, than deal with consequences in case of media failure.

>
> 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.

I'd start from application. I once saw exactly the same case, where a DW ETL process was done using a single serial package, with tight loops doing single row selects. When reviewed the application architecture, used bulk binding and forall, plus set the ETL procedure to work in parallel (not with PQ but recoded it to deal with sets of data), we could significantly increase performance without tweaking Oracle yet still keeping important features like recoverability etc..

Tanel.

> "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 - 12:02:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US