RAC Attack - Oracle Cluster Database at Home/Sequence Test

Prev: Services, Failover and Load Balancing

---

---

RAC SQL and PLSQL (e)

---

1. Install Runstats
2. Sequence Test
3. Parallel Query Test
4. Scheduler Test
5. File Test

---

---

Next: RAC Backups and Recovery

---

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

---

1. First, open a connection to the database as SYSDBA on both nodes.

   
   

2. 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;
   

   
   

3. 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;
   

   
   

4. 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;
   /
   

   
   

5. Repeat step 3 on node collabn1. See how the results are different as soon as cluster contention is introduced.

   
   

6. 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;