Re: Oracle is painfully slow when doing massive INSERTs

In article <6e6m9p$11j$1_at_elle.eunet.no>, Morten Myrvold <someone_at_somwehere.no> 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....
>

Do you have indexes? Have you done this without them, rebuilding afterwards? Have you pre-created from a compressed export and truncated the receiving table? Have you tried creating a dblink and inserting with a select statement? (Assuming you have software to do this). How about selecting to a flat file and using a direct SQLLOAD? Are you running in archivelog mode with too small logfiles (how often are the switches?)? How big is your SGA? Perhaps it is too big causing swapping? Are all your Oracle files on one disk?

>Any ideas, anyone?
>
>
>
>Thx in advance,
>
>
>Morten Myrvold
>Systems Consultant,
>Sysdeco Profdoc AS, Norway
>
>
>

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!