RE: insert performance

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: <arvind.kumar2@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Mon, 25 Sep 2006 08:12:33 -0500

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
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
5.      see if you can improve the select performence

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kumar, Arvind IN GGN SISL
Sent: Monday, September 25, 2006 7:17 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: insert performance
      Dev Env.  -  Oracle Database 10g Enterprise Edition Release, 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
Arvind Kumar
Associate  Consultant
Siemens Information Systems Limited
e-mail : arvind.kumar@xxxxxxxxxxx

Other related posts: