RE: insert performance

1. Disable indexes before loading and rebuild afterwards
2. use insert /*+ apped */ as select nologging
3. size rollback segments appropriately to only have to commit at the end.
4. truncate the tables in the beginning instead of deleting them, the FK's will need to be disabled. 40 minutes is a very long time, I'd trace this to see what it is waiting on as I would expect this to be 5-10 times faster.
5. see if you can improve the select performence

   ---

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