Menu
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.
838
sql must be executed. 2. Disable Indexes and Constraints. For conventional data loads only, the disabling of indexes and constraints can greatly enhance the performance of SQL*Loader.
SQL*Loader is the more efficient method. It gives you more control. You have an option do DIRECT load and NOLOGGING , which will reduce redo log generation, and when indexes have been disabled (as part of direct loading), the loading goes faster.
What is the difference between the SQL*Loader and IMPORT utilities? SQL*Loader can be used to load data from Delimiter separated files and fixed or variable width text. On the other hand, the import utility read files that are generated by other export utility of oracle.
SQL*Loader provides the following methods to load data: Conventional Path Loads. Direct Path Loads. External Table Loads.
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.
166
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
