토요일 밤 2022년 주문을 정리한 뒤로 등급별 매출 화면이 어떤 때는 0.3초, 어떤 때는 4초다

중급DDL한빛유통466 / 700

상황

운영 DBA가 지난 토요일 밤 T_ORD_BIG에서 2022년 주문(3분의 1)을 정리했다. DELETE 대신 2023년 이후만 새 테이블로 옮기고 이름을 바꿔 끼웠고, 빨리 끝내려고 테이블과 인덱스를 모두 PARALLEL 8 NOLOGGING으로 만들었다. 작업은 4분 만에 끝났다. 월요일 오전부터 등급별 매출 화면(시간당 약 200번 호출)의 응답 시간이 들쭉날쭉하고 DB 서버 CPU가 90%를 넘나든다. 같은 날 08:10, 리포트용 대기 DB에서는 월간 리포트가 블록 손상 에러로 실패했다.

테이블과 인덱스

T_ORD_BIG(정리 뒤): 666,971행 · 약 3,890블록 · ORD_DT 2023-01-01 ~ 2024-12-31 · 전부 자정
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)(정리 때 새로 만들었다)
M_CUS 고객: 2,000행 · 30블록 · CUS_GD A 1% · B 9% · C 90%
운영은 EE이고 병렬 실행이 켜져 있다(parallel_max_servers 32). 운영 DB는 ARCHIVELOG이고 FORCE_LOGGING은 NO다. 리포트용 대기 DB(Active Data Guard)가 운영의 redo를 받아 따라간다.

원본 SQL

sql

-- 토요일 23:00 · 2022년 주문 정리 (운영 DBA)
CREATE TABLE T_ORD_BIG_N
  PARALLEL 8 NOLOGGING
AS SELECT * FROM T_ORD_BIG WHERE ORD_DT >= DATE '2023-01-01';

CREATE INDEX IX_ORD_BIG_N_DT_AMT ON T_ORD_BIG_N (ORD_DT, ORD_AMT) PARALLEL 8 NOLOGGING;
CREATE INDEX IX_ORD_BIG_N_DT_SEQ ON T_ORD_BIG_N (ORD_DT, ORD_SEQ) PARALLEL 8 NOLOGGING;

ALTER TABLE T_ORD_BIG RENAME TO T_ORD_BIG_OLD;
ALTER TABLE T_ORD_BIG_N RENAME TO T_ORD_BIG;
ALTER INDEX IX_ORD_BIG_DT_AMT RENAME TO IX_ORD_BIG_OLD_DT_AMT;
ALTER INDEX IX_ORD_BIG_DT_SEQ RENAME TO IX_ORD_BIG_OLD_DT_SEQ;
ALTER INDEX IX_ORD_BIG_N_DT_AMT RENAME TO IX_ORD_BIG_DT_AMT;
ALTER INDEX IX_ORD_BIG_N_DT_SEQ RENAME TO IX_ORD_BIG_DT_SEQ;
GRANT SELECT ON T_ORD_BIG TO STLM_APP;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_BIG')

실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3)

xplan

[월요일 10:00 · 등급별 매출 화면 (힌트 없는 같은 SQL) — 계획 모양만]
SELECT c.CUS_GD, SUM(o.ORD_AMT) FROM T_ORD_BIG o JOIN M_CUS c ON c.CUS_ID = o.CUS_ID GROUP BY c.CUS_GD

| Id | Operation                       | Name      |    TQ  | IN-OUT | PQ Distrib |
|  0 | SELECT STATEMENT                |           |        |        |            |
|  1 |  PX COORDINATOR                 |           |        |        |            |
|  2 |   PX SEND QC (RANDOM)           | :TQ10002  |  Q1,02 | P->S   | QC (RAND)  |
|  3 |    HASH GROUP BY                |           |  Q1,02 | PCWP   |            |
|  4 |     PX RECEIVE                  |           |  Q1,02 | PCWP   |            |
|  5 |      PX SEND HASH               | :TQ10001  |  Q1,01 | P->P   | HASH       |
|  6 |       HASH GROUP BY             |           |  Q1,01 | PCWP   |            |
|* 7 |        HASH JOIN                |           |  Q1,01 | PCWP   |            |
|  8 |         PX RECEIVE              |           |  Q1,01 | PCWP   |            |
|  9 |          PX SEND BROADCAST      | :TQ10000  |  Q1,00 | S->P   | BROADCAST  |
| 10 |           PX SELECTOR           |           |  Q1,00 | SCWC   |            |
| 11 |            TABLE ACCESS FULL    | M_CUS     |  Q1,00 | SCWP   |            |
| 12 |         PX BLOCK ITERATOR       |           |  Q1,01 | PCWC   |            |
| 13 |          TABLE ACCESS FULL      | T_ORD_BIG |  Q1,01 | PCWP   |            |
Note
-----
   - Degree of Parallelism is 8 because of table property

[같은 화면 · 지난주와 이번 주]
                        지난주 금요일             이번 주 월요일
  경과 (화면 한 번)      0.31 초                  0.4 ~ 4.1 초
  계획                   직렬 HASH JOIN · 5,868   위 PX 계획 (DOP 8)

[V$SYSSTAT · 월요일 09:00 ~ 11:00]
  queries parallelized                          2,388
  Parallel operations downgraded to serial        412
  Parallel operations downgraded 75 to 99 pct     131

[딕셔너리 — 운영]
  USER_TABLES   T_ORD_BIG          DEGREE 8 · LOGGING NO
  USER_INDEXES  IX_ORD_BIG_DT_AMT  DEGREE 8 · LOGGING NO
                IX_ORD_BIG_DT_SEQ  DEGREE 8 · LOGGING NO

[리포트용 대기 DB · 월요일 08:10 월간 리포트]
  ORA-01578: ORACLE data block corrupted (file # 7, block # 29441)
  ORA-26040: Data block was loaded using the NOLOGGING option

문제

힌트도 없는 화면 SQL이 병렬로 돌게 된 이유와, 응답 시간이 들쭉날쭉한 이유를 위 숫자로 설명하시오.
대기 DB의 리포트가 실패한 이유를 설명하시오.
두 문제를 바로잡는 SQL을 작성하고, 다음에 같은 정리를 할 때 스크립트에 무엇을 넣어야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 병렬 차수를 되돌린다 — CTAS · CREATE INDEX 의 PARALLEL 8 은 그 작업에만 쓰이고 끝나는 게 아니라 객체 속성으로 남는다
ALTER TABLE T_ORD_BIG NOPARALLEL;
ALTER INDEX IX_ORD_BIG_DT_AMT NOPARALLEL;
ALTER INDEX IX_ORD_BIG_DT_SEQ NOPARALLEL;

-- ② 로깅을 되돌린다 — 앞으로의 직접 경로 작업이 redo 를 남기게
ALTER TABLE T_ORD_BIG LOGGING;
ALTER INDEX IX_ORD_BIG_DT_AMT LOGGING;
ALTER INDEX IX_ORD_BIG_DT_SEQ LOGGING;

-- ③ 이미 NOLOGGING 으로 쓴 블록
--    운영: 지금 그 테이블스페이스를 백업한다          RMAN> BACKUP TABLESPACE USERS;
--    대기 DB: 그 파일을 운영에서 다시 받는다          RMAN> RESTORE DATAFILE 7 FROM SERVICE … (등)
-- ④ 재발 방지: 대기 DB가 있는 운영은 NOLOGGING 이 무시되게
ALTER DATABASE FORCE LOGGING;

-- 다음 정리 스크립트의 끝에 넣을 확인
SELECT table_name, degree, logging FROM user_tables  WHERE table_name = 'T_ORD_BIG';
SELECT index_name, degree, logging FROM user_indexes WHERE table_name = 'T_ORD_BIG';

개선 후 실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3)

xplan

[① 뒤 · 같은 화면]
-------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |      3 |00:00:00.21 |    3926 |
|   1 |  HASH GROUP BY      |           |      1 |      3 |      3 |00:00:00.21 |    3926 |
|*  2 |   HASH JOIN         |           |      1 |    667K|    667K|00:00:00.17 |    3926 |
|   3 |    TABLE ACCESS FULL| M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |
|   4 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    667K|    667K|00:00:00.06 |    3896 |
-------------------------------------------------------------------------------------
   2 - access("C"."CUS_ID"="O"."CUS_ID")

  화면 한 번 0.21 초로 일정 · queries parallelized 0 · CPU 평소 수준
  (지난주 5,868 → 3,926: 2022년 3분의 1을 정리한 몫)

모범 답안 서술

화면 SQL에는 힌트가 없지만 Note가 'Degree of Parallelism is 8 because of table property'다. CTAS와 CREATE INDEX에 적은 PARALLEL 8은 그 작업을 8개로 나눠 하라는 뜻에 그치지 않고, 테이블과 인덱스의 속성(DEGREE 8)으로 딕셔너리에 남는다. 그래서 정리 뒤로는 이 테이블을 읽는 모든 SQL을 옵티마이저가 DOP 8로 계획한다. 토요일 작업의 병렬 설정이 월요일 화면까지 따라온 것이다. 응답 시간이 들쭉날쭉한 것은 PX 서버 때문이다. 화면 한 번이 PX 서버를 최대 16개(생산자와 소비자 두 벌) 쓰는데, 시간당 약 200번 호출되는 화면 여러 개가 동시에 돌면 parallel_max_servers 32를 금방 다 쓴다. 서버를 얻은 호출은 병렬로, 못 얻은 호출은 강등되어 직렬로 돈다(downgraded to serial 412번, 75 ~ 99% 강등 131번). 병렬 실행을 준비하고 서버끼리 행을 주고받는 비용까지 더해져 0.4초에서 4.1초까지 흔들리고 CPU가 90%를 넘나든다. 대기 DB 리포트가 실패한 것은 NOLOGGING 때문이다. 운영이 FORCE LOGGING이 아니어서 NOLOGGING이 그대로 적용됐고, CTAS와 인덱스 생성이 쓴 블록은 내용 없이 '여기는 NOLOGGING으로 썼다'는 표시만 redo에 남았다. 대기 DB는 그 redo를 받아 따라가므로 그 블록을 채울 수 없고, 읽는 순간 ORA-01578과 ORA-26040이 난다. 운영도 다음 백업 전에 그 파일이 망가지면 그 블록은 복구할 수 없다. 바로잡는 SQL은 셋이다. ALTER TABLE/INDEX … NOPARALLEL로 병렬 차수를 1로 되돌리면 화면은 직렬 HASH JOIN으로 돌아가 0.21초, Buffers 3,926으로 일정해진다(정리 덕분에 지난주 5,868보다 적다). LOGGING도 되돌린다. 이미 NOLOGGING으로 쓴 블록은 속성을 바꿔도 되살아나지 않으므로, 운영은 그 테이블스페이스를 지금 백업하고 대기 DB는 해당 파일을 운영에서 다시 받는다. 대기 DB가 있는 운영이라면 ALTER DATABASE FORCE LOGGING으로 NOLOGGING이 아예 무시되게 해 둔다. 다음 정리 스크립트에는 작업이 끝나자마자 NOPARALLEL, LOGGING으로 되돌리는 줄과, USER_TABLES와 USER_INDEXES의 DEGREE와 LOGGING을 확인하는 줄을 넣는다. NOLOGGING을 쓸 수밖에 없다면 작업 직후 백업을 순서에 넣고 대기 DB 담당과 미리 협의한다.

해설

함정: 이름을 바꿔 끼울 때 권한, 통계, 제약은 챙겼지만, 원래 테이블의 물리 속성(병렬 차수, 로깅, 압축, PCTFREE)도 '따라오지 않는 것'이다. 새 테이블은 CTAS 문장에 적은 속성을 가진다. DBMS_METADATA.GET_DDL로 원본과 새 테이블의 DDL을 나란히 비교해 본다.
작업에만 병렬을 쓰고 싶다면 CTAS의 SELECT에 /*+ PARALLEL(8) */ 힌트를 주고 만든 뒤 바로 NOPARALLEL로 되돌리는 줄을 둔다. 인덱스도 CREATE INDEX … PARALLEL 8 뒤에 ALTER INDEX … NOPARALLEL을 붙인다.
지울 행이 3분의 1이면 CTAS가 DELETE보다 늘 크게 유리하지는 않다. 다만 DELETE는 고수위선을 내리지 않고 인덱스 두 개의 키를 행마다 지우므로, 작업 창이 있다면 옮겨 담는 쪽이 정리 뒤 테이블도 작다(교재 05화).
NOLOGGING 블록은 V$DATAFILE.UNRECOVERABLE_TIME과 RMAN REPORT UNRECOVERABLE로 찾는다. 대기 DB가 없더라도 NOLOGGING 작업 직후에는 백업한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 병렬 계획은 EE 모양(교재 14화 §9.3) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황