Oracle is painfully slow when doing massive INSERTs

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?

Thx in advance,

Morten Myrvold
Systems Consultant,
Sysdeco Profdoc AS, Norway Received on Wed Mar 11 1998 - 00:00:00 CST