Jump to content

RAC Attack - Oracle Cluster Database at Home/Sequence Test

Add links
From Wikibooks, open books for an open world


The first test we will perform is a sequence test. Sequential fields often become points of contention in cluster database systems.


  2. First, open a connection to the database as SYSDBA on both nodes.
  3. Setup a table and several sequence types for comparison. Also enable 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;
  4. On node collabn1 measure the differences between various methods. Run this two or three times to warm up the machines. (Note: subtract 500 from the runtimes reported (in hsecs) to account for time in 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;
  5. On the other node - collabn2 - start an anonymous PL/SQL block that retrieves a value every half second. 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; /
  6. Repeat step 3 on node collabn1. See how the results are different as soon as cluster contention is introduced.
  7. Perform more tests, comparing different types of sequences. What conclusions can you draw about sequences? Does caching matter for ORDER sequences? 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;


Retrieved from "https://en.wikibooks.org/w/index.php?title=RAC_Attack_-_Oracle_Cluster_Database_at_Home/Sequence_Test&oldid=2509019"