sequence跳号问题以及相应知识拓展

SEQUENCE跳号可能是事务ROLLBAK或者实例被宕过或者是SEQUENCE的CACHE被交换出去过。第一个或者第二个解决方法比较难解决（可以从业务上来避免）。第三个对于有特殊要求的SEQUENCE或者包、存储过程、触发器等等，可以使用DBMS_SHARED_POOL.KEEP方法，把他们永久保留在SHARED POOL中，从而可以实现一些特定的用途。

用SEQUENCE值来生成的主键经常出现断号，而且断号不是一两个，而是每次几十个几十个的跳。而一般能想到的导致这种跳号的基本就是事务ROLLBAK了导致取出的SEQUENCE没有被插入到表中去，或者就是数据库关闭或者宕掉，导致已经CACHE的SEQUENCE的值丢失，导致的跳号。这两种情况都被一一排除了。那么到底什么原因导致的SEQUENCE跳号呢？
     一般为了提高从SEQUENCE取值的效率，我们都会对SEQUENCE设置CACHE，而且取值越频繁的，CACHE值设置的越大。那么这个CACHE的值是保存在SHAREDPOOL中的，而且这块内存是可以被其他程序共享的，也就是说SEQUENCE的CACHE是会在一定的情况下被刷出SHARED POOL的，那么就相当于已经CACHE的SEQUENCE的值就丢失了，这就是导致跳号的原因。

解决办法步骤（具体可以参考点击打开链接）

   ORACLE给我们提供了把SEQUENCEKEEP到SHARED POOL中的方法，这样就保证了SEQUENCE的CACHE不会被交换出去，从而避免了这种情况的发生。来看下面的示例：
SQL> select seq_bear.nextval from dual;
NEXTVAL
----------
102
再次查询的时候，值为102
SQL> exec dbms_shared_pool.keep(name => 'SEQ_BEAR',flag => 'Q');
PL/SQL procedure successfully completed
使用DBMS_SHARED_POOL来KEEP SEQUENCE的CACHE
SQL> SELECT SEQ_BEAR.NEXTVAL FROM DUAL;
NEXTVAL
----------
103
再次查询，值为103
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered
SQL> SELECT SEQ_BEAR.NEXTVAL FROM DUAL;
NEXTVAL
----------
104
而这时当我们再次刷新SHARED POOL并从SEQUENCE取值的时候，发现SEQUENCE已经不再跳号了。
那么，如何能知道哪些SEQUENCE已经被KEEP了呢？
SQL> select KEPT from v$db_object_cache where name='SEQ_BEAR';
KEPT
----
YES

相关知识拓展：

INCREMENTBY 用于定义序列的步长，如果省略，则默认为1，如果出现负值，则代表序列的值是按照此步长递减的。

START WITH 定义序列的初始值(即产生的第一个值)，默认为1。

MAXVALUE 定义序列生成器能产生的最大值。选项NOMAXVALUE是默认选项，代表没有最大值定义，这时对于递增序列，系统能够产生的最大值是10的27次方;对于递减序列，最大值是-1。

MINVALUE定义序列生成器能产生的最小值。选项NOMAXVALUE是默认选项，代表没有最小值定义，这时对于递减序列，系统能够产生的最小值是?10的26次方;对于递增序列，最小值是1。

CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。CYCLE代表循环，NOCYCLE代表不循环。如果循环，则当递增序列达到最大值时，循环到最小值;对于递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。

CACHE(缓冲)定义存放序列的内存块的大小，默认为20（不做声明的情况下）。NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。

Create Sequence  

你首先要有CREATE SEQUENCE或者CREATE ANY SEQUENCE权限，  
CREATE SEQUENCE emp_seq  
    INCREMENT BY 1        -- 每次加几个  
    START WITH 1         -- 从1开始计数  
    NOMAXVALUE        -- 不设置较大值  
    NOCYCLE         -- 一直累加，不循环  
    CACHE 10; 

如果指定CACHE值，ORACLE就可以预先在内存里面放置一些sequence，这样存取的快些。cache里面的取完后，oracle自动再取一组到cache。使用cache或许会跳号， 比如数据库突然不正常down掉（shutdown abort),cache中的sequence就会丢失. 所以可以在create sequence的时候用nocache防止这种情况。 

创建nocache  sequence在高并发访问时，容易导致row cachelock等待事件，主要原因是每次获取nextval时都需要修改rowcache中的字典信息。使用nocache  sequence，还会导致如下问题:
由于每次修改字典信息都需要commit,可能导致log file sync等待，nocache sequence在RAC环境下，会对基于sequence生成的列创建的索引造成实例间大量索引块争用
基于以上问题,避免创建nocache sequence。

再来看看sequence相关保护机制：
row cache lock：在调用sequence.nextval情况下需要修改数据字典时发生，对应row cache lock事件
SQ lock：在内存缓存(并非rowcache)上获取sequence.nextval时发生，对应enq:SQ-contention事件
SV lock：RAC环境下获取cache+order属性的sequence.nextval时发生，对应DFS lock handle事件

什么情况下使用cache什么时间上使用nocache?

我个人感觉应该尽量使用cache，因为现在的数据库很多都是在高并发的情况下运行的，首先这样可以搞性能，并且也不会产生row cachelock等待事件。可能有些人会担心数据库不正常的down掉会产生序列号间断，但这也是很少的情况。当然如果你的业务要求是绝不能产生间断的序列号，那就要使用nochache了。

你就可以用CURRVAL，NEXTVAL  
CURRVAL=返回sequence的当前值  
NEXTVAL=增加sequence的值，然后返回 sequence 值  
比如：  
  emp_sequence.CURRVAL  
  emp_sequence.NEXTVAL  

 参考地址：

https://blog.csdn.net/agaric717/article/details/6690890

https://blog.csdn.net/jeryjeryjery/article/details/53725634

http://www.dataguru.cn/thread-283432-1-1.html