Oracle RAC序列性能测试

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/