Oracle RAC序列性能测试
测试Oracle RAC环境下,sequence的cache和order对性能的影响;
创建sequence如果不指定cache和order选项,默认为cache 20,noorder;
指定cache值,可以预先在内存里面放置一些Sequence,这样存取的快些,在RAC环境中建议指定较大的cache值。
指定ORDER值,在单实例环境没有影响,在RAC环境时,多实例实际缓存相同的序列,多实例并发取序列时,会有数据字典争用问题,RAC环境下尽量使用NOORDER选项。
测试结果如下:
虚拟机性能非常不稳定,时间会有一些偏差;
cache
100
10
100
10
no
no
order
no
no
order
order
no
order
SQL时间(秒)
13
63
24
63
813
672
对应的等待事件如下:
结论:
RAC环境中,创建sequence尽量使用大一些的cache(默认只有20),尽量使用noorder;
---本实验参考于:
Oracle+RAC性能调优案例分析---高斌老师
Database SQL Language Reference---CREATE SEQUENCE
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314
部分测试过程如下:
CHENJCH@ncdb1> create table t1(id number,comments varchar2(200));
Table created.
CHENJCH@ncdb1> create sequence test_seq minvalue 1 maxvalue 99999999999999999999 increment by 1 start with 1 cache 100 noorder nocycle;
Sequence created.
一:cache测试
节点一:
CHENJCH@ncdb1> set timing on
CHENJCH@ncdb1>
begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.63
节点二:
CHENJCH@ncdb2> set timing on
CHENJCH@ncdb2> begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.60
降低cache值
CHENJCH@ncdb1> alter sequence test_seq cache 10;
节点一:
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
CHENJCH@ncdb1> begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:03.86
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
节点二:
CHENJCH@ncdb2> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
CHENJCH@ncdb2> begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:01:02.22
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
二:order测试
CHENJCH@ncdb1> alter sequence test_seq order cache 100;
节点一:
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
CHENJCH@ncdb1> begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.71
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
节点二:
CHENJCH@ncdb2> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
CHENJCH@ncdb2> begin
for i in 1..50000 loop
insert into t1 values(test_seq.nextval,'comments');
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:24.60
CHENJCH@ncdb1> exec dbms_workload_repository.create_snapshot();
https://docs.oracle.com/cd/E11882_01/server.112/e40540/glossary.htm#CNCPT89131
sequence
A schema object that generates a serial list of unique numbers for table columns.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314
CACHE Specify how many values of the sequence the database preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
Note:
Oracle recommends using the CACHE setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
NOCACHE Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.
ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
NOORDER Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29785807/viewspace-2155612/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29785807/viewspace-2155612/