보관본 적재에 NOLOGGING까지 붙였는데 운영 redo가 47.9MB 그대로다

기초INSERT한빛유통458 / 700

상황

야간 체인 ①(T_ORD_BIG 재적재) 앞에는 「재적재 전 보관본」 단계가 있다. 재적재가 잘못되면 되돌릴 수 있도록 지금의 T_ORD_BIG을 T_ORD_BIG_BAK에 통째로 떠 둔다. 연습 DB에서 /*+ APPEND */를 붙이자 redo가 0.2MB로 줄었는데, 운영에 반영하니 47.9MB가 그대로 나왔다. 오과장: "그래서 NOLOGGING도 붙였어요. 그런데 숫자가 하나도 안 바뀌어요." 운영 DBA는 일요일 밤 아카이브 로그가 평일의 세 배라며 줄여 달라고 한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록(약 46MB)
T_ORD_BIG_BAK 보관본(문제 설정, T_ORD_BIG과 같은 컬럼): 매주 비우고 다시 채운다. 인덱스, 제약, 트리거는 없고, 재적재가 실패했을 때만 읽는다.
운영 DB는 ARCHIVELOG이고 FORCE_LOGGING은 NO다. 연습 DB는 NOARCHIVELOG다. 운영 백업은 매일 06:00 RMAN 증분 백업이다.

원본 SQL

sql

-- 일요일 21:50 · 재적재 전 보관본 (오과장 · 지난주 수정)
TRUNCATE TABLE T_ORD_BIG_BAK;

INSERT /*+ APPEND */ INTO T_ORD_BIG_BAK NOLOGGING
SELECT * FROM T_ORD_BIG;
COMMIT;

실행계획 학습용 예시

xplan

[실행계획 — 운영]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |               |      1 |        |      0 |00:00:02.84 |    6140 |
|   1 |  LOAD AS SELECT                  | T_ORD_BIG_BAK |      1 |        |      0 |00:00:02.84 |    6140 |
|   2 |   OPTIMIZER STATISTICS GATHERING |               |      1 |   1000K|   1000K|00:00:00.41 |    5826 |
|   3 |    TABLE ACCESS FULL             | T_ORD_BIG     |      1 |   1000K|   1000K|00:00:00.18 |    5826 |
------------------------------------------------------------------------------------------------------------

[세션 통계 — 같은 문장 · 두 DB]
                            연습 DB (NOARCHIVELOG)    운영 (ARCHIVELOG)
  redo size                       0.2 MB                47.9 MB
  undo change vector size         0.04 MB               0.04 MB
  physical writes direct          5,872                 5,872
  경과                            0.3 초                 2.8 초

[운영 딕셔너리]
  V$DATABASE    LOG_MODE = ARCHIVELOG · FORCE_LOGGING = NO
  USER_TABLES   T_ORD_BIG_BAK · LOGGING = YES

문제

직접 경로로 적재됐는데도 운영 redo가 47.9MB인 이유와, 붙인 NOLOGGING이 아무 효과가 없는 이유를 설명하시오.
보관본 단계의 redo를 줄이도록 고치고, 그 대가와 미리 확인할 것을 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 한 번만: NOLOGGING 은 테이블 속성이다 (INSERT 문 안의 NOLOGGING 은 테이블 별칭으로 읽힌다)
ALTER TABLE T_ORD_BIG_BAK NOLOGGING;

-- 일요일 21:50
TRUNCATE TABLE T_ORD_BIG_BAK;
INSERT /*+ APPEND */ INTO T_ORD_BIG_BAK
SELECT * FROM T_ORD_BIG;
COMMIT;

-- 미리 확인 : SELECT log_mode, force_logging FROM v$database;   (FORCE_LOGGING = YES 면 NOLOGGING 은 무시된다)
-- 적재 뒤   : RMAN> REPORT UNRECOVERABLE;  → 06:00 증분 백업이 그 블록을 담는지 본다

개선 후 실행계획 학습용 예시

xplan

[세션 통계 — 운영 · 같은 100만 행]
  redo size                 47.9 MB → 약 0.3 MB
  undo change vector size   0.04 MB
  경과                      2.8 초 → 약 1 초

[적재 직후]
  USER_TABLES                   T_ORD_BIG_BAK · LOGGING = NO
  RMAN> REPORT UNRECOVERABLE    T_ORD_BIG_BAK 이 든 데이터 파일 하나 (06:00 증분 백업 뒤에 사라진다)

모범 답안 서술

원본은 직접 경로로 돌았다. 실행계획 1번 줄이 LOAD AS SELECT이고 undo가 0.04MB뿐이다. 그런데 redo가 연습 DB의 0.2MB와 달리 운영에서 47.9MB인 것은 로그 모드 차이 때문이다. NOARCHIVELOG에서는 직접 경로 적재가 데이터 redo를 거의 만들지 않지만, ARCHIVELOG에서는 LOGGING 테이블에 직접 경로로 쓴 블록을 통째로 redo에 남긴다. 47.9MB는 새로 쓴 5,872블록(약 46MB)과 거의 같은 크기다. INSERT … INTO T_ORD_BIG_BAK NOLOGGING SELECT의 NOLOGGING은 아무 일도 하지 않는다. INSERT 문에서 테이블 이름 바로 뒤에 오는 단어는 테이블 별칭으로 읽히므로, 이 문장은 T_ORD_BIG_BAK에 NOLOGGING이라는 별칭을 붙였을 뿐이다. 에러가 나지 않아서 붙인 사람은 효과가 있다고 믿게 된다. NOLOGGING은 테이블, 인덱스, 파티션의 속성이므로 ALTER TABLE T_ORD_BIG_BAK NOLOGGING으로 한 번 바꿔 둔다. 그러면 직접 경로 적재의 데이터 redo가 47.9MB → 약 0.3MB로 준다. 대가는 복구다. NOLOGGING으로 쓴 블록은 redo가 없으므로 다음 백업 전에 디스크가 망가지면 미디어 복구로 살릴 수 없다(읽으면 ORA-01578, ORA-26040). 보관본은 원본에서 언제든 다시 뜰 수 있고 06:00 증분 백업이 그 블록을 담으므로 감수할 수 있다. 적재 뒤 RMAN REPORT UNRECOVERABLE로 대상 파일을 확인해 둔다. 운영이 Data Guard 때문에 FORCE LOGGING이라면 NOLOGGING 속성은 무시되므로 redo는 줄지 않는다. 미리 V$DATABASE.FORCE_LOGGING을 보고, 그때는 보관본을 뜨는 방식 자체를 바꿔야 한다.

해설

함정 둘: /*+ APPEND NOLOGGING */처럼 힌트 안에 넣어도 NOLOGGING이라는 힌트가 없으므로 무시된다. 반대로 CTAS의 CREATE TABLE … NOLOGGING AS SELECT는 테이블 속성이라 효과가 있다. 같은 단어라도 문장마다 뜻이 다르다.
NOLOGGING은 직접 경로 연산(APPEND, CTAS, 인덱스 생성과 REBUILD, MOVE)에만 효과가 있다. 일반 INSERT, UPDATE, DELETE는 NOLOGGING 테이블에서도 redo를 다 남긴다.
연습 DB의 0.2MB를 운영의 기대값으로 옮기면 안 된다. 교재 14화 §5가 로그 모드별 차이를 표로 정리했고, 측정 기록에 로그 모드를 적는 이유가 여기에 있다.
다른 방법: 보관본을 따로 뜨지 않고, 재적재를 새 테이블에 한 뒤 이름을 바꿔 끼우면 옛 테이블이 그대로 보관본이 된다. FORCE LOGGING이라 NOLOGGING을 쓸 수 없을 때 생각할 수 있다. 대신 권한, 인덱스, 통계를 새 테이블에 다시 챙겨야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황