Last active
March 20, 2023 08:00
-
-
Save ilmarkerm/462d14da050fb73fb4eeed5384604f1b to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Goal for this benchmark is just to commit a crazy amounts | |
-- to simulate "traditional" poorly written apps | |
-- It matters because other Oracle benchmarks, even if they stress redo, they do not commit often and make really large redo records | |
-- But I need small redo records and crazy amount of commits | |
-- To tests for issues like this https://access.redhat.com/solutions/5906661 | |
-- This benchmark gives ma a lots of IOPS and tiny average IO sizes | |
-- Ilmar Kerm 2023 | |
create table t (t timestamp not null) partition by hash (t) partitions 64; | |
create or replace procedure commitalot(p_runtime_s number default 60) is | |
v_start timestamp; | |
v_end_interval interval day to second; | |
v_current_time timestamp; | |
begin | |
v_end_interval:= numtodsinterval(p_runtime_s, 'second'); | |
v_start:= sys_extract_utc(systimestamp); | |
loop | |
v_current_time:= sys_extract_utc(systimestamp); | |
insert into t (t) values (v_current_time); | |
commit write wait immediate; | |
exit when v_current_time - v_start > v_end_interval; | |
end loop; | |
end; | |
/ | |
alter system switch logfile; | |
alter system checkpoint; | |
declare | |
starttime timestamp with time zone; | |
runtime number:= 600; | |
num_threads number:= 6; | |
begin | |
execute immediate 'truncate table t reuse storage'; | |
DBMS_WORKLOAD_REPOSITORY.create_snapshot; | |
starttime:= systimestamp + interval '2' second; | |
for i in 1..num_threads loop | |
dbms_scheduler.create_job( | |
job_name=>'committer'||to_char(i), | |
job_type=>'STORED_PROCEDURE', | |
job_action=>'commitalot', | |
number_of_arguments=>1, | |
start_date=>starttime, | |
enabled=>false, | |
auto_drop=>true | |
); | |
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(job_name=>'committer'||to_char(i), argument_position=>1, argument_value=>runtime); | |
dbms_scheduler.enable('committer'||to_char(i)); | |
end loop; | |
commit; | |
dbms_session.sleep(runtime+3); | |
DBMS_WORKLOAD_REPOSITORY.create_snapshot; | |
end; | |
/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment