CNDBA社区
1、 自适应序列概述
自适应序列（Scalable Sequence）是Oracle 18c 中引入的新特性，在将大规模数据加载到具有序列生成主键的单实例或RAC集群的表中时，通过自适应序列可以减少序列和索引块的争用问题，从而提供更好的吞吐量。
官网对自适应序列（Scalable Sequence）的介绍如下：
The ability to create Scalable Sequences has been added to improve the performance of data loading into tables having sequence values as keys. This feature provides the option to add instance and session offsets to significantly reduce the possibility of sequence and index block contention when loading data across RAC instances and multiple loading processes within single instances.
The new business benefit is that it further enhances the Oracle Database data loading capabilities by reducing contention when loading data into tables that use sequence values as keys. By adding the ability to create sequences with instance and session ids added to the sequence value, contention on sequence value generation and index blocks inserts for the key values is significantly reduced. This means that Oracle Database is even more scalable for data loading and can support even higher data loading rates.
2、 18c 之前的解决方法
高并发系统中的序列争用一直都是存在的，在Oracle 18c 之前，只能通过自定义seq的方式(instance+sid+seq)来解决, Oracle 18c 之后可以直接使用自适应序列（Scalable Sequence）。
18c 之前采用如下类似算法来实现：
[dave@www.cndba.cn ~]$ sql sys/dave@www.cndba.cn as sysdba
SQLcl: Release 19.1 Production on Mon Jan 25 20:28:56 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL> show con_name
CON_NAME
------------------------------
DAVE
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 DAVE READ WRITE NO
SQL> create sequence normal_seq increment by 1 maxvalue 10000 cache 20;
Sequence created.
SQL> select (instance_number + 100)||(select lpad(mod(sid,1000),3,0) from v$mystat where rownum=1)||normal_seq.nextval as id from v$instance;
ID
--------------------------------------------------------------------------------
1013771
SQL>
--其他会话查询：
SQL> select (instance_number + 100)||(select lpad(mod(sid,1000),3,0) from v$mystat where rownum=1)||normal_seq.nextval as id from v$instance;
ID
--------------------------------------------------------------------------------
1013723
这种方式我们将三种变量融合到一起，虽然序列还是连续的，但是最终产生的数字就不在连续，减少大量数据集中在一个block中，避免引起争用。 也正因为这种处理后的序列是无序的，所以Oracle 也不建议最自适应序列进行排序。
3 自适应序列操作示例
自适应序列创建语法如下：
CREATE | ALTER SEQUENCE [ schema. ]sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
| { SCALE {EXTEND | NOEXTEND} | NOSCALE}
]
这里与普通序列有区别是如下几个选项：
1）SCALE/NOSCALE
当指定SCALE 选项后，创建的就是自适应序列，即在普通序列前面会添加一个6位的数字前缀。
自适应序列的格式是： iii||sss|| normal_seq其中：
iii = (instance_id % 100) + 100 (三位数字)
sss = (session_id % 1000) (三位数字)
2）EXTEND/NOEXTEND
官方的说明如下：
When EXTEND is specified with the SCALE keyword, the generated sequence values are all of length (x+y), where x is the length of the scalable offset (default 6), and y is the maximum number of digits in the sequence maxvalue/minvalue. Thus, for an ascending sequence with maxvalue 100 and SCALABLE EXTEND specified, the generated sequence values are of the form iii||sss||001, iii||sss||002, …,iii||sss||100
The default setting for the SCALE clause is NOEXTEND. With the NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence maxvalue/minvalue. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns. On invocation of NEXTVAL on a sequence with SCALABLE NOEXTEND specified, a user error is thrown if the generated value requires more digits of representation than the sequence’s maxvalue/minvalue.
The addition of 100 in generating iii ensures that all generated values are of the same length, and consequently, there will be no duplicates in the generated values across all instances.
The default length of the scalable sequence offset is 6.
简单的说：EXTEND / NOEXTEND 是用来控制序列的位数。
1. EXTEND: 序列总长度=[X +Y]，X默认值是6位数，Y是maxvalue/minvalue指定的位数。
2. NOEXTEND: 默认值，序列总长度不能超过maxvalue/minvalue的位数，此时maxvalue/minvalue的位数不能小于7，因为前缀偏移量就占了6位数字。
具体操作示例如下：
创建scale extend 自适应序列：
--会话1：
[dave@www.cndba.cn admin]$ sql dave/dave@dave
SQLcl: Release 19.1 Production on Mon Jan 25 21:24:14 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon Jan 25 2021 21:24:14 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL>
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
1
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
372
SQL> create sequence seq_extend start with 1 increment by 1 minvalue 1 maxvalue 100 scale extend;
Sequence created.
SQL> select seq_extend.nextval from dual;
NEXTVAL
----------
101372001
SQL>
--会话2：
SQL> select seq_extend.nextval from dual;
NEXTVAL
----------
101377002
因为我们定义的最大值是100，是3位数，所以自适应序列就是 6+ 3 共9位数。
创建noextend 自适应序列：
[dave@www.cndba.cn admin]$ sql dave/dave@dave
SQLcl: Release 19.1 Production on Mon Jan 25 21:24:14 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon Jan 25 2021 21:24:14 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL>
SQL> create sequence seq_noextend start with 1 increment by 1 minvalue 1 maxvalue 100 scale noextend;
Sequence created.
SQL> select seq_noextend.nextval from dual;
select seq_noextend.nextval from dual
*
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for SEQ_NOEXTEND. Widen the sequence
by 4 digits or alter sequence with SCALE EXTEND.
SQL>
序列虽然创建成功，但是取值的时候报错了，提示我们将序列的宽度增加4位，或者将序列改成SCALE EXTEND。 因为我们的100是三位数，自适应序列最小都是7位。
修改maxvalues 长度：
SQL> alter sequence seq_noextend maxvalue 100000000;
Sequence altered.
SQL> select seq_noextend.nextval from dual;
NEXTVAL
----------
101377001
修改成extend：
SQL> alter sequence seq_noextend scale extend;
Sequence altered.
序列的信息可以通过 dba_sequences 视图查询，相关字段如下：
[dave@www.cndba.cn admin]$ sql dave/dave@dave
SQLcl: Release 19.1 Production on Mon Jan 25 21:24:14 2021
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Last Successful login time: Mon Jan 25 2021 21:24:14 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
SQL>
SQL> desc dba_sequences
SEQUENCE_OWNER NOT NULL VARCHAR2(128)
SEQUENCE_NAME NOT NULL VARCHAR2(128)
MIN_VALUE NUMBER
MAX_VALUE NUMBER
INCREMENT_BY NOT NULL NUMBER
CYCLE_FLAG VARCHAR2(1)
ORDER_FLAG VARCHAR2(1)
CACHE_SIZE NOT NULL NUMBER
LAST_NUMBER NOT NULL NUMBER
SCALE_FLAG VARCHAR2(1)
EXTEND_FLAG VARCHAR2(1)
SHARDED_FLAG VARCHAR2(1)
SESSION_FLAG VARCHAR2(1)
KEEP_VALUE VARCHAR2(1)
SQL>
SQL> set lines 120
SQL> col sequence_name for a40
SQL> select sequence_name,max_value,scale_flag,extend_flag from dba_sequences where sequence_name='SEQ_NOEXTEND';
SEQUENCE_NAME MAX_VALUE S E
---------------------------------------- ---------- - -
SEQ_NOEXTEND 100000000 Y Y
SQL>
4 小结
对上文的总结就是对于18c 以后的Oracle 数据库，为了解决序列的争用的问题，建议使用scale extend 的自适应序列，并且不建议排序。
关于自适应序列的更多信息参考官方手册：
Making a Sequence Scalable
https://docs.oracle.com/en/database/oracle/oracle-database/21/admin/managing-views-sequences-and-synonyms.html#GUID-76663C84-D792-46A3-A25A-03C49DED71AD
版权声明：本文为博主原创文章，未经博主允许不得转载。
