JDBC Batch insert into Oracle Not working

Question

I'm using JDBC's batch to inserting a million of rows. I was faced with that Oracle driver doesn't work as expected - batch insert takes a long time to work. I have decided to sniff application's traffic with Wireshark. And what did I see?

Oracle JDBC driver sent first request (1)
then it sending data (2), about 2500 rows
oracle server responds with some package (3)
now all remain data will be send with one-by-one inserts, not batching!
- insert into my_table...
- insert into my_table...

Why does this happen? How can I fix this?

Table

create table my_table (val number);

Code

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class scratch_1 {

    @Test
    public void foo() throws SQLException {
        String sql = "insert into my_table (val) values (?)";

        try (Connection con = getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps = con.prepareStatement(sql)) {

                for (long i = 0; i < 100_000; i++) {
                    ps.setBigDecimal(1, BigDecimal.valueOf(i));

                    ps.addBatch();
                }

                ps.executeBatch();
                ps.clearBatch();
            }
            con.commit();
        }
    }

    private Connection getConnection() throws SQLException {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "my_user";
        String password = "my_password";
        return java.sql.DriverManager.getConnection(url, user, password);
    }
}

Wireshark code to illustrate what is happened:

Environment

$ java -version
java version "1.8.0_181"
Java(TM) SE Runtime Environment (build 1.8.0_181-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

Oracle Database 12.2.0.1 JDBC Driver

Server: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit

Running query multiple times does not help - same result. 250k rows "batch" inserted in 465s

At the Server side v$sql:

SELECT *
FROM
  (SELECT REGEXP_SUBSTR (sql_text, 'insert into [^\(]*') sql_text,
    sql_id,
    TRUNC(
    CASE
      WHEN SUM (executions) > 0
      THEN SUM (rows_processed) / SUM (executions)
    END,2) rows_per_execution
  FROM v$sql
  WHERE parsing_schema_name = 'MY_SCHEMA'
  AND sql_text LIKE 'insert into%'
  GROUP BY sql_text,
    sql_id
  )
ORDER BY rows_per_execution ASC;

Maybe u should try to run your sample code with different number of executions (and compare them). Also you can investigate from the other side the view v$sql - columns executions, rows_processed. — ibre5041
Thank you for comment. I updated the description, did I answer your question? — referee
PS: do you have any triggers or constraints on target table? In case of OCI api this becomes non-trivial, because Oracle returns a vector of values and you have to check for every table whether the bulk operation succeed or not. — ibre5041

referee referee · Accepted Answer · 2018-07-31T13:24:02

Problem is solved

Thank you for all your responses. I'm very grateful to you!

My previous example doesn't describe real problem. Sorry that did not give the whole picture at once.
I simplified it to such a state that I lost processing of null values.
Check please example above I have updated it.
If I use java.sql.Types.NULL Oracle JDBC driver used theVarcharNullBinder for null values - it somehow leads to such a strange work. I think that Driver is used batch until first null with not specified type, after null it is fallback to one-by-one insert.

After change it to java.sql.Types.NUMERIC for number column driver used theVarnumNullBinder and correctly work with it - fully batching.

Code

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class scratch_1 {

    @Test
    public void foo() throws SQLException {
        String sql = "insert into my_table (val) values (?)";

        try (Connection con = getConnection()) {
            con.setAutoCommit(false);
            try (PreparedStatement ps = con.prepareStatement(sql)) {

                for (long i = 0; i < 100_000; i++) {
                    if (i % 2 == 0) {
                        //the real problem was here:
                        //ps.setNull(1, Types.NULL); //wrong way!
                        ps.setNull(1, Types.NUMERIC); //correct
                    } else {
                        ps.setBigDecimal(1, BigDecimal.valueOf(i));
                    }

                    ps.addBatch();
                }

                ps.executeBatch();
                ps.clearBatch();
            }
            con.commit();
        }
    }

    private Connection getConnection() throws SQLException {
        String url = "jdbc:oracle:thin:@localhost:1521:orcl";
        String user = "my_user";
        String password = "my_password";
        return java.sql.DriverManager.getConnection(url, user, password);
    }
}

JDBC Batch insert into Oracle Not working

2 Answers