How to speed up operations with normal oracle operations?

Question

I am developing a tool for continuously exporting changes from a MongoDb database to Oracle.

I have a problem with a batch execution operation (Oracle).

static void save(List result) {
    withBatchConnection { Statement stm ->
        result.each { String line ->
                stm.addBatch(line)
        }
    }
}

static withConnection(Closure closure) {
    def conn = null
    boolean success = false
    while (!success) {
        try {
            conn = getConnection()
            closure.call(conn)
            success = true
        } catch (e) {
            log.error('Connection problem', e)
            log.error(e, e)
            log.info('Retrying for 30 sec')
            sleep(30000)
        } finally {
            conn?.close()
        }
    }
}

static withTransactionConnection(Closure closure) {
    withConnection { Sql sql ->
        OracleConnection conn = sql.getConnection() as OracleConnection
        conn.setAutoCommit(false)
        closure.call(conn)
        conn.commit()
    }
}

static withBatchConnection(Closure closure) {
    withTransactionConnection { Connection conn ->
        def statement = conn.createStatement()
        closure.call(statement)
        statement.executeBatch()
        statement.close()
    }
}

The problem is that I cannot use a prepared statement because the order of operations is very important.

When I save to MySql with a Rewrite Batched Statement, it is like 10k operations per second. For Oracle - 400 operations / s

Is there a chance to make it faster?

I am using OJDBC 7 and groovy 2.4.7

Answer 1

Set the size of the array on the client side maximum and try