查看: 1513|回复: 0

[Oracle数据库] 分享ORACLE SEQUENCE跳号总结

发表于 2017-10-10 08:00:04
句号论坛

在ORACLE数据库中,序列(SEQUENCE)是使用非常频繁的一个数据库对象,但是有时候会遇到序列(SEQUECNE)跳号(skip sequence numbers)的情形,那么在哪些情形下会遇到跳号呢?

事务回滚引起的跳号

不管序列有没有CACHE、事务回滚这种情况下,都会引起序列的跳号。如下实验所示:

  1. SQL> create sequence my_sequence
  2. 2 start with 1
  3. 3 increment by 1
  4. 4 maxvalue 99999
  5. 5 nocache;
  6. Sequence created.
  7. SQL> create table test(id number(10), name varchar2(32));
  8. Table created.
  9. SQL> insert into test
  10. 2 select my_sequence.nextval , 'kerry' from dual;
  11. 1 row created.
  12. SQL>
  13. SQL> rollback;
  14. Rollback complete.
  15. SQL> select my_sequence.nextval from dual;
  16. NEXTVAL
  17. ----------
  18. 3
  19. SQL>
复制代码

并发访问序列引起的跳号

并发访问序列引起的跳号,其实不算真正的跳号,而只是逻辑跳号,只是序列值被其它并发会话使用了。我们来构造一起并发访问序列引起的跳号,我们开启两个会话窗口,循环获取序列的值,模拟并发出现的场景。

会话窗口A:

  1. exec dbms_lock.sleep(2); --延迟2秒执行,根据你实验情况调整
  2. /
  3. begin
  4. for i in 1 .. 2000 loop
  5. dbms_output.put_line(my_sequence.nextval);
  6. end loop;
  7. end;
  8. /
复制代码

会话窗口B:

  1. spool test.txt;
  2. begin
  3. waitfor delay '00:00:10';
  4. for i in 1 .. 2000 loop
  5. dbms_output.put_line(my_sequence.nextval);
  6. end loop;
  7. end;
  8. /
  9. spool off;
复制代码

如下所示,我构造的实验当中,你会看到序列的跳号情况。

FLUSH SHARED_POOL会导致CACHE的序列跳号

实验测试如下所示(序列的CACHE值必须大于0),当然正常情况下,很难遇到这种情况。

  1. SQL> select test.my_sequence.nextval from dual;
  2. NEXTVAL
  3. ----------
  4. 17004
  5. SQL> alter sequence test.my_sequence cache 40;
  6. Sequence altered.
  7. SQL> select test.my_sequence.nextval from dual;
  8. NEXTVAL
  9. ----------
  10. 17005
  11. SQL> alter system flush share_pool;
  12. alter system flush share_pool
  13. *
  14. ERROR at line 1:
  15. ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword
  16. SQL> alter system flush shared_pool;
  17. System altered.
  18. SQL> select test.my_sequence.nextval from dual;
  19. NEXTVAL
  20. ----------
  21. 17045
复制代码

数据库实例异常关闭导致跳号

如下实验所示,当数据库使用shutdown abort命令关闭后,重新启动实例,序列缓存在shared pool里面没有用过的值都没有了。一下子从17045跳到17085

  1. SQL> select test.my_sequence.currval from dual;
  2. CURRVAL
  3. ----------
  4. 17045
  5. SQL> select object_id from dba_objects where object_name=upper('my_sequence');
  6. OBJECT_ID
  7. ----------
  8. 97760
  9. SQL> select increment$, minvalue, maxvalue,highwater, cache
  10. 2 from seq$ where obj#=97760;
  11. INCREMENT$ MINVALUE MAXVALUE HIGHWATER CACHE
  12. ---------- ---------- ---------- ---------- ----------
  13. 1 1 99999 17085 40
  14. SQL> shutdown abort;
  15. ORACLE instance shut down.
  16. SQL> startup;
  17. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  18. ORACLE instance started.
  19. Total System Global Area 1620115456 bytes
  20. Fixed Size 2213816 bytes
  21. Variable Size 1258293320 bytes
  22. Database Buffers 352321536 bytes
  23. Redo Buffers 7286784 bytes
  24. Database mounted.
  25. Database opened.
  26. SQL> select test.my_sequence.currval from dual;
  27. select test.my_sequence.currval from dual
  28. *
  29. ERROR at line 1:
  30. ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this session
  31. SQL> select test.my_sequence.nextval from dual;
  32. NEXTVAL
  33. ----------
  34. 17085
  35. SQL>
复制代码

另外,我们也来看看正常关闭数据库的情况下,序列会不会出现跳号,我们采用10046跟踪事件,看看正常数据库关闭情况下,会对序列做一些啥操作

  1. SQL> select test.my_sequence.nextval from dual;
  2. NEXTVAL
  3. ----------
  4. 17085
  5. SQL> alter session set events '10046 trace name context forever, level 4';
  6. Session altered.
  7. SQL> shutdown immediate;
  8. Database closed.
  9. Database dismounted.
  10. ORACLE instance shut down.
  11. SQL> startup
  12. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  13. ORACLE instance started.
  14. Total System Global Area 1620115456 bytes
  15. Fixed Size 2213816 bytes
  16. Variable Size 1258293320 bytes
  17. Database Buffers 352321536 bytes
  18. Redo Buffers 7286784 bytes
  19. Database mounted.
  20. Database opened.
  21. SQL> select test.my_sequence.currval from dual;
  22. select test.my_sequence.currval from dual
  23. *
  24. ERROR at line 1:
  25. ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this session
  26. SQL> select test.my_sequence.nextval from dual;
  27. NEXTVAL
  28. ----------
  29. 17086
  30. SQL>
复制代码

Trace文件中有更新seq$数据字典表,如果你看过我这篇文章ORACLE中seq$表更新频繁的分析,基本上就知道其实seq$中维护的是序列的一些信息。通过跟踪文件,我们知道在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下,数据库正常关闭未出现nextval跳跃(currval也同样不跳跃);而在数据库异常关闭之时,数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失,从而可能出现了sequence使用cache导致跳跃的情况

  1. =====================
  2. PARSING IN CURSOR #25 len=129 dep=1 uid=0 oct=6 lid=0 tim=1504236336294194 hv=2635489469 ad='bf780410' sqlid='4m7m0t6fjcs5x'
  3. update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1
  4. END OF STMT
  5. PARSE #25:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1935744642,tim=1504236336294194
  6. BINDS #25:
  7. Bind#0
  8. oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  9. oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  10. kxsbbbfp=bf45ca48 bln=24 avl=02 flg=09
  11. value=1
  12. Bind#1
  13. oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  14. oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  15. kxsbbbfp=bf45ca5a bln=24 avl=02 flg=09
  16. :/17086
  17. Bind#3
  18. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  19. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  20. kxsbbbfp=2b7d80f57350 bln=24 avl=01 flg=05
  21. value=0
  22. Bind#4
  23. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  24. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  25. kxsbbbfp=2b7d80f57320 bln=24 avl=01 flg=05
  26. value=0
  27. Bind#5
  28. oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  29. oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  30. kxsbbbfp=bf47b85e bln=24 avl=02 flg=09
  31. value=40
  32. Bind#6
  33. oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00
  34. oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0
  35. kxsbbbfp=bf47b870 bln=24 avl=04 flg=09
  36. value=17086
  37. Bind#7
  38. oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  39. oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0
  40. kxsbbbfp=bf47b882 bln=32 avl=32 flg=09
  41. value="--------------------------------"
  42. Bind#8
  43. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  44. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  45. kxsbbbfp=2b7d80f572f0 bln=24 avl=02 flg=05
  46. value=8
  47. Bind#9
  48. oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  49. oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  50. kxsbbbfp=2b7d80f57380 bln=22 avl=04 flg=05
  51. value=97760
  52. EXEC #9:c=999,e=709,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1504236336297033
  53. CLOSE #9:c=0,e=2,dep=1,type=3,tim=1504236336297058
  54. mealink上提到了使用dbms_shared_pool.keep将对象在锁定在shared pool 中,永远不释放。这样可以防止FLUSH SHARED POOL导致序列跳号,但是这个无法避免数据库异常关闭或CRASH引起的跳号
  55. SQL> select test.my_sequence.currval from dual;
  56. CURRVAL
  57. ----------
  58. 17086
  59. SQL> exec dbms_shared_pool.keep('test.my_sequence','q');
  60. PL/SQL procedure successfully completed.
  61. SQL> alter system flush shared_pool;
  62. System altered.
  63. SQL> select test.my_sequence.currval from dual;
  64. CURRVAL
  65. ----------
  66. 17086
  67. SQL> shutdown abort
  68. ORACLE instance shut down.
  69. SQL> startup
  70. ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  71. ORACLE instance started.
  72. Total System Global Area 1620115456 bytes
  73. Fixed Size 2213816 bytes
  74. Variable Size 1258293320 bytes
  75. Database Buffers 352321536 bytes
  76. Redo Buffers 7286784 bytes
  77. Database mounted.
  78. Database opened.
  79. SQL> select test.my_sequence.nextval from dual;
  80. NEXTVAL
  81. ----------
  82. 17126
复制代码

其实如果业务允许,单号出现跳号也无所谓的情形最好,如果碰到业务要求绝对不能出现单号出现跳号的情况,那么就不能使用序列号了,就必须使用其它替代方案,此处不做展开说明!

总结

以上所述是小编给大家介绍的分享ORACLE SEQUENCE跳号总结,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



太阳http代理AD
回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条