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