sequence is not in a row on oracle tables-CodePudding

I have a project that its structure is java ee 7. I use hibernate as ORM and my database is Oracle.

I use @SequenceGenerator with allocationSize = 1 for id of my entity and @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq"). My database sequence in Oracle has cache=1000. But when I persist two records in database, first record's id is older than second record even if after a day and the id is not in a row and continuously.

How can I resolve this problem and what is my problem?

CodePudding user response：

As you are using 11g ( a very old version, so your company should think in upgrade asap ) the option for RAC has to pass for a balance between performance and integrity gap.

You have two options noorder vs order

create sequence xxx start with 1 increment by 1 noorder|order cache xxx

How do the instances co-ordinate their use of sequence values and avoid the risk of two instances using the same value?

There are two solutions: the default noorder mechanism where each instance behaves as if it doesn’t know about the other instances. The other is the order option, where the instances continuously negotiate through the use of global enqueues to determine which instance should be responsible for the sequence at any moment.

Noorder

The upshot of this noorder mechanism is that each instance will be working its way through a different range of numbers, and there will be no overlaps between instances. If you had sessions that logged on to the database once per second to issue a call to nextval (and they ended up connecting through a different instance each time), then the values returned would appear to be fairly randomly scattered over a range dictated by “number of instances x cache size.” Uniqueness would be guaranteed, but ordering would not.

Order

If you declare a sequence with the order option, Oracle adopts a strategy of using a single “cache” for the values and introduces a mechanism for making sure that only one instance at a time can access and modify that cache. Oracle does this by taking advantage of its Global Enqueue services. Whenever a session issues a call to nextval, the instance acquires an exclusive SV lock (global enqueue) on the sequence cache, effectively saying, “who’s got the most up to date information about this sequence – I want control”. The one instance holding the SV lock in exclusive mode is then the only instance that can increment the cached value and, if necessary, update the seq$ table by incrementing the highwater. This means that the sequence numbers will, once again, be generated in order. But this option has a penalty in performance and should be considered carefully.

Summary

If your transactions are fast, you can use order and test how it behaves. If your transactions are not fast, I would avoid order all together. The best option is to upgrade to 19c ( 12c is already near of obsolescence ) and use IDENTTTY COLUMNS

CodePudding user response：

If you have unordered (separate) caches on each node (the default):

node 1: cache values (1 - 1000)
node 2: cache values (1001 - 2000)

then the caches cannot overlap values and value used will depend on which node performs the insert. That is why your sequence values currently appear to be out of order.

Using the NOCACHE and/or ORDERED options will result sequential numbers, but you can expect at least some performance impact to your application, as the database must perform more overhead to determine the current sequence value before making it available to your SQL command. Reducing the cache size or eliminating the cache entirely can have a severe negative impact on performance if you are executing a lot of inserts (as suggested by your current cache value of 1000).

Assuming for now that you continue to use a cache (whether ordered or not), be aware that every time you restart your database, or a node (depending on your exact configuration), the unused cached values will be flushed and lost and a new cache will be created.

In the end, it is important to realize that sequence values are not intended (for most applications) to be perfectly sequential without gaps, or even (as in your case) ordered. They are only intended to be unique. Be sure to understand your requirement, and don't be put off if sequences don't behave quite like you expected: must the values be sequential in the order inserted, and will gaps in the sequence affect your application? If the answer is no, the application won't care, then stick with what you've got for the sake of performance.