Re: Oracle is painfully slow when doing massive INSERTs

I don't have any DBA experience but I work closely with our DBA on tuning our database, so please correct me as needed.

Regarding PK's, the data that you are getting from your source database should be validated before trying to transfer over to the destination db. With that assumption, you can drop the PK and pick up any problems when you try to recreate. Someone mentioned spooling the source data to a flat file on the destination machine then using sqlldr to do a direct load. That is one method that we use here and it is much faster than using a dblink; it is about 2 to 3 times faster. Direct load using sqlldr ignores all indices and constraints, including PKs.

You may want to invest in more disks and spread the datafiles so that Oracle can access them in parallel. We went from wide SCSI to ultra-wide SCSI configuration and our load process finished 3 times faster.

A good book to pick up for various tuning topics is O'Reilly's Oracle Performance Tuning. The book covers both application and database tuning, along with various tips.

Good luck.

Chi

Morten Myrvold 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.
>
> >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...
Received on Thu Mar 12 1998 - 00:00:00 CST