Sunday, October 21, 2012

RAC WAIT EVENT PRACTICAL DEMO

In this demo, we'll try to simulate all the wait events

Step-1: Create a table of arround 40 MB

Step-2: Execute the following command to generate wait events

Fire update1 on instance (1) & select query1 on instance (2), update2 on instance(2) & select query2 on instance(1), update3 on instance(3) & select query3 on instance(1).

QUERY-1
--------
declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 50 and 100;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

QUERY-2
--------

declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 50 and 100;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

QUERY-2
---------

declare
cursor c1 is select prod_id,time_id,amount_sold from rac_wait where prod_id between 101 and 148;
v_prodid number;
v_timeid date;
v_amt_sold number;
begin
open c1;
loop
fetch c1 into v_prodid,v_timeid,v_amt_sold;
exit when c1%notfound;
end loop;
close c1;
end;
/

UPDATE-1
---------

declare
i number:=1;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=50;
end loop;
end;
/


UPDATE-3
---------
declare 
i number:=100;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=148;
end loop;
end;
/


UPDATE-2
---------
declare
i number:=51;
begin
loop
update rac_wait set amount_sold=i where prod_id=i;
i:=i+1;
commit;
exit when i=100;
end loop;
end;
/












No comments:

Post a Comment