COMMIT과 리두 대기

log file sync와 커밋 단위, log buffer space 및 checkpoint incomplete 구분과 RAC 리두 크기 실습

T_ORDER_MIGRuser commitslog file syncLGWRRedo Log Bufferlog buffer spacelog file switch (checkpoint incomplete)log file parallel write배치 COMMIT로그 스위치 빈도리두 로그 크기

#핵심 요약

log file sync는 COMMIT한 서버 프로세스가 LGWR가 그 트랜잭션의 리두를 Online Redo Log에 다 쓸 때까지 기다리는 대기임
기록이 끝나야 커밋 완료가 반환됨

행마다 COMMIT하면 그만큼 log file sync가 반복됨
업무상 가능한 N건 단위로 COMMIT하면 대기 횟수가 줌

비슷해 보이는 로그 대기를 구분함

대기 이벤트	발생 조건	점검
`log file sync`	COMMIT의 리두 기록 완료 대기	COMMIT 단위(`user commits` 과임), LGWR I/O(`log file parallel write` 지연)
`log buffer space`	로그 버퍼 공간 부족(작은 버퍼, LGWR 지연)	버퍼 크기, LGWR 상태
`log file switch (checkpoint incomplete)`	작은 리두 파일로 스위치가 잦아 재사용할 그룹의 체크포인트가 덜 끝남	스위치 빈도, 리두 파일 크기

로그 버퍼를 키운다고 log file sync가 준다는 보장은 없음
실습에서는 최대 부하 시간대 로그 스위치를 시간당 3~5회로 맞추는 것을 기준으로 적었음

#개념과 동작 원리

COMMIT 처리 단계는 DML 비용과 Direct Path에 있음

구성 요소	설명
Redo Log Buffer	변경 이력을 먼저 저장하는 SGA 메모리 영역
LGWR	로그 버퍼의 리두를 Online Redo Log 파일에 기록 수업 자료가 든 동작 조건: COMMIT, 버퍼 1/3 사용, 3초 경과, DBWR 쓰기 이전 등
RAC	인스턴스마다 리두 thread와 LGWR를 따로 씀

리두 로그 그룹 관리 일반은 리두 로그 장애 처리와 함께 봄

#필수 명령어 또는 SQL

#행별 COMMIT과 배치 COMMIT 비교

SQL*Plus (실습 계정)객체 또는 설정을 변경함

CREATE TABLE t_commit (id NUMBER, memo VARCHAR2(50));

BEGIN
  FOR i IN 1.. 50000 LOOP
    INSERT INTO t_commit VALUES (i, 'row by row');
    -- 5만 번
    COMMIT;
  END LOOP;
END;
/

SQL*Plus (실습 계정)조회만 함

SELECT event, total_waits, ROUND(time_waited_micro / 1000) AS ms
  FROM v$session_event
 WHERE sid = SYS_CONTEXT('userenv', 'sid')
   AND event = 'log file sync';

결과 해석:

새 세션에 접속해 세션 통계를 초기화한 뒤 1000건마다 COMMIT하는 블록(총 50번)을 실행해 Elapsed와 log file sync 대기 횟수, 시간을 비교함
수업 자료에 두 경우의 측정 수치는 기재 없음
실습에서는 PL/SQL에는 COMMIT 최적화가 있어 차이가 작게 보일 수 있고, JDBC 같은 클라이언트의 행별 COMMIT에서는 차이가 더 클 수 있다고 적었음

#작은 로그 버퍼와 작은 리두 파일로 경합 재현

SQL*Plus (SYSDBA)객체 또는 설정을 변경함

-- 기존값 7136K의 1/4
ALTER SYSTEM SET log_buffer = 1784K SCOPE = SPFILE SID = '*';

OS (oracle 사용자, 1번 노드)인스턴스 또는 서비스 중단이 필요함

srvctl stop database -db <DB_NAME>
srvctl start database -db <DB_NAME>

SQL*Plus (SYSDBA)객체 또는 설정을 변경함

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 21 ('+DATA') SIZE 5M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 22 ('+DATA') SIZE 5M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 23 ('+DATA') SIZE 5M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 24 ('+DATA') SIZE 5M;
-- 여러 번 반복
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT GLOBAL;

SQL*Plus (SYSDBA)데이터 또는 파일을 삭제함, 되돌릴 수 없음

-- 기존 그룹 중 INACTIVE인 것
ALTER DATABASE DROP LOGFILE GROUP <GROUP#>;

결과 해석: 스레드마다 로그 그룹을 최소 2개 유지해야 하고, CURRENT, ACTIVE 그룹은 지울 수 없음
실습에서는 이 상태에서 OWI log_buffer_space 시나리오로 부하를 주고 GV$SESSION_WAIT에서 log buffer space, log file switch (checkpoint incomplete) 등을 확인했음

SQL*Plus (SYSDBA)조회만 함

SELECT inst_id, event, COUNT(*)
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND event LIKE 'log%'
 GROUP BY inst_id, event;

결과 해석: 개별 실행 출력은 기재 없음

#스위치 빈도로 크기 정하기와 복원

SQL*Plus (SYSDBA)조회만 함

SELECT thread#, TO_CHAR(first_time, 'yyyy-mm-dd hh24') AS hour, COUNT(*) AS switch_count
  FROM v$log_history
 WHERE first_time >= SYSDATE - 1
 GROUP BY thread#, TO_CHAR(first_time, 'yyyy-mm-dd hh24')
 ORDER BY hour, thread#;

결과 해석:

실습에서는 100MB 그룹을 스레드당 2개 추가하고 5MB 그룹을 지운 뒤 ALTER SYSTEM RESET log_buffer SCOPE = SPFILE SID = '*'로 로그 버퍼를 자동 계산 기본값으로 돌리고 DB를 재기동했음
100MB 두 개는 VM 공간 제약에 맞춘 실습 구성임

#확인 문제: 이관 프로그램이 느린 것이 디스크 탓인지 가리기

상황: 구 시스템 주문 데이터를 옮기는 프로그램을 두 노드에서 동시에 돌리는데 느리고, 스토리지 팀은 디스크가 느리다는 증거가 없다고 함

객체 준비는 한 창에서 한 번만 실행함
생성이 끝난 뒤 두 노드에서 적재 블록만 동시에 실행함

SQL*Plus (실습 계정, 한 창에서 한 번)객체 또는 설정을 변경함

CREATE TABLE <SCHEMA>.t_order_migr (order_id NUMBER, inst VARCHAR2(10), memo VARCHAR2(50));

SQL*Plus (실습 계정, 두 노드에서 동시 적재)객체 또는 설정을 변경함

SET TIMING ON
BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO <SCHEMA>.t_order_migr VALUES (i, sys_context('userenv','instance_name'), 'migr');
    COMMIT;
  END LOOP;
END;
/

SQL*Plus (SYSDBA, 1번 노드)조회만 함

SELECT inst_id, event, COUNT(*)
  FROM gv$session
 WHERE state = 'WAITING' AND username = '<SCHEMA>'
   AND wait_class <> 'Idle'
 GROUP BY inst_id, event;

SELECT SYSTIMESTAMP AS sampled_at, inst_id, value
  FROM gv$sysstat WHERE name = 'user commits';

SELECT SYSTIMESTAMP AS sampled_at, inst_id, event, total_waits, time_waited_micro
  FROM gv$system_event
 WHERE event IN ('log file sync', 'log file parallel write');

결과 해석:

두 노드의 적재, 트랜잭션이 모두 끝났는지 확인한 뒤 한 세션에서만 초기화함
초기화 완료 후 아래 배치 적재 블록만 두 노드에서 동시에 실행함

SQL*Plus (실습 계정, 한 세션에서 한 번)데이터 또는 파일을 삭제함, 되돌릴 수 없음

TRUNCATE TABLE <SCHEMA>.t_order_migr;

SQL*Plus (실습 계정, 두 노드에서 동시 적재)객체 또는 설정을 변경함

SET TIMING ON
BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO <SCHEMA>.t_order_migr VALUES (i, sys_context('userenv','instance_name'), 'migr');
    IF mod(i, 1000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

결과 해석:

판단 항목	기준
측정 구간	행별, 배치 COMMIT 각각에서 부하 직전과 두 노드의 부하 완료 직후에 위 누적 통계를 수집함 같은 인스턴스, 이벤트의 전후 차이만 비교하고 구간 중 재기동된 경우 다시 측정함
구간 평균	`Δtime_waited_micro / NULLIF(Δtotal_waits, 0) / 1000`으로 ms를 계산함 대기 횟수 차이가 0이면 평균을 계산하지 않음
커밋 빈도	`Δuser commits / 구간 경과 초`와 적재 행당 커밋 수를 비교함 인스턴스 통계에는 다른 업무도 포함되므로 실습 부하와 다른 업무를 구분함
원인 판단	`log file sync`에는 리두 쓰기와 완료 통지가 포함됨 `log file parallel write`와의 차이만으로 커밋 횟수 문제를 확정할 수 없으며 같은 구간의 CPU 사용률, LGWR 실행과 스케줄링 지연, I/O를 함께 확인함
배치 단위	실패 시 재처리 범위 같은 업무 요구와 Undo 사용량을 함께 고려

예상값: 대기 log file sync, user commits가 이관 건수만큼 늘어남

실습이 끝나면 DROP TABLE <SCHEMA>.t_order_migr PURGE로 지움

실행 결과 확인 불가