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

部分測試過程如下：

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();

 

sequence

A   that generates a serial list of unique numbers for table columns.

   

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.

  歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！