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 [Quoted]
>painfully slow on massive INSERT's....
>

[Quoted] [Quoted] Do you have indexes? Have you done this without them, rebuilding afterwards? [Quoted] [Quoted] Have you pre-created from a compressed export and truncated the receiving table? [Quoted] 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 [Quoted] [Quoted] 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 [Quoted] 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_at_eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the _at_#%*& DBA!