Re: High volume loading to Oracle 8.x or Oracle 9i

rjerez_at_extensity.com (Roberto Jerez) wrote in message news:<7ca12392.0112191054.60e6484e_at_posting.google.com>...
> I have several Java applications that load large amounts of data into
> Oracle 8.x and Oracle 9i databases. I use Java JDBC to build batch
> updates of around 2,000 rows and submit them to the database for
> insert. The volume of data is increasing and the load time is becoming
> unacceptable. My suspicion is that the database logging and index
> building take up some time. So I removed the indexes from the database
> (I apply them after the data is loaded) and this has improved the time
> by about 20%. However the logging is still on.
> My question: Is there a way to disable database logging during a batch
> load operation while still using JDBC APIs? The database is offline
> during the load process.
> Thanks for any replies
> Roberto Jerez

I would not start on this route rightaway. Experience dictates this is usually a result of insufficient online redolog files and redolog files of insufficient size. By disable database logging, you will compromise your options to recover the database when disaster strikes. And believe, Murphy never sleeps, and at that day you will regret you ever disabled logging. So first of all
- how big are your online redolog files (select bytes from v$log) a workable minimum is 5M
- how often do you checkpoint? (show parameter log_checkpoint_interval and log_checkpoint_timeout)

Then: JDBC is, AFAIK, not capable of loading data in direct mode. Sqlloader and Pl/sql are. This data won't be logged, though remember all your indexes will be invalid, and have to be rebuild after a direct load.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Thu Dec 20 2001 - 11:01:41 CET