OracleBulkCopy Performance

Question

My C # .NET application writes millions of records to a temporary table using OracleBulkCopy. Each time an application has 20,000 entries or more in memory, it calls OracleBulkCopy to write all of them in one batch (I use batch size = 0). At the beginning, the table is empty, and it takes only 2 seconds to write the entire packet. During the process, the writing time of the party increases. After 2,000,000 records, each call takes almost 25 seconds. I use the default options for OracleBulkCopy. Performing some tests when the table reaches 40,000,000, it takes about 3 minutes to record 20,000 records.

I would like to know if there is any recommendation (I could not find anything about this) in order to maintain an almost constant time for each call. Am I doing something wrong?

Answer 1

Sounds like weird behavior. Have you seen that the database session is inserting to see what it is waiting for?

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, trace results will indicate a problem in these strange cases of deceleration.