Can you please help me diagnose, explain, and possibly solve the problem of unstable peformance of mass insertions in Oracle?

I need periodically to load large amounts of data into an Oracle database. Since it must be done automatically, as part of a complicated business process that involves many other operations, and inside a transation, dedicated data-migration tools are probably out of the question, efficient though they may be. I therefore have written a .NET program that accesses Oracle via ADO.NET using Oracle's managed driver. For maximum performace, it loads data into the database in a series of parametrised INSERT ALL statements of the form:

/* a sample INSERT ALL statement for 4 rows */
   VALUES( :val_0_0, :val_0_1, :val_0_2, :val_0_3, :val_0_4 )

   VALUES( :val_1_0, :val_1_1, :val_1_2, :val_1_3, :val_1_4 )

   VALUES( :val_2_0, :val_2_1, :val_2_2, :val_2_3, :val_2_4 )

   VALUES( :val_3_0, :val_3_1, :val_3_2, :val_3_3, :val_3_4 )

pregenerated and prepared for a certain number of rows, which is configurable and which I call batch size, so that each iteration of the data-loading loop need only supply the parameter values.

The performance of this algorithm in our test environment—where client and server are in the same local network—is quite expectable: it improves with increasing batch size at a diminishing rate as the constant overhead due to network communication between client and server becomes smaller in relation to the time to process a batch.

In the client's enviromnent, however, where the network is less reliable and a ping from client to server takes 35 ms, the situation is—at least in my inexpert opinion—anomalous: there seems always to be a single best batch size that depends on table structure. Shown below are the results of three tests for two tables. During each test, the program was caused to insert the same data into the table using different batch sizes, and the duration of each run measured:

  Table A, test I     Table A, test II    Table B, test I    
+----------------+  +----------------+  +----------------+
|Rows: 100 000   |  |Rows: 100 000   |  |Rows: 6000      |
|Cols: 20        |  |Cols: 20        |  |Cols: 60        |
+----------------+  +----------------+  +----------------+
| batch  time, s |  | batch  time, s |  | batch  time, s |
|   4      910   |  |   16     252   |  |    8      54   |
|   8      463   |  |   64      65   |  |   16      36   |
|   16     235   |  |  256      30   |  |  [32]     18   |
|   32     120   |  | [384]     26   |  |   64      19   |
|   64      64   |  |  512      31   |  |  128      43   |
|  128      35   |  | 1024      90   |  |  256     318   |
| [256]     23   |  +----------------+  +----------------+
|  384      34   |
|  512      34   |
|  768      44   |

The optimal batch size in each set is indicated in brackets. My conjecture is that the optimum is determined not by the number of columns in each table, but by the amount of data that is sent over network per each row. Do you know the cause of this behavior or should you recommend that I perform more detailed tests with variable number and types of columns—including different lengths of NVARCHAR values—in order to determine the exact effects of each factor?

If my conjecture turns true, coming up with an efficient algorithm is going to be a non-trivial task, for it will require carnal knowledge of the driver's serialisation algorithm. Is it published anywhere, that one may consult it to calculate the serialised size of a data row and use that to ensure the serialised size of the batch is within the environment-specific optimum? And if it is impossible, then I am at a loss and have no more ideas how to make my program perform well on different tables with different amount of data in each row. Have you any suggesions?

This question assumes network communication is the culprit, but I will appreciate advice on testing this assumption.

P.S.: I realise such questions as this are better answerd in the course of a free discussion than in a Q&A format, but I have not found active mailing lists, newsgroups, or IRC channels about Oracle.

I have found prepared statements faster than ad-hoc parametrised ones, and a single INSERT ALL faster than multiple INSERT statements in a batch.

Except at the last iteration, when it has to generate and execute another statement if the total number of rows to insert is not a multiple of batch size.

  • So, looking briefly at your test results, is it saying unpredictable at certain times, or just certain table sizes? If at different times, would it be possible to run a ping command every say, 10 minutes at the client site, pinging the server from the client, and saving the results in a time-stamped file, and then when you see a long batch load, see if ping response time was bad too. Once I had to resort to a client visit, and run a network sniffer to find the cause! – Mark Stewart Oct 16 '20 at 19:14
  • 1
    @MarkStewart Most of the time, the client's network has a stable high latency, and the conditions remained stable throughout each test. This question is about the performance of Oracle in these conditions. For every given table and dataset, an a single optimal batch size seems to exist. In other words, the dependency of the processing time on batch size always has a single well-defined minumum, and I wonder why, for the effect of batch size on speed is very strong, and I don't want to be running with suboptimal performance. In our case, It is matter of waiting 4 or 16 hours! – Ant_222 Oct 16 '20 at 19:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.