Performance Issue in batch insert query
Date: Sun, 11 Sep 2022 11:11:45 +0530
Message-ID: <CAEjw_fi96kVthP7LJrskaUD3yGM93pD5gkD16fKUGWrgmFVdEw_at_mail.gmail.com>
Hi All, We have a 19c migration that happened recently from 11.2.0.4. And post this we are seeing one of the insert query which used to happen in batch has been now happening in row by row basis. This we can confirm from the dba_hist_sqlstat by dividing execution sum(rows_processed_delta)/sum(executions_delta), the number used to be ~1000 before but now it's showing ~1 i.e. showing a row by row insert pattern. This insert used to come from java code and we have no changes made in the java code. So I wanted to understand what could be the reason for this?
Along with the database move to 19C we also moved the websphere from ojdbc6 11.2.0.4 to ojdbc8-19.13.0.0.1. But can it cause this batch size difference even if we have not made changes done in the code?
Below is the code how it look like in javs side
java.sql.Connection connection
PreparedStatement statementfront = null;
StringBuilder stbuilder = new StringBuilder(1000);
stbuilder.append(" INSERT INTO TAB1 (c1, c2, c3,.....");
stbuilder.append(" ........");
stbuilder.append(".....,");
stbuilder.append(" ....");
stbuilder.append("......,");
stbuilder.append(" .......");
stbuilder.append("......");
stbuilder.append(".......");
stbuilder.append(" ......");
stbuilder.append(" ........");
stbuilder.append(" ....)");
stbuilder.append(" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,");
stbuilder.append(" ?, ?, ?, ? , ?, ?, ?, ?, ? , ?, ?, ? , ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? , ?, ?, ?, ?, ?, ?, ?, ?, ?,");
stbuilder.append(" ?, ?, ?, ?,?,");
stbuilder.append(" ?)");
statementfront = connection.prepareStatement(stbuilder.toString());
--http://www.freelists.org/webpage/oracle-l Received on Sun Sep 11 2022 - 07:41:45 CEST