RAC 攻击 - Oracle 集群数据库在家/序列测试

上一篇: 服务、故障转移和负载均衡

RAC SQL 和 PLSQL (e)

下一篇: RAC 备份和恢复

我们将进行的第一个测试是序列测试。在集群数据库系统中，顺序字段通常会成为争用点。

首先，以 SYSDBA 身份在**两个节点**上打开到数据库的连接。
设置一个表和几个不同类型的序列进行比较。同时启用 serveroutput。create table SEQTEST (seqid varchar2(30), highval number); insert into SEQTEST values ('MYTABLE', 1); commit; create sequence SEQTEST_O_NC ORDER NOCACHE; create sequence SEQTEST_O_C ORDER CACHE 100; create sequence SEQTEST_NO_NC NOORDER NOCACHE; create sequence SEQTEST_NO_C NOORDER CACHE 100; set serveroutput on;
在节点**collabn1**上测量不同方法之间的差异。运行两到三次以预热机器。（注意：从报告的运行时间（以 hsecs 为单位）中减去 500，以考虑 DBMS_LOCK.SLEEP 中的时间。）exec runstats_pkg.rs_start; DECLARE myval number; BEGIN FOR counter IN 1..10 LOOP select highval into myval from SEQTEST where seqid='MYTABLE' for update; update SEQTEST set highval=highval+1 where seqid='MYTABLE'; dbms_lock.sleep(0.5); commit; END LOOP; END; / exec runstats_pkg.rs_middle; DECLARE myval number; BEGIN FOR counter IN 1..10 LOOP myval := SEQTEST_O_C.NEXTVAL; dbms_lock.sleep(0.5); commit; END LOOP; END; / exec runstats_pkg.rs_stop;
在另一个节点 - **collabn2** - 启动一个匿名 PL/SQL 块，它每半秒检索一次值。DECLARE myval number; BEGIN LOOP select highval into myval from SEQTEST where seqid='MYTABLE' for update; update SEQTEST set highval=highval+1 where seqid='MYTABLE'; select SEQTEST_O_NC.NEXTVAL into myval from dual; select SEQTEST_O_C.NEXTVAL into myval from dual; select SEQTEST_NO_NC.NEXTVAL into myval from dual; select SEQTEST_NO_C.NEXTVAL into myval from dual; dbms_lock.sleep(0.5); commit; END LOOP; END; /
在节点**collabn1**上重复步骤 3。观察一旦引入集群争用，结果将如何不同。
执行更多测试，比较不同类型的序列。你能得出关于序列的哪些结论？缓存对 ORDER 序列有影响吗？exec runstats_pkg.rs_start; DECLARE myval number; BEGIN FOR counter IN 1..10 LOOP myval := SEQTEST_O_NC.NEXTVAL; dbms_lock.sleep(0.5); commit; END LOOP; END; / exec runstats_pkg.rs_middle; DECLARE myval number; BEGIN FOR counter IN 1..10 LOOP myval := SEQTEST_O_C.NEXTVAL; dbms_lock.sleep(0.5); commit; END LOOP; END; / exec runstats_pkg.rs_stop;