Script SDA Test Data
Script zum Testdate erstelle mit Hilf vom Package SDA_Test
/*
1. Install RBSC$SDA_TEST (SCRIPT PACKAGE)
2. Run the preparation blocks (below)
3. Run one of the scripts below to generate clients in the desired BU
*/
-------------------------------------------------------------------------------
-- START PREPARATION
-------------------------------------------------------------------------------
exec dbms_session.reset_package;
exec session#.open_session;
begin
-- Fix the DOCM keys (required for person modifications)
update obj_rel_key set key_val = 'U39' where obj_key_id = 47 and obj_id = 24870980;
update obj_rel_key set key_val = 'U40' where obj_key_id = 47 and obj_id = 24871003;
update obj_rel_key set key_val = 'U41' where obj_key_id = 47 and obj_id = 24871028;
commit;
end;
/
-- Fix the person sequence numbers caused by anonymisation
declare
l_target_start_id number := 500000;
l_dummy_id number;
begin
install#.log#write('Checking person sequence numbers');
for s in (
select s.*, 500000-s.last_number fix
from dba_sequences s
where 1=1
and sequence_name like upper('RBSC_PERSON_NR%')
and (500000-s.last_number) != 0
) loop
install#.log#write('Fixing '||s.sequence_name);
execute immediate 'alter sequence '||s.sequence_name||' increment by '||s.fix;
execute immediate 'select '||s.sequence_name||'.nextval from dual' into l_dummy_id;
execute immediate 'alter sequence '||s.sequence_name||' increment by 1';
end loop;
end;
/
-------------------------------------------------------------------------------
-- END PREPARATION
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- Create one or more clients (sequential)
declare
l_bu_id number := 12;
l_clt_cnt number := 1;
l_start timestamp;
l_end timestamp;
l_start_date date := trunc(sysdate);
l_dur_sec number;
begin
l_start := current_timestamp;
session#.open_session(i_bu_id=>l_bu_id, i_oracle_user=>sys_context('USERENV', 'OS_USER'));
for i in 1..l_clt_cnt loop
s#rbsc$sda_test.create_clt(i_migr_key => '1001'||l_clt_cnt);
end loop;
l_end := current_timestamp;
install#.log#write('Start : '||(l_start));
install#.log#write('End : '||(l_end));
install#.log#write('Duration: '||(l_end-l_start));
l_dur_sec := (l_start_date + (l_end-l_start) - l_start_date) * 86400;
install#.log#write('Avq/perf: '||l_clt_cnt/(l_dur_sec/60)||' per min');
exception
when others then
dbms_output.put_line(regexp_substr(sqlerrm, '.*')||chr(10)||' log# '||log#.log#init_log_id(co_err.last_err_log_id)||': '||log#.log#ctx(log#.log#init_log_id(co_err.last_err_log_id)));
end;
/
-------------------------------------------------------------------------------
-- Create many clients (parallel execution)
declare
l_bu_id number := 12;
l_slave_cnt number := 40;
l_clt_cnt number := 50000;
l_cin_prefix number := 10000000;
l_start timestamp;
l_end timestamp;
l_start_date date := trunc(sysdate);
l_dur_sec number;
begin
l_start := current_timestamp;
rbsc$prl_sql#.start_slaves(l_slave_cnt, 'Create Clients');
for i in 1..l_clt_cnt loop
rbsc$prl_sql#.send_stmt('begin session#.open_session(i_bu_id=>'||l_bu_id||', i_oracle_user=>'''||sys_context('USERENV', 'OS_USER')||'''); s#rbsc$sda_test.create_clt(i_migr_key => '''||(l_cin_prefix+i)||'''); end;');
end loop;
rbsc$prl_sql#.wait_feedback();
rbsc$prl_sql#.stop_slaves();
l_end := current_timestamp;
install#.log#write('Start : '||(l_start));
install#.log#write('End : '||(l_end));
install#.log#write('Duration: '||(l_end-l_start));
l_dur_sec := (l_start_date + (l_end-l_start) - l_start_date) * 86400;
install#.log#write('Avq/perf: '||l_clt_cnt/(l_dur_sec/60)||' per min');
exception
when others then
dbms_output.put_line(regexp_substr(sqlerrm, '.*')||chr(10)||' log# '||log#.log#init_log_id(co_err.last_err_log_id)||': '||log#.log#ctx(log#.log#init_log_id(co_err.last_err_log_id)));
end;
/
-------------------------------------------------------------------------------
