Oracle 18c Scalable Sequences

Index block contention is very common databases with high insert activity and it’s especially common on tables that have monotonically increasing key values typically generated via a sequence.

Oracle B-tree indexes are “right-handed” and the right-hand leaf nodes of the B-tree contain the highest key which is located in the lowest tree level.

Index leaf node contention happens when rows are inserted based on a user generated key like via a sequence and in case of columns populated by sequence based key values, recent entries will be in the right most leaf block of the B-Tree.

This means all new rows will be stored in the right most leaf block of the index. As more and more sessions insert rows in to the table, that right most leaf block will be full.

Oracle will split that right most leaf block into two leaf blocks with one block containing all the rows except for one row and a new block with just a single row.

This type of index growth termed as “Right Handed Growth” Indexes. As more and more concurrent sessions inserts into the right most leaf block of the index, that index block becomes hot block, and concurrency on that leaf block leads to performance issues.

In an Oracle RAC database this problem is magnified and becomes a bigger bottleneck. If the Sequence cache (which is instance specific) is small (defaults to 20), then the right most leaf block becomes a hot block in not only one instance but in all the instances part of the cluster and the hot block will need to be transferred back and forth over the interconnect.

Oracle Database 18c introduces a new type of sequence called a Scalable Sequence.

Now in Oracle 18c, in those cases of data ingestion workloads with high level of concurrency, the new scalable sequence by generating unordered primary or unique key values helps in significantly reducing the sequence and index block contention caused by the right-handed indexes and thus provides better throughput, data load scalability and performance as compared to the pre -Oracle 19c solution of having to configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE or ALTER SEQUENCE statement.

The scalable sequence is made up of a sequence offset number which by default contains 6 digits

The first 3 digits is derived from the instance number with 100 added and the next 3 digits is derived from the SID of that session.

So lets say user with SID 555 and instance number is 1 – then the offset number is 101555 and for another session with SID 666 in instance 2, the offset number would be 102666.

We can create the scalable sequence with either the EXTEND of NOEXTEND option.

When the EXTEND option is specified for the SCALE clause, the scalable sequence values are of the length [X digits + Y digits], where X is the sequence offset number and Y is the number of digits specified in the MAXVALUE clause.

Let us see how that works when we create a scalable sequence with the EXTEND option.

Note the different (unrelated and unordered) values which are generated by the sequence in each instance.

Instance 1

SQL> create sequence system.scale_ext_seq 
  2  start with 1 increment by 1
  3  maxvalue 100
  4  scale extend;

Sequence created.

SQL> select system.scale_ext_seq.nextval from dual;

   NEXTVAL
----------
 101007001


SQL> select sid from v$mystat where rownum = 1;

       SID
----------
	 7

Instance 2

SQL> select system.scale_ext_seq.nextval from dual; 

   NEXTVAL
----------
 102036021

SQL> select sid from v$mystat where rownum = 1;

       SID
----------
	36

When the NOEXTEND option is specified for the SCALE clause the number of digits in the scalable sequence cannot exceed the number of digits specified in the MAXVALUE clause.

Note what happens when the number of digits in the sequence exceeds 7 (number of digits in MAXVALUE clause 1000000).

SQL>create sequence system.scale_noext_seq 
  start with 1 increment by 1
  maxvalue 1000000
  scale noextend;  

Sequence created.

INSTANCE 1

SQL> select system.scale_noext_seq.nextval from dual;

   NEXTVAL
----------
   1010071

SQL> /

   NEXTVAL
----------
   1010072

SQL> /

   NEXTVAL
----------
   1010073

...
...

   NEXTVAL
----------
   1010078

SQL> /

   NEXTVAL
----------
   1010079

SQL> /
select system.scale_noext_seq.nextval from dual
*
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for SCALE_NOEXT_SEQ. Widen the
sequence by 1 digits or alter sequence with SCALE EXTEND.


SQL> alter sequence system.scale_noext_seq maxvalue 10000000;

Sequence altered.

SQL> select system.scale_noext_seq.nextval from dual;

   NEXTVAL
----------
  10100741



INSTANCE 2


SQL> select system.scale_noext_seq.nextval from dual;

   NEXTVAL
----------
  10203661