Oracle sequence跳号知多少
Sequence是oracle中的一个非常常用的功能，开发经常会频繁使用。但是在生产环境中经常有应用反馈通过sequence生成的自增主键会出现不连续跳号的现象，而且是几十个几十个地跳，为了弄清楚sequence跳号的原因和机制，进行了一些研究和实验。
事务回滚引起的跳号
不管序列有没有CACHE、事务回滚这种情况下，都会引起序列的跳号。如下实验所示：
SQL> create sequence test_seq
start with 1
increment by 1
Maxvalue 9999999999
Cache 30
Order;
Sequence created.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
1
SYS@orcl>insert into test_tab select test_seq.nextval from dual;
1 row created.
SYS@orcl>rollback;
Rollback complete.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
3
并发访问序列引起的跳号
并发访问序列引起的跳号，其实不算真正的跳号，而只是逻辑跳号，只是序列值被其它并发会话使用了。我们来构造一起并发访问序列引起的跳号，我们开启两个会话窗口，循环获取序列的值，模拟并发出现的场景。
Session 1:
SYS@orcl>begin
for i in 1..50000 loop
Insert into ta select test_seq.nextval from dual;
end loop;
end;
/
PL/SQL procedure successfully completed.
Session 2:
SYS@orcl>begin
for i in 1..50000 loop
insert into tb select test_seq.nextval from dual;
end loop;
end;
/
PL/SQL procedure successfully completed.
SYS@orcl>select * from ta where rownum<10;
ID
----------
466322
466324
466326
466327
466329
466330
466332
466333
466335
9 rows selected.
发现序号并不连续，高并发情况下存在sequence争用。
FLUSH SHARED_POOL会导致CACHE的序列跳号
实验测试如下所示（序列的CACHE值必须大于0），当然正常情况下，很难遇到这种情况。
SYS@orcl>alter sequence test_seq cache 30;
Sequence altered.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560432
SYS@orcl>alter system flush shared_pool;
System altered.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560462
数据库实例异常关闭导致跳号
如下实验所示，当数据库使用shutdown abort命令关闭后，重新启动实例，序列缓存在shared pool里面没有用过的值都没有了。一下子从17045跳到17085
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560464
SYS@orcl>select object_id from dba_objects where object_name='TEST_SEQ';
OBJECT_ID
----------
20306
SYS@orcl>select increment$,minvalue,maxvalue,highwater,cache from seq$ where obj#=20306;
INCREMENT$ MINVALUE MAXVALUE HIGHWATER CACHE
---------- ---------- ---------- ---------- ----------
1 1 9999999999 560492 30
SYS@orcl>shutdown abort;
ORACLE instance shut down.
SYS@orcl>startup;
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560492
而在正常关闭数据库的情况下，sequence没有发生跳号
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560526
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 1946157720 bytes
Database Buffers 2415919104 bytes
Redo Buffers 11661312 bytes
Database mounted.
Database opened.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560527
设置test_seq为nocache，然后异常宕机
SYS@orcl>alter sequence test_seq nocache;
Sequence altered.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560528
SYS@orcl>shutdown abort;
ORACLE instance shut down.
SYS@orcl>startup;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 1946157720 bytes
Database Buffers 2415919104 bytes
Redo Buffers 11661312 bytes
Database mounted.
Database opened.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560529
查阅资料发现数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下，数据库正常关闭未出现nextval跳跃(currval也同样不跳跃)；而在数据库异常关闭之时，数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失，从而可能出现了sequence使用cache导致跳跃的情况
MOS（文档ID：470784.1）上提到了使用dbms_shared_pool.keep将对象在锁定在shared pool 中，永远不释放。这样可以防止FLUSH SHARED POOL导致序列跳号，但是这个无法避免数据库异常关闭或CRASH引起的跳号
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560530
SYS@orcl>exec dbms_shared_pool.keep('test_seq','q');
PL/SQL procedure successfully completed.
SYS@orcl>alter system flush shared_pool;
System altered.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560531
SYS@orcl>alter sequence test_seq cache 30;
Sequence altered.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560532
SYS@orcl>shutdown abort
ORACLE instance shut down.
SYS@orcl>startup;
ORACLE instance started.
Total System Global Area 4375998464 bytes
Fixed Size 2260328 bytes
Variable Size 1946157720 bytes
Database Buffers 2415919104 bytes
Redo Buffers 11661312 bytes
Database mounted.
Database opened.
SYS@orcl>select test_seq.nextval from dual;
NEXTVAL
----------
560562
其实如果业务允许，单号出现跳号也无所谓的情形最好，如果碰到业务要求绝对不能出现单号出现跳号的情况，那么就不能使用序列号了。
RAC环境中的sequence
如果是cache下的order，单实例下没有影响，而rac下多实例缓存相同的sequence，如果order的取大量sequence则会出现短暂的资源竞争（由于资源需要在多实例间传递），性能要比noorder差很多。尤其注意nocache order的sequence，即对于sequence大量争用，还需要在实例间传递竞争资源，严重的甚至导致系统直接hang住，对于rac的环境需要通过cache fusion和序列的机制认真分析来找到性能瓶颈的根本原因。
oracle为了在rac环境下为了sequence的一致性，使用了三种锁：row cache lock、SQ锁、SV锁。 row cache lock的目的是在sequence指定nocache的情况下调用sequence.nextval过程中保证序列的顺序性； SQ锁是应用于指定了cache+noorder的情况下调用sequence.nextval过程中。 SV 锁（dfs lock handel) 是调用sequence.nextval期间拥有的锁。前提是创建sequence时指定了cache 和order属性 (cache+order)。order参数的目的是为了在RAC上节点之间生成sequence的顺序得到保障。
创建sequence赋予的cache值较小时，有enq:sq-contention等待增加的趋势。 cache的缺省值是20.因此创建并发访问多的sequence时，cacheh值应取大一些。否则会发生enq:sq-contention等待事件。
rac上创建sequence时，如果指定了cache大小而赋予noorder属性，则各节点将会把不同范围的sequence值cache到内 存上。若两个节点之间都必须通过依次递增方式使用sequence，必须赋予如下的order属性（一般不需要这样做）”sql> create sequence seq_b cache 100 order”。如果是已赋予了cache+order属性的sequence，oracle使用SV锁进行同步。SV锁争用问题发生时的解决方法与sq锁 的情况相同，就是将cache 值进行适当调整。
在RAC多节点环境下，Sequence的Cache属性对性能的影响很大。应该尽量赋予cache+noorder属性，并要给予足够的 cache值。如果需要保障顺序，必须赋予cache+order属性。但这时为了保障顺序，实例之间需要不断的交换数据。因此性能稍差。
腾讯云开发者
