How do I optimize batch INSERTs for Oracle 11g and Hibernate?

Question

I have an application that does quite a bit of batch inserts. I'd like to optimize the application to do these as fast as possible.

I see several Hibernate Settings that I think have to do with batch inserts:

hibernate.jdbc.batch_size
hibernate.jdbc.batch_versioned_data
hibernate.jdbc.use_get_generated_keys

I'm not sure if I need to set these properties. If I do need to set them, I'm not sure what the correct values are.

Is it safe to set hibernate.jdbc.batch_versioned_data and hibernate.jdbc.use_get_generated_keys to true for my version of Oracle?

How do I choose a batch size?

I am using the following versions of these libraries:

Hibernate: 3.2.3 GA
Oracle Database: 11G
Oracle Database Driver: 11.2.0.3.0
c3p0: 0.9.1.2

Vlad Mihalcea · Accepted Answer · 2018-01-05 19:33:32Z

2

hibernate.jdbc.batch_size This should be set to a reasonable value that suits your requirements (recommended is between 5 and 30).

hibernate.jdbc.batch_versioned_data This is not safe for your database and JDBC driver version (see this question for more details). Do not turn this on. Otherwise, the optimistic locking mechanism will be silently broken for versioned entities.

hibernate.jdbc.use_get_generated_keys This is used by some Hibernate id generators that retrieve natively generated keys after insert (when auto-increment columns and similar are used for primary key generation). You have to enable it if you use such id generators regardless of whether batching is used or not.

Also, make sure to enable hibernate.order_inserts. More details on this flag and Hibernate batching in general can be found here.

edited Jan 5, 2018 at 19:33

Vlad Mihalcea

156k85 gold badges598 silver badges984 bronze badges

answered Dec 22, 2015 at 17:18

Dragan Bozanovic

23.6k5 gold badges50 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Adam Over a year ago

Thanks! I use Oracle sequences for most IDs, e.g. <generator class="native"> <param name="sequence">seq_mytable_id</param> </generator> Do those need hibernate.jdbc.use_get_generated_keys ?

Adam Over a year ago

Why do I need hibernate.order_inserts?

Dragan Bozanovic Over a year ago

"Do those need hibernate.jdbc.use_get_generated_keys?" No, they don't, because ids are fetched from the sequence prior to issuing insert statements._"Why do I need hibernate.order_inserts?"_ Regarding this and similar flags, please read the blog I linked to in the answer.

Collectives™ on Stack Overflow

How do I optimize batch INSERTs for Oracle 11g and Hibernate?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related