Re: Oracle is painfully slow when doing massive INSERTs

In article <6e82ob$9pm$1_at_elle.eunet.no>, 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)
>
>>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.

Ah, perhaps you should try starting with a loaded table, TRUNCATE the table, and see if reloading it doesn't go faster. Also, the default table creation parameters may be off.

>
>>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). And yes; all
>our Oracle files reside on the same disk. I'll try checking the SGA...

Oracle's default settings are not too good. And Oracle explicitly recommends spreading the load across disks. It will work on one disk, but if you then complain about performance, that's just whining! :-) I've seen it work ok on single disk setups anyways, but a mass load may choke it.

You need to get an experienced consultant, or a couple of good performance tuning books and a comfy armchair. In particular, you want to deal with db_blocksize, rollback segments, db_block_buffers, db_file_multiblock_read_count, logfiles, row chaining, pctfree, pctused, initial, next and probably some other stuff.

>
>
>
>

-- 
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!