From Oracle 18c, Sequences can be made scalable by using SCALE clause in CREATE/ ALTER SEQUENCE statement. Scalable sequences optimize the sequence generation by using a unique combination of instance number and session number to reduce the impact of Index leaf block contention during massive loads. This is one of the few features that is not automatically enabled as it requires some intervention by DBA’s to ensure this does not change their implemented business logic.
Here is the syntax for defining a scalable sequence.
Create /Alter sequence sequence_name …… scale [extend | noextend] | noscale
You can check DBA_SEQUENCES/USER_SEQUENCE/ALL_SEQUENCE dictionaries to know whether sequence is scalable or not. By default sequences are not scalable as you can see in dictionary both columns scale_flag/extend_flag is set to N.
SQL> create sequence test_seq;
SQL> select sequence_name, scale_flag, extend_flag from user_sequences;
SEQUENCE_NAME S E
------------- -- --
TEST_SEQ N N
Now create the sequence with scale clause. When the scale clause is specified, a 6 digit numeric scalable number is prefixed to the digit of the sequence. Out of 6 digits, three digits are instance numbers followed by a three digit session number. These are generated by: Instance number is [(instance id % 100) + 100] Session number is [session id % 1000]