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
INSERT ALL statements of the form:
/* a sample INSERT ALL statement for 4 rows */ INSERT ALL INTO TESTTAB( TEXTDATA1, TEXTDATA2, DECIMALDATA, DOUBLE) VALUES( :val_0_0, :val_0_1, :val_0_2, :val_0_3, :val_0_4 ) INTO TESTTAB( TEXTDATA1, TEXTDATA2, DECIMALDATA, DOUBLE) VALUES( :val_1_0, :val_1_1, :val_1_2, :val_1_3, :val_1_4 ) INTO TESTTAB( TEXTDATA1, TEXTDATA2, DECIMALDATA, DOUBLE) VALUES( :val_2_0, :val_2_1, :val_2_2, :val_2_3, :val_2_4 ) INTO TESTTAB( TEXTDATA1, TEXTDATA2, DECIMALDATA, DOUBLE) VALUES( :val_3_0, :val_3_1, :val_3_2, :val_3_3, :val_3_4 ) SELECT * FROM DUAL
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 | |  18 | | 32 120 | |  26 | | 64 19 | | 64 64 | | 512 31 | | 128 43 | | 128 35 | | 1024 90 | | 256 318 | |  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
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.