Oracle. Влияние коммита на время выполнения задачи.

Многие наверно знают, что частые коммиты — это плохо и медленно, но насколько плохо и насколько медленно, не понятно и данный маленький тест призван показать влияние этого события.

Сразу отмечу, что выполнять коммит нужно ровно тогда, когда этого требует бизнес-процесс, либо имеется любая другая веская причина, а не просто “на всякий случай”.

Тестовый стенд:

Oracle 19.23

OS: OEL 8.5 4.18.0-513.24.1.el8_9.x86_64

RAM: 128GB

CPU: AMD Threadripper 1920x

Диски:

Дисковая группа для датафайлов: +ORADATA: SATA SSD GeIL R3 512GB

Дисковая группа для редологов: +ADATAD1: XPG GAMMIX S11 Pro PCI E 3.0 х4

Дисковая группа для архивных логов: +ARCHLOG: SATA SSD GeIL R3 512GB

+ORADATA и +ARCHLOG расположены на одном физическом диске SATA SSD GeIL R3 512GB.

Код, который будет выступать в виде нагрузки (бурной деятельности бизнес-процессов):

Вариант номер 1:

declare
v_loop number;
v_seq number;
v_text varchar2(3000);
v_rtext varchar2(3000);
begin
  execute immediate 'truncate table t1';
  dbms_output.put_line('Start: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
  dbms_workload_repository.create_snapshot;
  v_loop:=1;
  select dbms_random.string(opt => 'X',len => 3000) into v_rtext from dual;
  while v_loop<5000001
    loop
      v_seq:=t1_seq.nextval;
      insert into t1(id,text) values(v_seq,v_rtext);
      commit write wait;
      select t1.text into v_text from t1 where id=v_seq;
      update t1 set t1.text='update' where id=v_seq;
      commit write wait;
      delete from t1 where id=v_seq;
      commit write wait;
      v_loop:=v_loop+1;
      end loop;
      dbms_workload_repository.create_snapshot;
      dbms_output.put_line('End: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
   end;

Вариант номер 2:

declare
v_loop number;
v_seq number;
v_text varchar2(3000);
v_rtext varchar2(3000);
begin
  execute immediate 'truncate table t1';
  dbms_output.put_line('Start: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
  dbms_workload_repository.create_snapshot;
  v_loop:=1;
  select dbms_random.string(opt => 'X',len => 3000) into v_rtext from dual;
  while v_loop<5000001
    loop
      v_seq:=t1_seq.nextval;
      insert into t1(id,text) values(v_seq,v_rtext);
      select t1.text into v_text from t1 where id=v_seq;
      update t1 set t1.text='update' where id=v_seq;
      delete from t1 where id=v_seq;
      commit write wait;
      v_loop:=v_loop+1;
      end loop;
      dbms_workload_repository.create_snapshot;
      dbms_output.put_line('End: '||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
   end;

Отличие первого и второго варианта в том, что первый вариант имеет 3 коммита в цикле, а второй 1 коммит в цикле. Также, конструкция commit write wait выбрана для того, чтобы избежать оптимизации PL/SQL, т.к. данный вариант коммита применяется если код написан не на PL/SQL.

Вариант 1, выполнился за 23 минуты и 6 секунд(1386 секунд)

Вариант 2, выполнился за 12 минут и 51 секунду(771 секунд).

В не зачёта: Вариант 3(коммит выполняется только по окончанию цикла), выполнился за 503 секунды.

Т.е. вариант 2 быстрее почти в 2 раза.

На что вариант 1 потратил время(почему он медленнее):

Далее расчеты делаются с определённой погрешностью и предполагается, что все статистики относятся только к выполняемым задачам.

Вариант 1 из 1386 секунд, потратил на log file sync(lfs) 501 сек, а вариант 2 из 771 секунд, потратил на log file sync(lfs) 192 сек. Отняв от 1386, 501, получим 885 сек, т.е. математически исключим все промежуточные коммиты в цикле из варианта 1, всё равно вариант 1 затрачивает времени больше, чем вариант 2, 885сек против 771сек.

График количества выполнений SQL выражений в секунду для “вариант 1”,“вариант 2” и “вариант 3”: