分享ORACLE SEQUENCE跳號總結

2020-07-26 14:04:11

字體：大中小

來源：轉載

供稿：網友

在ORACLE數據庫中，序列（SEQUENCE）是使用非常頻繁的一個數據庫對象，但是有時候會遇到序列（SEQUECNE）跳號(skip sequence numbers)的情形，那么在哪些情形下會遇到跳號呢？

事務回滾引起的跳號

不管序列有沒有CACHE、事務回滾這種情況下，都會引起序列的跳號。如下實驗所示：

SQL> create sequence my_sequence 2 start with 1 3 increment by 1 4 maxvalue 99999 5 nocache;Sequence created.SQL> create table test(id number(10), name varchar2(32));Table created.SQL> insert into test 2 select my_sequence.nextval , 'kerry' from dual;1 row created.SQL> SQL> rollback;Rollback complete.SQL> select my_sequence.nextval from dual; NEXTVAL----------  3SQL>

并發訪問序列引起的跳號

并發訪問序列引起的跳號，其實不算真正的跳號，而只是邏輯跳號，只是序列值被其它并發會話使用了。我們來構造一起并發訪問序列引起的跳號，我們開啟兩個會話窗口，循環獲取序列的值，模擬并發出現的場景。

會話窗口A：

exec dbms_lock.sleep(2); --延遲2秒執行，根據你實驗情況調整/begin for i in 1 .. 2000 loop dbms_output.put_line(my_sequence.nextval); end loop;end;/

會話窗口B：

spool test.txt;begin waitfor delay '00:00:10'; for i in 1 .. 2000 loop dbms_output.put_line(my_sequence.nextval); end loop;end;/spool off;

如下所示，我構造的實驗當中，你會看到序列的跳號情況。

FLUSH SHARED_POOL會導致CACHE的序列跳號

實驗測試如下所示（序列的CACHE值必須大于0），當然正常情況下，很難遇到這種情況。

SQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17004SQL> alter sequence test.my_sequence cache 40;Sequence altered.SQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17005SQL> alter system flush share_pool;alter system flush share_pool*ERROR at line 1:ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keywordSQL> alter system flush shared_pool;System altered.SQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17045

數據庫實例異常關閉導致跳號

如下實驗所示，當數據庫使用shutdown abort命令關閉后，重新啟動實例，序列緩存在shared pool里面沒有用過的值都沒有了。一下子從17045跳到17085

SQL> select test.my_sequence.currval from dual; CURRVAL---------- 17045SQL> select object_id from dba_objects where object_name=upper('my_sequence'); OBJECT_ID---------- 97760SQL> select increment$, minvalue, maxvalue,highwater, cache 2 from seq$ where obj#=97760;INCREMENT$ MINVALUE MAXVALUE HIGHWATER CACHE---------- ---------- ---------- ---------- ----------  1  1 99999 17085  40SQL> shutdown abort;ORACLE instance shut down.SQL> startup;ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size   2213816 bytesVariable Size  1258293320 bytesDatabase Buffers  352321536 bytesRedo Buffers  7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.currval from dual;select test.my_sequence.currval from dual  *ERROR at line 1:ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this sessionSQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17085SQL>

另外，我們也來看看正常關閉數據庫的情況下，序列會不會出現跳號，我們采用10046跟蹤事件，看看正常數據庫關閉情況下，會對序列做一些啥操作

SQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17085SQL> alter session set events '10046 trace name context forever, level 4';Session altered.SQL> shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size   2213816 bytesVariable Size  1258293320 bytesDatabase Buffers  352321536 bytesRedo Buffers  7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.currval from dual;select test.my_sequence.currval from dual  *ERROR at line 1:ORA-08002: sequence MY_SEQUENCE.CURRVAL is not yet defined in this sessionSQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17086SQL>

Trace文件中有更新seq$數據字典表，如果你看過我這篇文章ORACLE中seq$表更新頻繁的分析，基本上就知道其實seq$中維護的是序列的一些信息。通過跟蹤文件，我們知道在數據庫正常關閉的情況下,會觸發一個update seq$的操作,把當前的sequence.nextval的值更新到seq$.highwater中,從而使得sequence在有cache的情況下，數據庫正常關閉未出現nextval跳躍(currval也同樣不跳躍)；而在數據庫異常關閉之時，數據庫不能及時將sequence.nextval更新到eq$.highwater從而引起sequence cache中的值丟失，從而可能出現了sequence使用cache導致跳躍的情況

=====================PARSING IN CURSOR #25 len=129 dep=1 uid=0 oct=6 lid=0 tim=1504236336294194 hv=2635489469 ad='bf780410' sqlid='4m7m0t6fjcs5x'update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1END OF STMTPARSE #25:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1935744642,tim=1504236336294194BINDS #25: Bind#0 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=bf45ca48 bln=24 avl=02 flg=09 value=1 Bind#1 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=bf45ca5a bln=24 avl=02 flg=09:/17086                      Bind#3 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b7d80f57350 bln=24 avl=01 flg=05 value=0 Bind#4 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b7d80f57320 bln=24 avl=01 flg=05 value=0 Bind#5 oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=bf47b85e bln=24 avl=02 flg=09 value=40 Bind#6 oacdty=02 mxl=22(04) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=bf47b870 bln=24 avl=04 flg=09 value=17086 Bind#7 oacdty=01 mxl=32(32) mxlc=00 mal=00 scl=00 pre=00 oacflg=18 fl2=0001 frm=01 csi=873 siz=32 off=0 kxsbbbfp=bf47b882 bln=32 avl=32 flg=09 value="--------------------------------" Bind#8 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b7d80f572f0 bln=24 avl=02 flg=05 value=8 Bind#9 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=2b7d80f57380 bln=22 avl=04 flg=05 value=97760EXEC #9:c=999,e=709,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1504236336297033CLOSE #9:c=0,e=2,dep=1,type=3,tim=1504236336297058mealink上提到了使用dbms_shared_pool.keep將對象在鎖定在shared pool 中，永遠不釋放。這樣可以防止FLUSH SHARED POOL導致序列跳號，但是這個無法避免數據庫異常關閉或CRASH引起的跳號SQL> select test.my_sequence.currval from dual; CURRVAL---------- 17086SQL> exec dbms_shared_pool.keep('test.my_sequence','q');PL/SQL procedure successfully completed.SQL> alter system flush shared_pool;System altered.SQL> select test.my_sequence.currval from dual; CURRVAL---------- 17086SQL> shutdown abortORACLE instance shut down.SQL> startupORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instanceORACLE instance started.Total System Global Area 1620115456 bytesFixed Size   2213816 bytesVariable Size  1258293320 bytesDatabase Buffers  352321536 bytesRedo Buffers  7286784 bytesDatabase mounted.Database opened.SQL> select test.my_sequence.nextval from dual; NEXTVAL---------- 17126