Re: Oracle is painfully slow when doing massive INSERTs

Morten Myrvold wrote:

> Hi all...
>
> Hope someone can help me with this one (kinda newbie question):
>
> I've written a utility that essentially transfer tables from one database to
> another (different RDBMSes). To transfer a table consisting of
> 30000-something rows takes 18+++ mins to complete (number of columns doesn't
> seem to affect this). I'm using an ODBC cursor for selecting rows from the
> source DB and a dynaset (OO4O21) for inserting into Oracle.
>
> What I'd like to know is: Is there a way for me to speed up this process.
> I've tried changing the COMMIT frequency (also tried not using transactions
> at all), but these adjustments did very little to the overall transfer
> time... If it means I'll have no way of rolling back transactions, so be
> it... I don't really care - I just want the destination database to be an
> exact replication of the source database (and the transfer time as low as
> possible, of course).
>
> Timing the transfer process revealed that as much as 90%++ of the time is
> spent on INSERTing into Oracle.
>
> I've tried running my databases on (almost) every possible configuration (on
> the same computer, on two different HDs/controllers on one computer and on
> two separate computers) - all with the same result. I've also made sure that
> the network traffic is as low as possible, so that's not it either.
>
> It seems to me that it all boils down to one thing: Oracle's just plain
> painfully slow on massive INSERT's....
>
> Any ideas, anyone?
>

You did mention any indices that are kept on you destination table. Large amounts
of time can be spent updating the various indices on the destination table if they are complex. Also, if the rows being inserted are already ordered, updates

to indices and be particularly slow.

If you are keeping indices, try dropping them before doing the insert, and then recreating them afterwards.

Mark Received on Wed Mar 11 1998 - 00:00:00 CST