Batch data load
From: Lok P <loknath.73_at_gmail.com>
Date: Tue, 6 Apr 2021 00:29:25 +0530
Message-ID: <CAKna9VaXV4-OkU0_oDsDt+Fd0B-Qip7HNT9-zOC3rYOh3po+=Q_at_mail.gmail.com>
Hi Listers, Its version 12.1.0.2.0 version of Oracle. We were having a row by row insert happening and was inserting ~90million rows daily into a table from Java and the avg response time per row insert was approx ~.4milli second/row insert. We have just converted it to batch insert with a batch size of ~50 and we observed the response time dropped to~.2 milliseconds/row insert in production. Then we again increased the batch size to ~80 but this time we are not seeing any further improvements. And we realized this thing in production because we don't have any performance environment as such to test it.
Date: Tue, 6 Apr 2021 00:29:25 +0530
Message-ID: <CAKna9VaXV4-OkU0_oDsDt+Fd0B-Qip7HNT9-zOC3rYOh3po+=Q_at_mail.gmail.com>
Hi Listers, Its version 12.1.0.2.0 version of Oracle. We were having a row by row insert happening and was inserting ~90million rows daily into a table from Java and the avg response time per row insert was approx ~.4milli second/row insert. We have just converted it to batch insert with a batch size of ~50 and we observed the response time dropped to~.2 milliseconds/row insert in production. Then we again increased the batch size to ~80 but this time we are not seeing any further improvements. And we realized this thing in production because we don't have any performance environment as such to test it.
So I want to understand from experts, If this is enough to point towards the fact that we won't get any further improvements if we increase the batch size further or should we increase it till ~254 , as I read somewhere , Oracle internally uses batch size of ~254 and so we may get maximum benefit with batch size of ~254?
Regards
Lok
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 05 2021 - 20:59:25 CEST