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
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@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
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@xxxxxxxxxxx
Other related posts:
