【转自Oracle ACS--惜总】正常关闭数据库sequence cache不为0 sequence不跳跃

来源：互联网发布：java知识点重点和难点编辑：程序博客网时间：2024/06/10 00:16

原文网址： http://www.xifenfei.com/5831.html

发表于 2015 年 04 月 05 日由惜分飞

联系：手机(13429648788) QQ(107644445)

链接：http://www.xifenfei.com/5831.html

标题：正常关闭数据库sequence cache不为0 sequence不跳跃

在Oracle中sequence使用很频繁，而大部分使用都配合了cache一起使用,那在sequence+cache一起使用的过程中,如果数据库正常关闭sequence.nextval如果变化,如果数据库异常关闭sequence.nextval又如何变化？这里通过试验进行了证明,结论为：在有cache的sequence中,正常关闭数据库sequence不会出现跳跃,异常关闭数据库很可能导致sequence出现跳跃
创建sequence测试

[oracle@localhost ~]$ sqlplus / assysdba
 
SQL*Plus: Release 11.2.0.4.0 Production onSun Apr 5 15:44:23 2015
 
Copyright (c) 1982, 2013, Oracle.  Allrights reserved.
 
 
Connectedto:
OracleDatabase11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
Withthe Partitioning, OLAP, Data Mining andReal Application Testing options
 
SQL>selectsysdate "www.xifenfei.com"from dual;
 
www.xifen
---------
05-APR-15
 
SQL>createsequence seq_xifenfei
  2   minvalue 1
  3   maxvalue 100000
  4   start with1
  5   increment by1
  6   cache 100;
 
Sequencecreated.
 
SQL>selectSEQ_XIFENFEI.nextval fromdual;
 
   NEXTVAL
----------
         1
 
SQL> /
 
   NEXTVAL
----------
         2
 
SQL> /
 
   NEXTVAL
----------
         3
 
SQL>selectobject_id fromdba_objects whereobject_name='SEQ_XIFENFEI';
 
 OBJECT_ID
----------
     87549
 
SQL> SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        101          1          0          0        100

这里创建了一个名为SEQ_XIFENFEI的sequence,并且nextval已经查询到3,而且cache值设置为100,object_id为87549.

正常关闭数据库测试sequence.nextval变化

SQL> shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System GlobalArea 1570009088 bytes
FixedSize                 2253584 bytes
VariableSize            469765360 bytes
DatabaseBuffers         1090519040 bytes
Redo Buffers                7471104 bytes
Databasemounted.
Databaseopened.
SQL>SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1          4          1          0          0        100
 
SQL>selectSEQ_XIFENFEI.nextval fromdual;
 
   NEXTVAL
----------
         4
 
SQL>SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        104          1          0          0        100

正常关闭数据库启动后,sequence.nextval依然在上次基础之上增加，并为出现跳跃现象.

异常关闭数据库测试sequence.nextval变化

SQL> shutdown  abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System GlobalArea 1570009088 bytes
FixedSize                 2253584 bytes
VariableSize            469765360 bytes
DatabaseBuffers         1090519040 bytes
Redo Buffers                7471104 bytes
Databasemounted.
Databaseopened.
SQL>SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        104          1          0          0        100
 
SQL>selectSEQ_XIFENFEI.nextval fromdual;
 
   NEXTVAL
----------
       104
 
SQL>SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        204          1          0          0        100

异常关闭数据库启动后,sequence.nextvla发生跳跃从本该5直接跳跃到了104.

跟踪数据库正常关闭过程

SQL>altersession setevents '10046 trace name context forever, level 4';
 
Session altered.
 
SQL> shutdown immediate;
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
 
Total System GlobalArea 1570009088 bytes
FixedSize                 2253584 bytes
VariableSize            469765360 bytes
DatabaseBuffers         1090519040 bytes
Redo Buffers                7471104 bytes
Databasemounted.
Databaseopened.
 
SQL> SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        105          1          0          0        100
 
SQL>selectSEQ_XIFENFEI.nextval fromdual;
 
   NEXTVAL
----------
       105
 
SQL>SELECTMINVALUE,HIGHWATER,INCREMENT$, CYCLE#,ORDER$,CACHEfromseq$ whereobj#=87549;
 
  MINVALUE  HIGHWATER INCREMENT$     CYCLE#     ORDER$      CACHE
---------- ---------- ---------- ---------- ---------- ----------
         1        205          1          0          0        100

再次证明了正常关闭数据库后,sequence.nextval未发生跳跃.

分析trace文件

PARSING IN CURSOR #139819144537744 len=129 dep=1 uid=0 oct=6 lid=0 tim=1428220381105111 
hv=2635489469 ad='bc6e6c30'sqlid='4m7m0t6fjcs5x'
updateseq$setincrement$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,
highwater=:8,audit$=:9,flags=:10 where obj#=:1
END OF STMT
BINDS#139819144537744:
 Bind#0
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e288  bln=22  avl=02  flg=09
  value=1
 Bind#1
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e29a  bln=22  avl=02  flg=09
  value=1
 Bind#2
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2ac  bln=22  avl=02  flg=09
  value=100000
 Bind#3
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f2a2edc2da8  bln=22  avl=01  flg=05
  value=0
 Bind#4
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f2a2edc2dc0  bln=22  avl=01  flg=01
  value=0
 Bind#5
  oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2be  bln=22  avl=02  flg=09
  value=100
 Bind#6
  oacdty=02 mxl=22(03) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=bc41e2d0  bln=22  avl=03  flg=09
  value=105     <     ----SEQ_XIFENFEI.nextval值
 Bind#7
  oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00
  oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=bc41e2e2  bln=32  avl=32  flg=09
  value="--------------------------------"
 Bind#8
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=48 off=0
  kxsbbbfp=7f2a2edc2d60  bln=22  avl=01  flg=05
  value=0
 Bind#9
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24
  kxsbbbfp=7f2a2edc2d78  bln=22  avl=04  flg=01
  value=87549                             <---这个就是我们的sequence(SEQ_XIFENFEI)
EXEC#139819144537744:c=0,e=650,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1428220381108066
CLOSE#139819144537744:c=0,e=2,dep=1,type=3,tim=1428220381108119

这里我们找出来了为什么数据库正常关闭sequence.nextval在有cache的情况下,未发生跳跃:因为在数据库正常关闭的情况下,会触发一个update seq$的操作,把当前的sequence.nextval的值更新到seq$.highwater中,从而使得sequence在有cache的情况下，数据库正常关闭未出现nextval跳跃(currval也同样不跳跃)；而在数据库异常关闭之时，数据库不能及时将sequence.nextval更新到eq$.highwater从而引起sequence cache中的值丢失，从而可能出现了sequence使用cache导致跳跃的情况
关于另外一篇关于sequence cache减小update seq$频率的测试,请见:关于oracle sequence一些小测试

0 0