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;