Re: Oracle is painfully slow when doing massive INSERTs
Date: 1998/03/12
Message-ID: <3507ff71.28105486_at_www.sigov.si>#1/1
On Thu, 12 Mar 1998 08:38:53 +0100, "Morten Myrvold" <someone_at_somwehere.no> 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)
If PKEYs exist, then indexes are also there and must also be processed during inserts, which means significant overhead. I belive the performance of bulk inserts will be much better if you drop the PKs and recreate them after your insert is done.
When you recreate the PKs, insert the line "EXCEPTIONS INTO <exceptions_table>" into your "ALTER TABLE ADD PRIMARY KEY ..." commands. That way, if there are any rows that violate the PK constraints, your PK generation will fail, but you'll trap all the rows that violate the constraint and you'll be able to process those rows as you want (DELETE, UPDATE). After that, simply reisue your "ALTER TABLE ADD PRIMARY KEY ..." command.
>>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.
>
>>Have you tried creating a dblink and inserting with a select statement?
>>(Assuming you have software to do this).
>We don't currently have the s/w to do this.
>
>>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?
>We're using Oracle 7.3.3.0.0, with all the default settings (whatever that
>is).
If this means your DB was created during your Oracle instalation then your log files are far too small for bulk loads (and you have only 2 of them, unmirrored!). Check your "alert_<SID>.log" file and I belive you'll find many of the following messages in it: "Checkpoint not complete". That means your log files are too small and/or you have too few of them.
>And yes; all our Oracle files reside on the same disk.
Hm, I hope you know that this is not considered a good DB design ;-). At least try to separate your log files on another disk.
The best advice would be: get an Oracle DBA to check your instance and the database. Oracle RDBMS is a beast that does not perform very well if you just install it out-of-the-box - it demands a DBA with some knowledge and experience to set it up properly. Otherwise it'll work, but don't expect top performance from it.
Regards,
Jurij Modic Republic of Slovenia jurij.modic_at_mf.sigov.mail.si Ministry of Finance ============================================================The above opinions are mine and do not represent any official standpoints of my employer Received on Thu Mar 12 1998 - 00:00:00 CET