ODP.NET

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 necessarily 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.