RE: insert performance
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <arvind.kumar2@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Sep 2006 07:25:46 -0400
I believe you have already identified your root cause, and you need to
figure out how to proceed from generating the .csv file to loader or
datapump in an automated fashion.
Trying *everything* to maximize the throughput of single row at a time
insert from values is going to yield marginal results at best. Presuming
datafactory is passing each of these insert commands to the database
individually and getting an ack (even a local one) to send the next one
injects an incredibly large interrupt driven overhead compared to an array
aware tool reading big chunks of an input file at a time, plus you're
getting all the overhead of row by row operations.
So if you need to avoid baby sitting the process you need to enchance your
process in one of the following ways:
1) Get whoever makes datafactory to add an option to close on action
completion.
2) Run some supervisor shell and figure out when the datafactory
window is complete.
Then, as you've already figured out, you can proceed with appropriate Oracle
tools to complete the job. Usually running a "unixlike" shell facilitates
this sort of thing. Cygwin and MKSToolkit come to mind for windows. In your
case, it would probably be unusual for the datestamp on the .csv being
generated to stop changing for more than a few seconds if the datafactory
command saving the .csv file is incomplete. If datafactory can be given
multiple directives, just not close, then create a small dummy file after
the generation of the .csv is incomplete. Or if datafactory closes the file
when it is done writing maybe you can just see whether it is still open by
anyone.
Good luck,
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Kumar, Arvind IN GGN SISL
Sent: Monday, September 25, 2006 11:30 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: Ric Van Dyke
Subject: RE: insert performance
<snip>
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.
<snip>
Other related posts:
