What is the optimum number of sqls run in batch?

Question

I am trying to load the data in Oracle server remotely. I am doing "executeBatch()" for every 50 sqls that are added in batch. (thru JDBC)

What is the optimum number of sqls run in batch? Is it unlimited?

Answer 1

Quoted from oracle documentation

• If you are using parameterized SQL (you configure your Login by calling Login method bindAllParameters), the maximum batch writing size is the number of statements to batch (default: 100).

• If you are using dynamic SQL, the maximum batch writing size is the size of the SQL string buffer in characters (default: 32000).

There's no such thing as an "universal optimal value".

Each situation has its optimal value, which depends on a lot of things (including the probability a statement might fall, the average time taken per statement, the importance of the timing in your specific business situation, etc.)