Oracle database 19c Dynamic sequence cache - LAST_VALUE for sequences is incorrectly calculated

Recently after  migrating to  19c  , Application team started complaining that sequences  value are not properly calculated . 

It was later found that  it was due to  Dynamic sequence cache  introduced in 19c . 

This issue is supposed to be fixed in 19.13 

If the nextval value of the sequence is frequently called in the application, there may be performance 

problems.  

The default sequence cache is usually 20, I still recommend configuring cache 200 to start when creating.

The feature is enabled by default and requires no additional setup by a DBA or end user. This feature resizes sequence caches dynamically based on the rate of consumption of sequence numbers. As a result sequence cache sizes can grow well beyond the configured DDL cache size.

If a sequence is used across nodes in a RAC setting, this can result in large gaps in sequence numbers seen across instances.

If you see large gaps in sequence numbers on 19.10 or later, and your application is sensitive to such gaps, then you can disable this feature with the following setting:

_dynamic_sequence_cache = FALSE;

References :

19c LAST_VALUE for sequences is incorrectly calculated when next cache of numbers required (Doc ID 2797098.1)

Sequence dynamic cache resizing feature (Doc ID 2790985.1)