RE: insert performance

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kumar, Arvind IN GGN SISL
Sent: Monday, September 25, 2006 11:30 PM To: oracle-l_at_freelists.org
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>
 

--
http://www.freelists.org/webpage/oracle-l