Sequence Dynamic Cache Resizing in Oracle 21c and 19.10

Tuning the cache attribute for sequences correctly can have a significant impact on performance. However, in some cases, due to application restrictions, we are not allowed to set a cache size greater than zero. Apart from this exception, it is often observed that some people leave the cache size at its default value (20) after creating a sequence. This default setting is not suitable for all sequences, and for sequences to handle workloads more efficiently, the cache size should be adjusted based on the sequence usage rate.

Oracle has introduced a new feature that can be very helpful in this regard. The Sequence Dynamic Cache Resizing feature, introduced in Oracle 21c (and later backported to 19.10), automatically manages this issue (cache size).

According to this feature, the cache size can increase or decrease automatically based on the sequence usage rate. However, it will never drop below the manually configured cache value. By default, the Dynamic Cache Resizing feature is enabled.

In the following, we will explore this feature with a scenario.

Step 1: Create a sequence with cache size = 3

SQL> create sequence seq1 cache 3;

Sequence created

At the first call, the sequence returns the value 1. We store this value in a table:

SQL> create table tbtest as select seq1.nextval from dual;

Table created

SQL> select * from tbtest;

NEXTVAL
-------
      1

Since the cache size is set to 3, numbers 1 to 3 are held in memory. The next value available on disk is 4, known as last_number:

SQL> select s.sequence_name, s.cache_size, s.last_number 
     from user_sequences s;

SEQUENCE_N  CACHE_SIZE  LAST_NUMBER
----------  ----------  -----------
SEQ1                 3            4

Note: last_number is equivalent to the highwater column in the $seq table.

If we flush the shared pool or crash the instance, the next sequence value will return the last_number (4). Cached numbers in memory are lost:

SQL> alter system flush shared_pool;

System altered

SQL> insert into tbtest select seq1.nextval from dual;

1 row inserted

SQL> select * from tbtest;

NEXTVAL
-------
      1
      4

As we see, flushing the shared pool caused values 2 and 3 to be lost.

Step 2: High sequence usage
So far, the sequence usage rate has been low, and Oracle has not used the Sequence Dynamic Cache Resizing feature. To observe its behavior, we increase the sequence calls by inserting 1 million rows:

begin
  for i in 1..1000000 loop
    insert into tbtest select seq1.nextval from dual;
  end loop;
end;
/

After inserting 1 million rows, the current value of the sequence and the maximum inserted value in the table is 1000004:

SQL> select seq1.currval from dual;

CURRVAL
-------
1000004

SQL> select max(nextval) from tbtest;

MAX(NEXTVAL)
------------
1000004

Step 3: Flushing the shared pool again

SQL> alter system flush shared_pool;

System altered

SQL> insert into tbtest select seq1.nextval from dual;

1 row inserted

In previous releases (before 21c), we would expect nextval to increase by two, from 1000004 → 1000006. But the result is different:

SQL> select max(nextval) from tbtest;

MAX(NEXTVAL)
------------
1009772

SQL> select seq1.currval from dual;

CURRVAL
-------
1009772

This difference is due to the Sequence Dynamic Cache Resizing feature being active. Since we stored the nextval values in tbtest, the gap is visible with this query:

SQL> select * from tbtest order by 1 desc fetch first 4 rows only;

NEXTVAL
-------
1009772
1000004
1000003
1000002

Step 4: Disabling the feature
If your application is sensitive to such gaps, you can disable this feature:

SQL> alter system set "_dynamic_sequence_cache" = FALSE;

System altered