In order to reproduce the problems, described in my post; and to test the possible solutions, I made a quick testing package. It has some flaws – for example, does not behave nice if the config table is empty – but it is a “quick and dry” solution. I put it here, so I do not loose it 🙂
The idea is: one simple script starts however sql*plus I want (for example 20 or 40).
for i in `seq 1 20`;
do
sqlplus /nolog @loader.sql &
done;
Then every instance calls the procedure start_single_process. That’s all
Loading data can be started when you execute (form another, “control” session) the procedure start_load. I would always give some “wait”, even small (0.01 sec). If you do not give “wait” at all, it gets ugly.
You can stop the loading at every moment by executing pause_load. Executing stop_load makes all sql*plus sessions to exit.
create or replace package multi_ses_load is
c_status_start constant number := 1; -- the LOADER sessions should run
c_status_pause constant number := 0; -- the LOADER sessions should stop
c_status_exit constant number := 2; -- the LOADER sessions should exit
-- every LOADER process should call this procedure. The procedure will wait for the START signal
procedure start_single_process;
-- issies the START signal. Parameters:
-- part_no: in which partition should we load data
-- wait: how many seconds to wait between 2 inserts
-- refresh_iterations: how often to refresh the data from the config table
procedure start_load(in_part_no in number,
in_wait_sec in number default 0,
in_refresh_iterations in number default 10);
-- changes in which partition should we load data
procedure change_part(in_part_no in number);
-- changes how many seconds to wait between 2 inserts
procedure change_wait(in_wait_sec in number);
-- changes how often to refresh the data flrom the config table
procedure change_refresh(in_refresh_iterations in number);
-- stops the load process until another START signal
procedure pause_load;
-- restarts the load without changing any parameters
procedure restart_load;
-- makes the loading processes to exit
procedure stop_load;
end multi_ses_load;
create or replace package body multi_ses_load is
c_alert_name constant varchar2(20) := 'SESS_ALERT';
-- every LOADER process should call this procedure. The procedure will wait for the START signal
procedure start_single_process is
v_alert_msg VARCHAR2(1800);
v_alert_status PLS_INTEGER;
v_part_no number;
v_wait_sec number;
v_refresh_iterations number;
v_curr_stat number;
v_ses_clob clob;
v_my_sid number;
v_seed number;
procedure read_config_vars is
begin
-- reed the config variables form teh config table
select curr_stat, part_no, wait_sec, refresh_iterations
into v_curr_stat, v_part_no, v_wait_sec, v_refresh_iterations
from ses_load_parames;
end;
begin
-- init the random generator. we will use the current SID and number of seconds since mignight for seed
Select Sid
into v_my_sid
from v$mystat
where rownum = 1;
v_seed := (sysdate - trunc(sysdate)) * 1000 * (24 * 60 * 60) + v_my_sid;
dbms_random.initialize(v_seed);
-- read the config vars
read_config_vars;
while v_curr_stat in (c_status_start, c_status_pause) -- if the current status is START or PAUSE, we proceed;
loop
if v_curr_stat = c_status_pause then
-- if the status us PAUSE, wait for the signal
-- we should register the current session as a consumer for the alert
dbms_alert.register(c_alert_name);
-- now we can wait for the alert
dbms_alert.waitone(c_alert_name, v_alert_msg, v_alert_status);
-- refresh the config vars - they may be changed
read_config_vars;
if v_curr_stat = c_status_exit then
exit;
end if;
end if;
-- do 'v_refresh_iterations' inserts
for i in 1 .. v_refresh_iterations loop
-- insert a row into 'v_part_no' partition
insert into ...
values (dbms_random.string('P', 32), ...); -- here we should take care of the partition placement
commit;
dbms_lock.sleep(v_wait_sec); -- wiat for 'v_wait_sec' seconds
end loop;
-- refresh the config vars after whe have done enough iterations
read_config_vars;
end loop;
end;
-- issues the START signal. Parameters:
-- part_no: in which partition should we load data
-- wait: how many seconds to wait between 2 inserts
-- refresh_iterations: how often to refresh the data flrom the config table
procedure start_load(in_part_no in number,
in_wait_sec in number default 0,
in_refresh_iterations in number default 10) is
begin
-- we set the parameters in the config table
update ses_load_parames
set curr_stat = c_status_start, part_no = in_part_no, wait_sec = in_wait_sec,
refresh_iterations = in_refresh_iterations;
commit;
-- set teh LOADER processes free :)
dbms_alert.signal(c_alert_name, 'Газ!');
commit;
end;
-- changes in which partition should we load data
procedure change_part(in_part_no in number) is
begin
update ses_load_parames
set part_no = in_part_no;
commit;
end;
-- changes how many seconds to wait between 2 inserts
procedure change_wait(in_wait_sec in number) is
begin
update ses_load_parames
set wait_sec = in_wait_sec;
commit;
end;
-- changes how often to refresh the data flrom the config table
procedure change_refresh(in_refresh_iterations in number) is
begin
update ses_load_parames
set refresh_iterations = in_refresh_iterations;
commit;
end;
-- stops the load process until another START signal
procedure pause_load is
begin
update ses_load_parames
set curr_stat = c_status_pause;
commit;
end;
-- restarts the load without changing any parameters
procedure restart_load is
begin
-- we set the parameters in the config table
update ses_load_parames
set curr_stat = c_status_start;
commit;
-- set the LOADER processes free :)
dbms_alert.signal(c_alert_name, 'Аре пак на пангара!');
commit;
end;
-- makes the loading processes to exit
procedure stop_load is
begin
-- put the exit flag in teh table
update ses_load_parames
set curr_stat = c_status_exit;
commit;
-- is some process is in PAUSE, we should alert him to reread the config vars
dbms_alert.signal(c_alert_name, 'Аре да си одиме, момчета!');
commit;
end;
end multi_ses_load;