I am working on an application with high number for DML operations due to which log file sync wait event is observed. We are using ebean framework for querying the Oracle database. I was looking for a way to reduce the number of commits. Is it advisable to use JDBC batch using batch size attribute for transactional calls.
Is it advisable to use JDBC batch using batch size attribute for transactional calls.
Assuming a transaction is inserting, updating or deleting more that 1 bean/row then in short yes.
The caveat is that in terms of application code the actual execution of DML can occur later with statement flush at batch size, at commit time etc. This means statements can execute later in application code (like at commit time).
This typically only really matters to application code when application code is looking to handle exceptions like db constraint violations, missing foreign keys, unique constraints etc and actually continue the transaction. In this case we might need to add explicit transaction.flush() into the application code to ensure the statements have been executed and hit the database.