commit_wait, commit_logging 설정이 성능에 미치는 영향

비록 아주 작은 환경 (1 OCPU, 16 GB Memory, Oracle 21c EE)이지만, commit_wait, commit_logging 데이터베이스 파라미터 설정 변경에 따라 어느정도 성능에 영향이 있는지 간단한 테스트를 해 보았습니다.

아래 테스트 결과는 테스트 서버의 자원, 테스트한 어플리케이션에 따라 다를 수 있는 내용입니다. 대략적인 현상을 확인하는 차원에서 수행한 테스트 결과입니다.

 

아래 오라클 문서를 참조했습니다.

21c Database Reference

 

COMMIT_WAIT

• 커밋을 했을 때 리두를 언제 리두 로그에 flush 할 지를 제어하는 파라미터
• NOWAIT: 커밋 시 로그 버퍼의 내용이 로그 파일에 기록될 때까지 기다리지 않고, 다음 작업 처리를 진행. 데이터베이스가 비정상 종료 시 트랜잭션 ACID 중 durability를 보장받을 수 없음
• WAIT: 커밋 시 로그 버퍼의 내용이 로그 파일에 기록될 때까지 대기
• FORCE_WAIT: 커밋 시 로그 버퍼의 내용이 로그 파일에 기록될 때까지 대기. 단 시스템 레벨에서 설정한 경우, 세션 및 트랜잭션 레벨 설정은 무시되고, 세션레벨에서 설정한 경우 트랜잭션 레벨 설정은 무시됨.

 

COMMIT_LOGGING

• 커밋을 했을 때 Log Writer가 리두를 어떻게 묶어서 처리할 지를 제어하는 파라미터
• IMMEDIATE: 커밋 마다 로그 버퍼의 내용을 로그 파일에 기록
• BATCH: 커밋 시 일정량을 담아뒀다가 로그 파일에 한꺼번에 기록

 

세션에서 commit_wait, commit_logging을 설정하고, 100 만건을 Insert ... commit, Update ... commit 하는 PL/SQL 프로시저를 수행했을 때 결과입니다. commit_wait='NOWAIT', commit_logging='BATCH'로 설정했을 때 디폴트 설정 대비, 대략 25% 전후 응답시간의 차이를 보이고 있습니다. 아래 표 숫자의 단위는 초입니다.

 

테스트에 사용된 PL/SQL 프로시저입니다.

CREATE OR REPLACE PROCEDURE demo.demoproc
IS
   var_sysdate DATE;
   var_elapsed_time NUMBER :=0;
BEGIN
   var_sysdate := SYSDATE;
   FOR I IN 1..1000000
   LOOP
      insert into demo.demotab values (I, 'this is the test for the database parameter that is related to commit','this is the test for the database parameter that is related to commit','this is the test for the database parameter that is related to commit','this is the test for the database parameter that is related to commit');
      commit;
   END LOOP;
   var_elapsed_time := (SYSDATE - var_sysdate) * 60 * 60 * 24;
   DBMS_OUTPUT.PUT_LINE('########################################################');
   DBMS_OUTPUT.PUT_LINE('Elapsed Time : ' || round(var_elapsed_time,0) );
   DBMS_OUTPUT.PUT_LINE('########################################################');
END;
/

CREATE OR REPLACE PROCEDURE demo.demoprocupt
IS
   var_sysdate DATE;
   var_elapsed_time NUMBER :=0;
BEGIN
   var_sysdate := SYSDATE;
   FOR I IN 1..1000000
   LOOP
      update demo.demotab
	  set col2 = 'this is the test for the database parameter that is related to commit',
	      col3 = 'this is the test for the database parameter that is related to commit',
          col4 = 'this is the test for the database parameter that is related to commit',
          col5 = 'this is the test for the database parameter that is related to commit'		  
	  where col1 = I;
      commit;
   END LOOP;
   var_elapsed_time := (SYSDATE - var_sysdate) * 60 * 60 * 24;
   DBMS_OUTPUT.PUT_LINE('########################################################');
   DBMS_OUTPUT.PUT_LINE('Elapsed Time : ' || round(var_elapsed_time,0) );
   DBMS_OUTPUT.PUT_LINE('########################################################');
END;
/

 

끝