Aug 302010
 

This post is also available in: Bulgarian

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;
 Posted by at 18:24

Sorry, the comment form is closed at this time.