This content originally appeared on DEV Community and was authored by Vahid Yousefzadeh
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
This content originally appeared on DEV Community and was authored by Vahid Yousefzadeh
Vahid Yousefzadeh | Sciencx (2025-10-01T21:28:26+00:00) Sequence Dynamic Cache Resizing in Oracle 21c and 19.10. Retrieved from https://www.scien.cx/2025/10/01/sequence-dynamic-cache-resizing-in-oracle-21c-and-19-10/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.