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 

相关文章

Oracle Database 20c之SQL宏
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Oracle Database 19c在优化方面有哪些新特性
Oracle Database 20c云端预览版发布了
Oracle 19c新特性之RAC Automatic Failback Service
Install Oracle RAC Database 19c Step by Step
保障业务连续性的神器
Oracle DataGuard feature and workshop
Top