RE: insert performance

Thanks all,  

There is no indexes, constraints on these tables. Parent and child relationship has been set in DataFactory itself. The table are already in nologging mode. Problem is that datafactory use the ' INSERT INTO tablename VALUES ('........'); ' syntax to load the data, so I can not even add the /*+ APPEND */ hint.  

One more option is available to me is to save the generated data from datafacotry into .csv files the use SQLLOADER to load into the tables, problem is that I can start the datafactory project from command line but its window does not close after the run so I can not write a batch file to start SQLLOADER when the datafactory job is finished.  

I even tried to use the destructive method "_DISABLE_LOGGING" but this also does not reduce the time it takes to insert.       

Arvind Kumar

Associate Consultant
Siemens Information Systems Limited
e-mail : arvind.kumar_at_siemens.com

From: Ric Van Dyke [mailto:ric.van.dyke_at_hotsos.com] Sent: Monday, September 25, 2006 5:58 PM To: Kumar, Arvind IN GGN SISL
Subject: RE: insert performance  

Not sure if you are doing this but this is a pretty simple way to speed things up. Drop all indexes and disable triggers on both tables, then once the data is loaded recreate the indexes and enable the triggers. The total time here can likely be less then the load with all the indexes and triggers in place.  

Ric Van Dyke

Hotsos Enterprises

Hotsos Symposium March 4-8, 2007. Be there.

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kumar, Arvind IN GGN SISL
Sent: Monday, September 25, 2006 8:17 AM To: oracle-l_at_freelists.org
Subject: insert performance  

Gurus,  

      Dev Env. - Oracle Database 10g Enterprise Edition Release 10.2.0.1.0, Windows 2000 server, Quest datafactory 5.5 to load sample data.  

     How can I improve the Insert performance while loading data into two tables (a parent with one child)? If I am saving the generated data (1 Million rows) from datafactory into .CSV file it takes only 40 minutes , for the same volume it takes 2 hour if loading into Oracle tables.  

    I have set COMMIT_WRITE = BATCH,NOWAIT to avoid log_file_sync Wait event.    

Arvind Kumar

Associate Consultant
Siemens Information Systems Limited
e-mail : arvind.kumar_at_siemens.com  

--
http://www.freelists.org/webpage/oracle-l