За да възпроизведа проблемите, описани в статията; както и за да тествам евентуалните решения, драснах набързо един тестова пакет. Има си кусури – примерно не се държи правилно ако config таблицата е празна – но за решение „набързо“ се получи добре. Всъщност толкова добре, че реших да го сложа тук, да не го загубя 🙂
Идеята е следната: с едно елементарно скриптче се пускат произволен брой sql*plus сесии (аз пробвах с 20 и 40).
for i in `seq 1 20`;
do
sqlplus /nolog @loader.sql &
done;
Всяка от тях извиква процедурата start_single_process и толкова.
Зареждането започва с извикване (от друга – контролна – сесия) на start_load. Добре е да се зададе някакъв wait, пък бил той и 0.01 сек. Иначе става тегаво.
По всяко време зареждането може да бъде прекъснато с pause_load. Извикването на stop_load кара sql*plus сесиите да приключат.
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;