签到成功

知道了

CNDBA社区CNDBA社区

Oracle 19c 自适应序列(Scalable Sequence) 说明

2021-01-25 21:36 189 0 原创 Oracle 19c
作者: Dave

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.https://www.cndba.cn/dave/article/4368https://www.cndba.cn/dave/article/4368

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.https://www.cndba.cn/dave/article/4368

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

其中:

https://www.cndba.cn/dave/article/4368

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

https://www.cndba.cn/dave/article/4368

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.https://www.cndba.cn/dave/article/4368

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.

https://www.cndba.cn/dave/article/4368

简单的说: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位数。https://www.cndba.cn/dave/article/4368

创建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 的自适应序列,并且不建议排序。


关于自适应序列的更多信息参考官方手册:https://www.cndba.cn/dave/article/4368

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-03C49DED71ADhttps://www.cndba.cn/dave/article/4368

版权声明:本文为博主原创文章,未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
Dave

Dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 1832
    原创
  • 2
    翻译
  • 417
    转载
  • 119
    评论
  • 访问:3164735次
  • 积分:2888
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by ZhiXinTech · 皖ICP备19020168号·

        QQ交流群