1、默认值
2、默认情况下
1
| create sequence t0_seq start with 1 maxvalue 100;
|
rac1：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 16:46:40 SCOTT@ testcadb1> select t0_seq.nextval from dual;
NEXTVAL
----------
1
Elapsed: 00:00:00.02
16:46:41 SCOTT@ testcadb1> select t0_seq.nextval from dual;
NEXTVAL
----------
2
Elapsed: 00:00:00.00
16:46:47 SCOTT@ testcadb1> select t0_seq.nextval from dual;
NEXTVAL
----------
3
|
rac2：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 16:47:01 SCOTT@ testcadb2> select t0_seq.nextval from dual;
NEXTVAL
----------
21
Elapsed: 00:00:00.14
16:47:02 SCOTT@ testcadb2> select t0_seq.nextval from dual;
NEXTVAL
----------
22
Elapsed: 00:00:00.01
16:47:04 SCOTT@ testcadb2> select t0_seq.nextval from dual;
NEXTVAL
----------
23
|
3、指定cache大小
1
| create sequence t1_seq start with 1 maxvalue 100 cache 10;
|
rac1:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 16:39:03 SCOTT@ testcadb1> select t1_seq.nextval from dual;
NEXTVAL
----------
1
Elapsed: 00:00:00.09
16:40:10 SCOTT@ testcadb1> select t1_seq.nextval from dual;
NEXTVAL
----------
2
Elapsed: 00:00:00.00
16:40:16 SCOTT@ testcadb1> select t1_seq.nextval from dual;
NEXTVAL
----------
3
|
rac2:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 16:39:56 SCOTT@ testcadb2> select t1_seq.nextval from dual;
NEXTVAL
----------
11
Elapsed: 00:00:00.16
16:39:57 SCOTT@ testcadb2> select t1_seq.nextval from dual;
NEXTVAL
----------
12
Elapsed: 00:00:00.01
16:40:18 SCOTT@ testcadb2> select t1_seq.nextval from dual;
NEXTVAL
----------
13
|
3、指定cache和order
1
| create sequence t2_seq start with 1 maxvalue 100 cache 15 order;
|
rac1：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 17:07:16 SCOTT@ testcadb1> select t2_seq.nextval from dual;
NEXTVAL
----------
1
Elapsed: 00:00:00.03
17:07:31 SCOTT@ testcadb1> select t2_seq.nextval from dual;
NEXTVAL
----------
2
Elapsed: 00:00:00.01
17:07:33 SCOTT@ testcadb1> select t2_seq.nextval from dual;
NEXTVAL
----------
3
|
rac2:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 17:07:37 SCOTT@ testcadb2> select t2_seq.nextval from dual;
NEXTVAL
----------
4
Elapsed: 00:00:00.03
17:07:38 SCOTT@ testcadb2> select t2_seq.nextval from dual;
NEXTVAL
----------
5
Elapsed: 00:00:00.00
17:07:40 SCOTT@ testcadb2> select t2_seq.nextval from dual;
NEXTVAL
----------
6
|
rac1:
1
2
3
4
5
6
7
8
9
| 17:07:33 SCOTT@ testcadb1> select t2_seq.nextval from dual;
NEXTVAL
----------
7
Elapsed: 00:00:00.00
17:08:19 SCOTT@ testcadb1> select t2_seq.nextval from dual;
NEXTVAL
----------
8
|
rac2:
1
2
3
4
5
6
7
8
9
| 17:07:40 SCOTT@ testcadb2> select t2_seq.nextval from dual;
NEXTVAL
----------
9
Elapsed: 00:00:00.01
17:08:39 SCOTT@ testcadb2> select t2_seq.nextval from dual;
NEXTVAL
----------
10
|
4、指定nocache和noorder
1
| create sequence t3_seq start with 1 maxvalue 100 nocache noorder;
|
rac1:
1
2
3
4
5
6
7
8
9
10
| 17:10:30 SCOTT@ testcadb1> select t3_seq.nextval from dual;
NEXTVAL
----------
1
Elapsed: 00:00:00.07
17:10:45 SCOTT@ testcadb1> select t3_seq.nextval from dual;
NEXTVAL
----------
2
Elapsed: 00:00:00.02
|
rac2:
1
2
3
4
5
6
7
8
9
10
| 17:10:55 SCOTT@ testcadb2> select t3_seq.nextval from dual;
NEXTVAL
----------
3
Elapsed: 00:00:00.08
17:10:57 SCOTT@ testcadb2> select t3_seq.nextval from dual;
NEXTVAL
----------
4
Elapsed: 00:00:00.01
|
rac1:
1
2
3
4
| 17:10:47 SCOTT@ testcadb1> select t3_seq.nextval from dual;
NEXTVAL
----------
5
|
rac2:
1
2
3
4
5
6
7
8
9
| 17:10:58 SCOTT@ testcadb2> select t3_seq.nextval from dual;
NEXTVAL
----------
6
Elapsed: 00:00:00.02
17:11:02 SCOTT@ testcadb2> select t3_seq.nextval from dual;
NEXTVAL
----------
7
|
5、指定nocache和order
1
| create sequence t4_seq start with 1 maxvalue 100 nocache order;
|
rac1：
1
2
3
4
5
6
7
8
9
| 17:13:04 SCOTT@ testcadb1> select t4_seq.nextval from dual;
NEXTVAL
----------
1
Elapsed: 00:00:00.01
17:13:04 SCOTT@ testcadb1> select t4_seq.nextval from dual;
NEXTVAL
----------
2
|
rac2：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| 17:13:12 SCOTT@ testcadb2> select t4_seq.nextval from dual;
NEXTVAL
----------
3
Elapsed: 00:00:00.05
17:13:13 SCOTT@ testcadb2> select t4_seq.nextval from dual;
NEXTVAL
----------
4
Elapsed: 00:00:00.03
17:13:15 SCOTT@ testcadb2> select t4_seq.nextval from dual;
NEXTVAL
----------
5
|
rac1：
1
2
3
4
5
6
7
8
9
| 17:13:05 SCOTT@ testcadb1> select t4_seq.nextval from dual;
NEXTVAL
----------
6
Elapsed: 00:00:00.02
17:13:18 SCOTT@ testcadb1> select t4_seq.nextval from dual;
NEXTVAL
----------
7
|
rac2：
1
2
3
4
5
6
7
8
9
| 17:13:15 SCOTT@ testcadb2> select t4_seq.nextval from dual;
NEXTVAL
----------
8
Elapsed: 00:00:00.02
17:13:21 SCOTT@ testcadb2> select t4_seq.nextval from dual;
NEXTVAL
----------
9
|
6、关于cache和nocache在并发比较大的情况下的取值效率，参考链接
1
2
3
4
| cache和nocache参数取值效率对比
RAC两个会话分别处于不同node同时并发循环间断去取4万个值 ：
nocache： 2100s
cache ＝1000： 55s
|
7、结论
根据上面实验可以看到，在rac环境下，使用cache noorder，各个节点缓存的序列是不同的，因此获取到的序列值也不同。使用cache order方式创建的序列，多个实例缓存的相同的序列，很容易存在序列资源争用的问题，因此性能会比noorder 差很多。在非严格要求下建议不要使用order。更要避免使用nocache noorder。
在RAC情况下，使用cache可以尽量调大一点值（默认为20），使用频繁的序列Cache值增加到10000或者更高到50000。