從SEQUENCE跳號說起

一個應用上線後發現一個使用SEQUENCE值來生成的主鍵經常出現斷號，而且斷號不是一兩個，而是每次幾十個幾十個的跳。而一般能想到的導致這種跳號的 基本就是事務ROLLBAK了導致取出的SEQUENCE沒有被插入到表中去，或者就是資料庫關閉或者宕掉，導致已經CACHE的SEQUENCE的值丟 失，導致的跳號。而在這個場景下，這兩種情況都被一一排除了。那麼到底什麼原因導致的SEQUENCE跳號呢？[@more@]

一般為了提高從SEQUENCE取值的效率，我們都會對SEQUENCE設定CACHE，而且取值越頻繁的，CACHE值設定的越大。那麼這個CACHE的值是儲存在SHARED POOL中的，而且這塊記憶體是可以被其他程式共享的，也就是說SEQUENCE的CACHE是會在一定的情況下被刷出SHARED POOL的，那麼就相當於已經CACHE的SEQUENCE的值就丟失了，這就是導致跳號的原因。下面我們透過一個示例來證明並展開來看看。

SQL> create sequence seq_bear cache 100;

Sequence created
首先建立一個CACHE值為100的SEQUENCE

SQL> select seq_bear.nextval from dual;

NEXTVAL
----------
1
查詢SEQUENCE的值為1，再次查詢的話，SEQUENCE取下一個值，為2

SQL> /

NEXTVAL
----------
2

SQL> alter system flush shared_pool;

System altered

SQL> select seq_bear.nextval from dual;

NEXTVAL
----------
101
而當我們重新整理SHARED_POOL並再次查詢的時候，SEQUENCE的值直接跳到了101，說明已經CACHE的值除了被使用的1和2之外，其他的98個全部丟失了。


那麼我們如何避免這種情況呢？ORACLE給我們提供了把SEQUENCE KEEP到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
透過查詢V$DB_OBJECT_CACHE可以看到物件是否已經被KEEP在SHARED POOL，同時，這個檢視中還提供了其他的一些資訊，比如物件被LOAD了多少次、佔用多少記憶體空間等等資訊


DBMS_SHARED_POOL不僅可以KEEP SEQUENCE的CACHE，還可以KEEP住儲存過程、包、SQL等等，保證這些不會被交換出SHARED POOL。而KEEP方法使用也很簡單，只需要傳入要KEEP的物件的名稱，如果是其他使用者的，則使用USERNAME.OBJECT_NAME作為傳入引數；後面一個參數列示要KEEP的物件的型別，比如是包、SEQUENCE、SQL還是其他。具體的取值可以參考ORACLE的文件，上面寫的很詳細。

同時，DBMS_SHARED_POOL還提供了其他幾個方法：

ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER)：這個方法可以設定一個界限，保證如果要進入SHARED POOL的物件太大，那麼可以設定一個閥值，超過這個閥值的直接報錯，而不是經過LRU查詢和記憶體交換之後發現SHARED POOL不夠了再報錯，可以防止超大物件過度佔用SHARED POOL空間。

UNKEEP就是KEEP的反操作

SIZES (minsize NUMBER)：這個是列出SHARED POOL中所有大於minsize的物件，對於查詢SHARED POOL中大物件並設定合理的ABORTED_REQUEST_THRESHOLD很有用。

總結：
SEQUENCE跳號可能是事務ROLLBAK或者例項被宕過或者是SEQUENCE的CACHE被交換出去過，對於有特殊要求的SEQUENCE或者包、儲存過程、觸發器等等，可以使用DBMS_SHARED_POOL.KEEP方法，把他們永久保留在SHARED POOL中，從而可以實現一些特定的用途。