Master Yavor from Tryavna

За да възпроизведа проблемите, описани в статията; както и за да тествам евентуалните решения, драснах набързо един тестова пакет. Има си кусури – примерно не се държи правилно ако 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;