使用sequence的cache特性時要注意的

我們在使用sequence時為了提升效能通常都會使用sequence的cache功能，但是潛在的危險就是例項crash或者由於shared pool碎片較多容易出現ora-04031錯誤時，此時我們都會想到alter system flush shared pool...flush之後cache的sequence值全部丟失從而造成序列不連續...

[@more@]

--事先建立一個序列

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
1

SQL> select seq_test.nextval from dual;

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

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
3

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
4

--在flush shard poo之後很明顯被cache的sequence值全部丟失了

SQL> alter system flush shared_pool;

系統已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
21

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
22

--為了保證能不丟失被cache的sequence值，那麼在flush shared pool之前最好使序列暫時nocache

SQL> alter sequence seq_test nocache;

序列已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
23

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
24

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
25

SQL>

--如果既想使用序列的cache功能還想使flush shared pool使cached sequence值不丟失，此時可以把這個squence keep在shared pool中

SQL> alter sequence seq_test cache 20;

序列已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
26

SQL> alter system flush shared_pool;

系統已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
46

SQL> exec dbms_shared_pool.keep('seq_test','Q')

PL/SQL 過程已成功完成。

SQL> col name format a10
SQL> select name,kept from v$db_object_cache where name='SEQ_TEST';

NAME KEP
---------- ---
SEQ_TEST YES

SQL> alter sequence seq_test cache 30;

序列已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
47

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
48

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
49

SQL> alter system flush shared_pool;

系統已更改。

SQL> select seq_test.nextval from dual;

NEXTVAL
----------
50

SQL>

--顯然keep在shared pool裡面之後再flush shared pool被cache 的sequence值就不會丟失了