RE: insert performance
- From: "Kumar, Arvind IN GGN SISL" <arvind.kumar2@xxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Sep 2006 08:59:39 +0530
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@xxxxxxxxxxx
________________________________
From: Ric Van Dyke [mailto:ric.van.dyke@xxxxxxxxxx]
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Kumar, Arvind IN GGN
SISL
Sent: Monday, September 25, 2006 8: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:
