Oracle RAC性能 Oracle sequence 性能测试

在Oracle RAC环境中，系统性能会受到影响，主要集中在Block在instance之间的传输，尽管oracle 10g 中采用了Cache Fusion来改善以前重disk中读取数据。其中的一个部分就是关于oracle sequence的性能问题
下面是关于sequence 的cache和order对rac 性能的影响，从测试中看到增大cache和采用non-ordered的sequence性能受到的影响很小。
drop sequence test_rac;
create sequence test_rac;
set timing on
declare
  dummy number;
begin
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;
end;
/
cache 20, non-ordered
one instance - 2.46 seconds
two instance - 6.20 seconds
drop sequence test_rac;
create sequence test_rac order;
set timing on
declare
  dummy number;
begin
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;
end;
/
cache 20, ordered
one instance - 5.29 seconds
two instance - 13.30 seconds
drop sequence test_rac;
CREATE SEQUENCE test_rac
 CACHE 1000;
set timing on
declare
  dummy number;
begin
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;
end;
/
cache 1000, non-ordered
one instance - 1.16 seconds
two instance - 1.54 seconds
drop sequence test_rac;
CREATE SEQUENCE test_rac
 CACHE 1000 ORDER;
set timing on
declare
  dummy number;
begin
for i in 1..50000 loop
  select test_rac.nextval into dummy from dual;
end loop;
end;
/
cache 1000, ordered
one instance - 3.46 seconds
two instance - 8.47 seconds