Re: Optimisation of mass INSERTs

You can ignore the bit you've quoted from the Burleson website. It sounds like a misunderstanding of how things used to work some time around Oracle 8.

Then watch out for the (very common) quick in Oracle parameters where the word NO appears in the parameter name, making it easy to interpret parameter completely back to front. "tcp_nodelay = YES" means "I do not want a delay" (i.e. switch off the Nagle algorithm) and it's exactly what Oracle needs because if Oracle has passed a data unit to the tcp stack it's not going to pass another one until the tcp layer says it has successfully transmitted it.

I believe you are correct that the volume of data sent is an important factor in what's going on here. but there are other factors involved, My understanding of the process is as follows:

When you do array processing Oracle will fill a session data unit (SDU) with complete rows (except in the special case where one row is bigger than the SDU, of course). There is a layer of software in the Net services that will do some de-duplication of the data, but I don't think the session layer doesn't know about that..

The Net layer passes the SDU packet to the tcp layer which has (or used to have) a "transmission data unit (TDU)" that you could set in much older versions of Oracle. The settings you have to worry about now is the send_buf_size and recv_buf_size. The default and maximum values for all three parameters are version dependent so if you need throughput you can increase these to their maximum at a cost of every session needing to be able to allocate the buffers when they need them (i.e. increased memory demand).

The significance of the send/recv buffer sizes is that the tcp layer doesn't require an ACK from the other end for every transmission unit it sends (and that may be about 1400 bytes, or about 9K if you have enabled large frames). This can make a big difference to the network trip time. This reduction in the number of ACKs MIGHT explain why a larger arraysize results in a lowering of the sweet spot for the unstable network: imagine I send 2MB at a time on an unstable network with a small loss/corruption rate I may have to resend a 2MB packet several times; if I send 2MB as 4 packets of 512KB then I may only have to retransmit one of those packets once.

The other factor that may answer the anomaly when the network is sound, is the time it takes Oracle to handle a large number of bind variables. Lothar Flatz raised a question on this list a couple of weeks ago about why a statement with several thousand bind variables was taking extreme amounts of CPU for Oracle to process. I don't recall if there was a definite resolution to the problem, but if you pass a large array of bind variables then the session at the far end has to allocate a lot of memory and spend CPU constructuing and filling the array. If you can set up the experiment it would be worth checking whether your increased roundtrip time corresponds to an increased CPU usage at the server as the arraysize or row width increases.

If you want to experiment with adjusting the sdu_size and send/recv_buf_size remembet that they have to be adjusted at both the client and server.

Regards
Jonathan Lewis

On Fri, 6 Nov 2020 at 21:46, Anton Shepelev <anton.txt_at_gmail.com> wrote:

> Hello, all.
>
> I am working on a program that should effect transactional
> upload of massive amounts of data into Oracle tables using
> managed ODP.NET driver. Upon initial experiments, I have
> settled on transferring data in a series of "batches", each
> of which is a parametrised INSERT ALL statement of the form:
>
> 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
>
> with a customisable number of rows. My tests show that it is
> at least 1.5 times more efficient than a batch of equivalent
> INSERT statements inside a BEGIN..END block. The performance
> of this method on the client's site manifests two unusual
> tendencies, which I can't explain. I suppose them connected
> somehow with the length of the network route between client
> and server, which takes 36 ms to ping, but am still at a
> loss as to the exact explanation.
>
> The first anomaly is the effect of batch size. For each
> target table and dataset, there appears to be a single
> optimal number of rows in a batch, so that both smaller and
> larger batches work considerably slower. Performace
> measurements for two tables with various batch sizes are
> shown below, the respective optimums put in brackets (view
> in a fixed-width font):
>
> Table A Table B
> +----------------+ +----------------+
> |Rows: 100 000 | |Rows: 6000 |
> |Cols: 20 | |Cols: 60 |
> +----------------+ +----------------+
> | batch time, s | | batch time, s |
> | 16 252 | | 8 54 |
> | 64 65 | | 16 36 |
> | 256 30 | | [32] 18 |
> | [384] 26 | | 64 19 |
> | 512 31 | | 128 43 |
> | 1024 90 | | 256 318 |
> +----------------+ +----------------+
>
> I can understand why smaller batches are inefficent: it must
> be due to the overhead of frequent call-and-response between
> client and server, but why performace starts to degrade
> again as batch size increases past the optimum?
>
> The second anomaly occurs when the network becomes unstable.
> During these intervals, which occur once or twice a week,
> `ping' shows that a considerable portion of packets are lost
> and the percentage of lost packets grows with packet size.
> At these times, the general pattern with a single optimum
> remains, but the optimal batch size is much lower than when
> the network is feeling well. Again, I have no idea what may
> be causing this.
>
> I conjecture that it is a case of leaky abstractions[1]:
> since the ODP.NET driver cannot entirely abstract the
> ADO.NET interface from the gritty details of network data
> transfer, batch size affects both the size and the number of
> network packets required to send one batch, which in turn
> has a tremendous effect on throughput. But what can I do
> about it? If my conjecture be right, I have to optimise the
> serialised size of the batch, rather than the number of rows
> in it, and I cannot reliably to that because the
> serialisation algorithm is not officially documented.
>
> I have considered prepared statments, which should save
> about 60% of traffic, but Oracle have chosen not to
> implement the .Prepare() method provided by ADO.NET. The
> official documentation describes the implementation as "no-
> op":
>
> ODP.NET: OracleCommand Public Methods:
>
> https://docs.oracle.com/database/121/ODPNT/OracleCommandClass.htm#g1099930
>
> It is noteworthy that JDBC, on the contrary, does support
> parametrised statements:
>
> JDBC: Using Prepared Statements
> https://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html
>
> In spite of what some experts say about their obsolescense
> due to improved server-side caching of compiled statements,
> prepared statements remain superior because they save
> network traffic by letting the client send the statement id
> instead of the entire SQL text. I have not yet tried
> preparing them manually via PL/SQL as descrbied here:
>
> EXECUTE and PREPARE:
> https://docs.oracle.com/cd/B10501_01/appdev.920/a97269/pc_13dyn.htm#865
>
> but in any case it can only improve performace by a constant
> factor, but cannot remove the need of optimising batch size
> for each table, data sturcutre, and network condition.
>
> I have also tried a dynamic optimiser that measured
> performance in real-time and adjusted batch size
> accordingly, but it was not very efficient because I do not
> know how to predict the serialised batch size, in terms of
> bytes actually sent over the network.
>
> While reading performace-related documention, I came upon
> the TCP.NODELAY setting:
>
> TCP.NODELAY
>
> https://docs.oracle.com/cd/E18283_01/network.112/e10835/sqlnet.htm#CIHCAJGA
>
> Is that article correct in that it is `yes' by default? It
> should seem strange, because all the commentor about this
> setting discuss enabling it as very non-standard, e.g. Don
> Burleson writes:
>
> ,----[http://www.dba-oracle.com/art_builder_tns.htm:]
> | Oracle Net, by default, waits until the buffer is filled
> | before transmitting data. Therefore, requests aren't
> | always sent immediately to their destinations. This is
> | most common when large amounts of data are streamed from
> | one end to another, and Oracle Net does not transmit the
> | packet until the buffer is full.
> `---------------------------------
>
> Who is correct, and how can I test the actual default value?
> Do you think TCP.NODELAY could optimise massive data
> transfers? I am asking this because I can't easily test at
> client's site because of bureaucracy, while local testing is
> nearly useless in our fast local network.
>
> Antoher article, about TCP/IP optimisation in general,
> mentions the TCP_ACK setting in connexion with TCP_NODELAY:
>
> Best Practices for TCP Optimization in 2019:
>
> https://www.extrahop.com/company/blog/2016/tcp-nodelay-nagle-quickack-best-practices/
>
> Can it, in your opinion, affect massive data transfers, and
> if it can, then how to set it for Oracle connections?
>
> For your reference, I already asked this question on DBA
> StackExchange, but it drew no replies, and does not seem to
> be going to:
>
> Anomalous performance of mass INSERT in Oracle:
>
> https://dba.stackexchange.com/questions/278185/anomalous-performance-of-mass-insert-in-oracle
>
> Can you please help me diagnose and explain these anomalies,
> and possibly solve the problem of unstable peformance of
> mass insertions in Oracle? What monitoring tools can I use
> to determine the bottleneck?
> ____________________
> 1.
> https://www.joelonsoftware.com/2002/11/11/the-law-of-leaky-abstractions/
>
> --
> () ascii ribbon campaign -- against html e-mail
> /\ http://preview.tinyurl.com/qcy6mjc [archived]
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l