OracleBulkCopy Performance

Question

My C# .NET app writes millions of records to a temp table using OracleBulkCopy. Every time the app has 20000 records or more in memory it call OracleBulkCopy to write all of them in a single batch (I am using batch size = 0). At the beginning the table is empty and it takes just 2 seconds to write the whole batch. During the process the time for writing the batch increases. After 2.000.000 records each call takes almost 25 seconds. I am using the default options of OracleBulkCopy. Doing some tests when the table reaches 40.000.000 it takes almost 3 minutes to write 20.000 records.

I would like to know if there is any recommendation (I couldn't find anything about it) in order to maintain an almost constant time for each call. Am I doing anything wrong?

Answer 1

Seems like strange behaviour. Have you traced the database session doing the inserts to see what it is waiting on?

ALTER SESSION SET timed_statistics=TRUE;
ALTER SESSION SET max_dump_file_size=UNLIMITED;
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

99 times out of 100, the trace results will point you at the problem in these strange slow down cases.