OracleBulkCopy vs SQL*Loader Performance

Question

I am seeing some significant performance differences between OracleBulkCopy (ODP.NET) and SQL*Loader when the Oracle server is on another machine.

I have a very basic table in Oracle with three columns (one BINARY_FLOAT, two NUMBER(18,0)). There are no primary keys, indexes, triggers, etc. It is used as a staging table to get bulk data into the DB.

SQL*Loader takes about 27 seconds to load 4.5 million rows into the table.

OracleBulkCopy takes about 10 minutes to load just 1 million rows.

OracleBulkCopy, according to the documentation, *"...uses a direct path load approach, which is similar to, but not the same as Oracle SQL*Loader."* It might not be up there with SQL*Loader in terms of performance, but this difference is ridiculous.

After some basic analysis of the network traffic, I found the OracleBulkCopy was sending and receiving a huge number of small packets. I used Wireshark to compare the packets for each and found some interesting results.

SQL*Loader - after the initial connection handshaking - sends a series of 8 kilobyte packets (TNS protocol) and receives 60 byte ACKs in response.

OracleBulkCopy sends a series of 102 byte packets (TNS protocol) and receives a 133 byte packet (TNS protocol) in response. What the...!? It is like it is sending one row at a time!

With the OracleBulkCopy class, I am using a batch size of 100,000 and am using a custom IDataReader to read from a data file.

So, my questions are:

• Has anyone ever seen this behaviour?

• Does OracleBulkCopy actually perform as a bulk loading tool?

• Is there something I need to configure to get it to work properly? (client/server settings, etc)

Any help is much appreciated.

Fullerton

I'm having similar insert times using a datatable. Even worse, a couple columns are of type timestamp so I have to set the datatable column to an OracleTimeStamp type which also seems to take a good hit when I profile the application. Were you able to get bulkcopy sped up? Popping out to the commandline to run sqlloader seems like a really poor way to handle large files on a web server! – Coulombe

Answer 1

I received a response from Alex Keh, Oracle Product Manager, that Oracle "has noticed this issue as well. We're evaluating how to fix this bug."

So in answer to my own questions, I guess OracleBulkCopy does not perform well as a bulk loading tool. At least not at the moment.

I will be wrapping SQL*Loader as an alternate solution as there is no ETA on the bug fix.

Fullerton

Out of curiosity, did you try array binding? – Civil

Yes, we use array binding for most of our data loading. In this particular instance, however, we are loading tens of millions of rows of data into the database and we don't need a lot of logic around it - just fast loads and array binding isn't quite fast enough. – Fullerton

@AdrianBrown - If you are still monitoring this thread, do you remember which version and if where were any updates on the bug fix? – Hundredth

@Hundredth - Sorry, I don't remember which version. I got fed up waiting in the end so again, don't know if the bug was fixed. We ended up moving to SQL Server anyway, but for completely different reasons. :) – Fullerton

@AdrianBrown - No problem, it was quite a while ago. Thanks for getting back to me! – Hundredth