Re: Oracle is painfully slow when doing massive INSERTs

Morten Myrvold wrote:

> Joel Garry wrote in message <6e78ev$sdj$1_at_pebble.ml.org>...
> >Do you have indexes? Have you done this without them, rebuilding
> afterwards?
> No, there are no indices (PKEYS exist, they're needed to verify that the
> data is valid)
>

Well, PKs are indexes. Which means the bulk load is gonna be slow. If you want the database to check the uniqueness for ya, it's gonna be slower than a bulk load... ;-)

> >Have you pre-created from a compressed export and truncated the receiving
> table?
> Not sure I understood your question, but the destination database is created
> from the bottom up, starting with a CREATE TABLESPACE.
>

It means, have you pre-allocated the space for the table instead of letting ORACLE do that for you automatically? Won't magically improve things, but it helps.

> We're using Oracle 7.3.3.0.0, with all the default settings (whatever that
> is). And yes; all
> our Oracle files reside on the same disk. I'll try checking the SGA...

Well, that will be slow. The ORACLE redo log files get hit all over the place on an insert, so do your database files during a commit and the rollback segment when changing an index. All of that activity can't be good for a single disk, no matter how fast. Don't you reckon? You'll also need some minor tuning of the parameters for max efficiency, but right now there are other more effective bottlenecks in your configuration...

HTH Cheers
Nuno Souto
nsouto_at_acay.com.au Received on Sun Mar 15 1998 - 00:00:00 CST