CODEWITHSUNDEEP
javaoracle-databasehibernatespring-bootjpa
Arun
Arun

Reputation: 3841

Oracle SEQUENCE - 'increment by' and 'cache'

I use JPA along with hibernate for my Spring boot application. I am facing some insert performance issues while doing in bulk. So far the fixes I found are :

  1. Alter the Oracle Sequence update the 'Increment by > 1, I am giving 50'
  2. In the Java Entity use the allocationSize to same value as Oracle Increment By

So that JPA prevents a call from getting the next sequence. My sequence is defined as: CREATE SEQUENCE MYSCM.BOOKING_SCHED_SEQ INCREMENT BY 1 MAXVALUE 9999999999999999999999999999 MINVALUE 1 CACHE 20

When I increase the INCREMENT BY to 50 should the cache be increased to 50 or reduce?

Upvotes: 3

Views: 5856

Answers (1)

APC
APC

Reputation: 146219

When I increase the INCREMENT BY to 50 should the cache be increased to 50 or reduce?

Neither. There is no relationship between INCREMENT BY and CACHE.

The INCREMENT BY controls the monotonic aspect of the sequence. With INCREMENT BY 50 the series goes 1, 51, 101, 151 and so on.

The CACHE controls how many sequence numbers are held in memory to service NEXTVAL requests. The smaller the CACHE number the more often the database has to read from its internal tables to grab the next allocation range. So in a moderately busy system we would want to minimize the number of acquired latches, so we set CACHE to a high-ish number, say 1000.

People obsess over setting the CACHE value, because they think if it's too high they might "lose" some values and have gaps in their series. This is extremely unlikely to happen, and even if it does we shouldn't care. Sequences are a source of guaranteed unique values and have no further meaning.

Although, having re-read your question I don't think this will have any impact on the performance of your bulk inserts. Why did you choose to focus on sequence allocation? Have you run any trace to discover where the bottleneck is? Have you spoke to your DBA?

Upvotes: 7

Related Questions