horrible_little_oracle_benchmark.sql

-- 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;
/