在ORACLE數(shù)據(jù)庫(kù)中,序列(SEQUENCE)是使用非常頻繁的一個(gè)數(shù)據(jù)庫(kù)對(duì)象,但是有時(shí)候會(huì)遇到序列(SEQUECNE)跳號(hào)(skip sequence numbers)的情形,那么在哪些情形下會(huì)遇到跳號(hào)呢?
事務(wù)回滾引起的跳號(hào)
不管序列有沒有CACHE、事務(wù)回滾這種情況下,都會(huì)引起序列的跳號(hào)。如下實(shí)驗(yàn)所示:
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 ---------- 3 SQL>
并發(fā)訪問序列引起的跳號(hào)
并發(fā)訪問序列引起的跳號(hào),其實(shí)不算真正的跳號(hào),而只是邏輯跳號(hào),只是序列值被其它并發(fā)會(huì)話使用了。我們來構(gòu)造一起并發(fā)訪問序列引起的跳號(hào),我們開啟兩個(gè)會(huì)話窗口,循環(huán)獲取序列的值,模擬并發(fā)出現(xiàn)的場(chǎng)景。
會(huì)話窗口A:
exec dbms_lock.sleep(2); --延遲2秒執(zhí)行,根據(jù)你實(shí)驗(yàn)情況調(diào)整 / begin for i in 1 .. 2000 loop dbms_output.put_line(my_sequence.nextval); end loop; end; /
會(huì)話窗口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;
如下所示,我構(gòu)造的實(shí)驗(yàn)當(dāng)中,你會(huì)看到序列的跳號(hào)情況。
FLUSH SHARED_POOL會(huì)導(dǎo)致CACHE的序列跳號(hào)
實(shí)驗(yàn)測(cè)試如下所示(序列的CACHE值必須大于0),當(dāng)然正常情況下,很難遇到這種情況。
SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17004 SQL> alter sequence test.my_sequence cache 40; Sequence altered. SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17005 SQL> alter system flush share_pool; alter system flush share_pool * ERROR at line 1: ORA-02000: missing SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT keyword SQL> alter system flush shared_pool; System altered. SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17045
數(shù)據(jù)庫(kù)實(shí)例異常關(guān)閉導(dǎo)致跳號(hào)
如下實(shí)驗(yàn)所示,當(dāng)數(shù)據(jù)庫(kù)使用shutdown abort命令關(guān)閉后,重新啟動(dòng)實(shí)例,序列緩存在shared pool里面沒有用過的值都沒有了。一下子從17045跳到17085
SQL> select test.my_sequence.currval from dual; CURRVAL ---------- 17045 SQL> select object_id from dba_objects where object_name=upper('my_sequence'); OBJECT_ID ---------- 97760 SQL> select increment$, minvalue, maxvalue,highwater, cache 2 from seq$ where obj#=97760; INCREMENT$ MINVALUE MAXVALUE HIGHWATER CACHE ---------- ---------- ---------- ---------- ---------- 1 1 99999 17085 40 SQL> shutdown abort; ORACLE instance shut down. SQL> startup; ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size 2213816 bytes Variable Size 1258293320 bytes Database Buffers 352321536 bytes Redo Buffers 7286784 bytes Database 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 session SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17085 SQL>
另外,我們也來看看正常關(guān)閉數(shù)據(jù)庫(kù)的情況下,序列會(huì)不會(huì)出現(xiàn)跳號(hào),我們采用10046跟蹤事件,看看正常數(shù)據(jù)庫(kù)關(guān)閉情況下,會(huì)對(duì)序列做一些啥操作
SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17085 SQL> 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> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size 2213816 bytes Variable Size 1258293320 bytes Database Buffers 352321536 bytes Redo Buffers 7286784 bytes Database 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 session SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17086 SQL>
Trace文件中有更新seq$數(shù)據(jù)字典表,如果你看過我這篇文章ORACLE中seq$表更新頻繁的分析,基本上就知道其實(shí)seq$中維護(hù)的是序列的一些信息。通過跟蹤文件,我們知道在數(shù)據(jù)庫(kù)正常關(guān)閉的情況下,會(huì)觸發(fā)一個(gè)update seq$的操作,把當(dāng)前的sequence.nextval的值更新到seq$.highwater中,從而使得sequence在有cache的情況下,數(shù)據(jù)庫(kù)正常關(guān)閉未出現(xiàn)nextval跳躍(currval也同樣不跳躍);而在數(shù)據(jù)庫(kù)異常關(guān)閉之時(shí),數(shù)據(jù)庫(kù)不能及時(shí)將sequence.nextval更新到eq$.highwater從而引起sequence cache中的值丟失,從而可能出現(xiàn)了sequence使用cache導(dǎo)致跳躍的情況
===================== 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#=:1 END OF STMT PARSE #25:c=0,e=21,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1935744642,tim=1504236336294194 BINDS #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=97760 EXEC #9:c=999,e=709,p=0,cr=1,cu=2,mis=0,r=1,dep=1,og=4,plh=1935744642,tim=1504236336297033 CLOSE #9:c=0,e=2,dep=1,type=3,tim=1504236336297058 mealink上提到了使用dbms_shared_pool.keep將對(duì)象在鎖定在shared pool 中,永遠(yuǎn)不釋放。這樣可以防止FLUSH SHARED POOL導(dǎo)致序列跳號(hào),但是這個(gè)無法避免數(shù)據(jù)庫(kù)異常關(guān)閉或CRASH引起的跳號(hào) SQL> select test.my_sequence.currval from dual; CURRVAL ---------- 17086 SQL> 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 ---------- 17086 SQL> shutdown abort ORACLE instance shut down. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1620115456 bytes Fixed Size 2213816 bytes Variable Size 1258293320 bytes Database Buffers 352321536 bytes Redo Buffers 7286784 bytes Database mounted. Database opened. SQL> select test.my_sequence.nextval from dual; NEXTVAL ---------- 17126
其實(shí)如果業(yè)務(wù)允許,單號(hào)出現(xiàn)跳號(hào)也無所謂的情形最好,如果碰到業(yè)務(wù)要求絕對(duì)不能出現(xiàn)單號(hào)出現(xiàn)跳號(hào)的情況,那么就不能使用序列號(hào)了,就必須使用其它替代方案,此處不做展開說明!
總結(jié)
以上所述是小編給大家介紹的分享ORACLE SEQUENCE跳號(hào)總結(jié),希望對(duì)大家有所幫助,如果大家有任何疑問請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持!
標(biāo)簽:自貢 遼源 紹興 寧波 昭通 百色 中衛(wèi) 海東
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《分享ORACLE SEQUENCE跳號(hào)總結(jié)》,本文關(guān)鍵詞 分享,ORACLE,SEQUENCE,跳號(hào),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。