今天一个同事问了我一个问题：sequence是否可以创建同义词，答案是可以的，但是在做确认性测试的时候，碰到了一大推推推的问题，还真是以前没有注意的，现SHARE出来：

需要注意的地方是在RAC环境中，有可能会出现Sequence不连续的情况，是因为：
The sequence cache is in the shared pool, therefore sessions on the same node can share the cached entry, but sessions on different nodes cannot.所以CACHE 20会造成RAC的sequence不连续。

下面首先模拟CACHE与NOCACHE的情况In RAC Instance

1. 默认CACHE (20)

a. 同义词和序列属于同一个OWNER
SQL> CONN support/supporta@testdb1_dd
SQL> CREATE SEQUENCE TEST_SEQ1 INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 MINVALUE 1 CACHE 20;
Sequence created
SQL> CREATE SYNONYM SYN_SEQ1 FOR TEST_SEQ1;
Synonym created
SQL> select test_seq1.nextval from dual;
   NEXTVAL
----------
         1
SQL> select test_seq1.nextval from dual;
   NEXTVAL
----------
         2
SQL> select syn_seq1.nextval from dual;
   NEXTVAL
----------
         3
SQL> select syn_seq1.nextval from dual;
   NEXTVAL
----------
         4
SQL> grant select on test_seq1 to commonnew;
Grant succeeded

b. 同义词和序列不属于同一个OWNER
SQL> conn commonnew/commonnew@testdb1_dd;
SQL> CREATE SYNONYM SYN_SEQ1 FOR support.test_seq1;
Synonym created
SQL> select support.test_seq1.nextval from dual;
   NEXTVAL
----------
        5
SQL> select syn_seq1.nextval from dual;
   NEXTVAL
----------
        6
SQL> CONN support/supporta@testdb1_dd
SQL> select syn_seq1.nextval from dual;
   NEXTVAL
----------
         7
SQL> CONN support/supporta@testdb2_dd; --实例2
SQL> select syn_seq1.nextval from dual;
   NEXTVAL
----------
         21


2. NOCACHE

a. 同义词和序列属于同一个OWNER
SQL> CONN support/supporta@testdb1_dd;
SQL> CREATE SEQUENCE TEST_SEQ2 INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 MINVALUE 1 NOCACHE;
Sequence created
OR (两个语句都可以达到SEQUENCE连续的目的)
SQL> CREATE SEQUENCE TEST_SEQ2 INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 MINVALUE 1 CACHE 20 ORDER;
Sequence created
SQL> CREATE SYNONYM SYN_SEQ2 FOR TEST_SEQ2;
SYNONYM created
SQL> select TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         1
SQL> select TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         2
SQL> select TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         3
SQL> select TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         4
SQL> grant select on TEST_SEQ2 to commonnew;
Grant succeeded

b. 同义词和序列不属于同一个OWNER
SQL> conn commonnew/commonnew@testdb1_dd;
SQL> CREATE SYNONYM SYN_SEQ2 FOR support.TEST_SEQ2;
SYNONYM created
SQL> select support.SYN_SEQ2.nextval from dual;
   NEXTVAL
----------
         5
SQL> select support.TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         6
SQL> select SYN_SEQ2.nextval from dual;
   NEXTVAL
----------
         7
SQL> conn commonnew/commonnew@testdb2_dd; --实例2
SQL> select support.SYN_SEQ2.nextval from dual;
   NEXTVAL
----------
         8
SQL> select support.TEST_SEQ2.nextval from dual;
   NEXTVAL
----------
         9
SQL> select SYN_SEQ2.nextval from dual;
   NEXTVAL
----------
        10

注意：使用NOCACHE ORDER和 CACHE 对数据库性能影响是非常大的，几乎相差20倍，所以建议如果业务逻辑允许的条件下，不要使用NOCHACE ORDER。

Results:

50 000 loops with cache = 20 (default)
1 node = 5 seconds
2 nodes at same time = 14 seconds
2 nodes at same time rdered = 30 seconds

50 000 loops with cache = 1000
1 node = 1.5 seconds
2 nodes at same time = 1.8 seconds
2 nodes at same time rdered = 20 seconds

参考文献：
1. http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/
2. http://andzen.ycool.com/post.2959597.html

Sequence in cache & nocahce