3

I use Hibernate on my Java project, I have Oracle DB. An ID column, I identified a sequence with increment 1. But this is how JPA/Hibernate gets nextVal from the sequence:

1   1451
2   1450
3   1402
4   1401
5   1400
6   1352
7   1351
8   1350
9    426

You can see that nextVal sometimes get 1, but mostly 50 or more. I see even the start of a sequence 200 even though I put the start value 1. Why is this happening? Is this normal?

Can I reduce this "50" increment somehow?

Edit: Duplicate of hibernate oracle sequence produces large gap

Community
  • 1
  • 1
Yasin Okumuş
  • 2,299
  • 7
  • 31
  • 62

2 Answers2

8

I found this solution;

  1. I made sequence cache as "no-cache"
  2. I put "allocationSize=1" to annotation @SequenceGenerator of @Id property as it was described here: https://stackoverflow.com/a/5346701/169534

Problem resolved

Community
  • 1
  • 1
Yasin Okumuş
  • 2,299
  • 7
  • 31
  • 62
  • I did the same, increment got reduced to 2 from 50.But still its not incrementing by 1. Any idea what can be the issue? – Sahil Chhabra Apr 20 '18 at 02:40
0

If your sequence has a cache size of 50 you might see this issue if the database is getting shutdown in between your fetches from the sequence. The remaining cached values will never be used when the database gets shutdown.

GriffeyDog
  • 8,186
  • 3
  • 22
  • 34
  • No, there is no shutdown on DB so frequently even though "shutdown"s happen only on some critical high load values. – Yasin Okumuş Feb 01 '12 at 15:05
  • You're right about the cache, but doesn't need to be a database shutdown. Basically Oracle will cache 50 nextvals, and if not used can age out of cache. The next time nextval is called, will cache another 50 values (tossing out the previous ones in cache). – tbone Feb 01 '12 at 17:52
  • 1
    If I create/change cache value as "no-cache" on sequence, would it be a solution for it? And would it be a problem somehow? – Yasin Okumuş Feb 02 '12 at 07:03