Geek Answers Handbook

OracleBulkCopy vs SQL * Loader Performance

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

I have a very simple 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 an intermediate table for obtaining voluminous data in a database.

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

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

OracleBulkCopy, according to the documentation, * "... uses a direct path loading approach that is similar to but not the same as Oracle SQL * Loader." * It may not work with SQL * Loader in runtime conditions, but this difference is ridiculous.

After some basic analysis of network traffic, I found that OracleBulkCopy sends and receives a huge number of small packets. I used Wireshark to compare packages for each and found interesting results.

SQL * Loader - after the initial connection is established, it sends a series of 8 kilobytes of packets (TNS protocol) and receives 60 bytes of ACK in response.

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

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

So my questions are:

  • Has anyone seen this behavior?

  • Does OracleBulkCopy really work like a bulk upload tool?

  • Is there something I need to configure in order to make it work correctly? (client / server settings, etc.)

Any help is greatly appreciated.

+8
oracle sql-loader
source share
2 answers

I received a response from Alex Keh, Oracle Product Manager, that Oracle also noticed this problem. We are evaluating how to fix this error.

Therefore, in answering my own questions, I believe that OracleBulkCopy does not work well as a bulk upload tool. At least for now.

I will wrap SQL * Loader as an alternative solution, as there is no ETA in error correction.

+4
source share

Does this issue also occur when using a DataTable as a source? Or is this only related to using IDataReader?

+1
source share

More articles:


All Articles