Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » 性能优化

关于Oracle的Sequence,你需要知道的

如果表上的某列使用序列来填充,并且这一列还建立索引,那么在DML操作中,经常碰到在索引方面的性能问题。尤其是RAC环境中,这种情况,甚至还会加剧。下面让我们看看序列选项的组合,以及18c之后出现的自适应序列新特性,通过这个特性能够解决很多棘手的问题。

Oracle序列的常用组合

下面是序列常用选项组合,分为下面四种情况,对数据库性能的影响依次递增

1. CACHE + NOORDER
这个选项对RAC的性能影响最小。如果创建索引时没有指定选项,这个是默认的选择。每个实例在shared pool中会缓存一组不同的数值。序列不会做全局排序,当共享池刷新时(例如实例关闭),会出现更大的间隔。RAC上创建sequence时,如果指定了cache大小而没有使用noorder属性,则各节点将会把不同范围的sequence值cache到shared pool中。比如第一个节点使用的是101-200,第二个节点使用的是201-300。
2. CACHE + ORDER

每个实例缓存同一组数字,比如第一个节点使用101-200,第二个节点同样使用的是101-200。这种方式,序列的顺序是有保证的,可能会出现更大的间隔。性能优于使用NOCACHE序列。当序列缓存丢失时(例如刷新shared pool或关闭实例),序列编号中会出现间隙。

3. NOCACHE + NOORDER

当政府法规或法律要求序列号的间距有限时,使用此设置。该选项不能保证序列是排序的。它比NOCACHE/ORDER有更好的性能。

4. NOCACHE + ORDER
使用这个设置,不会出现间隙(除了下面说的这两种情况)。不过,此设置对RAC的性能影响最大。

序列出现间隙的原因

对于任何类型的序列设置,即使在非RAC数据库上,序列间隙(也就是我们说的跳号)始终是可能的,比如下面的情况:

a. 当发生任何类型的故障(如ora-600)或事务回滚时
  1. 用户A通过nextval获得了序列111
  2. 用户B通过nextval获得了序列
  3. 用户B提交了
  4. 用户A会话失败了,或者该事务被回滚了
  5. 用户C通过nextval获得了序列113. 那么110到112之间就会有一个差距111。
b. 当序列使用CACHE选项时,如果缓存并且缓存的值从共享池中刷新时。RAC和单实例数据库中的情况相同。任何共享池上的任何刷新都足以使RAC系统上的缓存的序列值失效。
如果应用程序不允许序列跳号,则不能使用Oracle数据库序列,可以考虑将序列号存储在数据库表中。使用数据库表实现序列生成器时要小心。即使在单个实例配置中,如果序列值生成过快,性能开销也与锁定存储序列值的行的成本相关。
序列与Oracle的锁
每次sequence更新,都要获得row cache lock,如果使用了Cache选项,能减少row cache lock,如果出现宕机或者刷新Shared Pool,序列会有间隙。

如果使用了NOCACHE和ORDER,执行seq.nextval也会触发row cache lock

如果使用了NOCACHE,每执行一次seq.nextval,都要更新数据字典的CACHE,row cache lock可能会成为主要的等待事件(NOCACHE: --> row cache lock)

如果使用了CACHE + ORDER,并且使用在RAC的环境中(cluster_database = true),那么在Shard Pool中插入或更新序列之前,都要获得一个实例级别的排它锁SV(enq: SV –  contention)锁。如果有多个会话同时获得nextval的同一个序列值,那么一些会话会出现'DFS lock handle'的等待事件,这个事件的PARAMETER2对应的id1就是序列的数值。(CACHE + ORDER(RAC): -->enq: SV -  contention,DFS lock handle )

相对来说,使用CACHE + NOORDER是最佳选择。在这种情况下会从Shared Pool中直接获取,如果CACHE值设置的不合理,那么可能会出现enq: SQ - contention成为主要的等待事件。

由于序列的机制等因素,在RAC环境中,序列的吞吐量不会随着RAC节点的增加,相反有可能会减少。在RAC环境中,Sequence的Cache属性对性能的影响很大。如果序列更新的不频繁,可以考虑使用默认的CACHE 20,如果更新频繁,建议增大CACHE大小,可以调整为1000甚至更多,可根据实际情况调整。

***************
18c后的序列
从Oracle 18.1数据库开始,自适应序列 (Scalable Sequences) 被引入。
为了改善以序列值作为键值的表的数据加载性能,自适应序列的特性被加入。这个特性为序列提供了添加instance和session 偏移量的选项,当跨RAC节 点加载数据或者单实例多个进程并发加载数据时,可以显著减少序列争用和索引块争用的可能性。
这个新特性的好处是,在以序列作为键值的表的数据加载时,它通过减少争用来进一步提升Oracle数据库加载数据的能力。在创建序列的时候,将instance和session的id 添加到序列的值中,这样在生成序列值时产生的争用和 insert 键值时产生的索引块争用可以显著的减少。这表明Oracle数据库数据的数据加载能力可以进一步扩展,并且可以支撑更高速率的数据加载。
SQL 语法:
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}
]

SCALE/NOSCALE
当SCALE 被指定的时候,一个数字偏移量会附着在序列值的开头。
偏移量的表现形式为 iii||sss|| ,其中,
iii 表现为一个3个数字的instance偏移量,数值来源为: (instance_id % 100) + 100,
sss 表现为一个3个数字的instance偏移量,数值来源为:(session_id % 1000)
||是连接符

EXTEND/NOEXTEND
当EXTEND和SCALE关键字同时被指定时,产生的序列值的长度都是 (x+y),这里 x 是自适应偏移量 (默认是6),而 y 则是序列的 maxvalue/minvalue关键字限定的最大值的数字位数。举例来说,如果一个升序的序列,maxvalue 被指定为100,并且指定了SCALABLE EXTEND关键字,那么产生的序列值的表现形式为 iii||sss||001,iii||sss||002 ...... iii||sss||100
SQL> select instance_number from v$instance;
INSTANCE_NUMBER
---------------
              2
SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------------------------------
101
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
----------
 102101001
SQL> select seq_extend.nextval from dual;
   NEXTVAL
----------
 102101002
默认情况下SCALE是NOEXTEND的,当NOEXTEND被设置时,产生的序列值长度最大只能是序列maxvalue/minvalue。关键字限定的最大值的数字位数。在某些应用中,序列被用来填充固定宽度的列,那么在整合这些应用时,这个设定很有用。在调用一个SCALABLE NOEXTEND的序列的NEXTVAL值时,如果产生的序列值需要的数字位数比 maxvalue/minvalue限定的最大值位数更大,那么一个用户错误会被抛出
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.
因为使用了noextend,maxvalue是100,因此序列的长度不能超过是3。使用了scale选项,序列的长度已经超过了三位,因此在获取序列时报错。下面让我们修改序列的最大值
SQL> alter sequence seq_noextend maxvalue 100100001;
Sequence altered.

再次获取序列时就没有问题了
SQL> select seq_noextend.nextval from dual;

   NEXTVAL
----------
 102101001
SQL> select seq_noextend.nextval from dual;
   NEXTVAL
----------
 102101002
SQL>


小结

18c之后建议使用自适应序列,使用SCALE EXTEND CACHE ORDER选项。适当调整CACHE值的大小对性能有提升。在18c之前,可以通过inverse index或者对索引使用的表空间进行分区(最省事的是hash分区)来提升性能。现在有了这个特性,就可以构建不连续的序列,减少索引之上的竞争,这也是Oracle数据库优化的最佳实践。


Reference
https://docs.oracle.com/en/database/oracle/oracle-database/18/newft/new-features.html#GUID-CB2428B8-A5BA-4B13-B437-ECB5F0C2C84E
18c Scalable Sequences (Doc ID 2341262.1)

RAC and Sequences (Doc ID 853652.1)



关键词:enqueue event sequence 18c oracle 

相关文章

基于PDB的Active Data Guard(Oracle 21.7+)
在Oracle数据库中使用REST
OGG from MySQL to Oracle
Oracle数据库容灾之两地三中心实践
低代码开发用Oracle Apex,看这篇就够了
Oracle Database 20c之SQL宏
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Oracle Database 19c在优化方面有哪些新特性
Top