PARALLEL 8을 붙인 월간 스냅샷 적재가 3분 18초에서 줄지 않는다

실전INSERT버거468 / 700

상황

매월 1일 01:00 본사 DW 이관 배치는 운영 ORD_ITEM 전체를 스냅샷 테이블 DW_ORD_ITEM_SNAP에 다시 담는다. 다음 단계(DW 전송)가 01:30에 시작하는데 이 단계가 3분 18초 걸려, 데이터가 늘면 겹칠 것이라는 걱정이 나왔다. 개발팀이 PARALLEL 8 힌트를 붙였지만 시간은 그대로였다. 운영은 EE이고 병렬 실행이 켜져 있다(parallel_max_servers 32). 01:00 ~ 01:30에 이 서버에서 도는 다른 배치는 매장 마감 집계 하나이고, 그 배치도 DOP 8로 돈다.

테이블과 인덱스

ORD_ITEM: 2,472,717행 · 55,265블록(약 432MB) · 01:00 ~ 01:30에 이 테이블을 고치는 세션은 없다.
ITEM 상품: 92행 · 5블록
DW_ORD_ITEM_SNAP 스냅샷(문제 설정): ORD_ITEM 컬럼에 ITEM_NM과 ITEM_TYPE_CD를 붙인 사본 · 인덱스, 제약, 트리거 없음 · 매달 비우고 다시 채운다 · DW 전송 단계만 읽는다.
운영 DB는 ARCHIVELOG이고 DW_ORD_ITEM_SNAP은 NOLOGGING이다(DW로 넘긴 뒤에는 다시 만들 수 있는 사본이라 승인받았다). FORCE LOGGING은 NO다.

원본 SQL

sql

-- 매월 1일 01:00 · DW 이관용 스냅샷
TRUNCATE TABLE DW_ORD_ITEM_SNAP;

INSERT /*+ APPEND PARALLEL(t 8) */ INTO DW_ORD_ITEM_SNAP t
       (ORD_NO, ITEM_ID, ITEM_NM, ITEM_TYPE_CD, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT /*+ PARALLEL(i 8) */
       i.ORD_NO, i.ITEM_ID, m.ITEM_NM, m.ITEM_TYPE_CD, i.ORD_ITEM_QTY, i.ORD_DT, i.ORD_HMS
  FROM ORD_ITEM i, ITEM m
 WHERE m.ITEM_ID = i.ITEM_ID;
COMMIT;

SELECT COUNT(*) FROM DW_ORD_ITEM_SNAP;        -- 건수 확인 (DW 전송 단계에 넘긴다)

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

xplan

[실행계획 — 지금 (모양만)]
| Id | Operation                         | Name             |    TQ  | IN-OUT | PQ Distrib |
|  0 | INSERT STATEMENT                  |                  |        |        |            |
|  1 |  LOAD AS SELECT                   | DW_ORD_ITEM_SNAP |        |        |            |
|  2 |   OPTIMIZER STATISTICS GATHERING  |                  |        |        |            |
|  3 |    PX COORDINATOR                 |                  |        |        |            |
|  4 |     PX SEND QC (RANDOM)           | :TQ10001         |  Q1,01 | P->S   | QC (RAND)  |
|* 5 |      HASH JOIN                    |                  |  Q1,01 | PCWP   |            |
|  6 |       PX RECEIVE                  |                  |  Q1,01 | PCWP   |            |
|  7 |        PX SEND BROADCAST          | :TQ10000         |  Q1,00 | P->P   | BROADCAST  |
|  8 |         PX BLOCK ITERATOR         |                  |  Q1,00 | PCWC   |            |
|  9 |          TABLE ACCESS FULL        | ITEM             |  Q1,00 | PCWP   |            |
| 10 |       PX BLOCK ITERATOR           |                  |  Q1,01 | PCWC   |            |
| 11 |        TABLE ACCESS FULL          | ORD_ITEM         |  Q1,01 | PCWP   |            |

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("M"."ITEM_ID"="I"."ITEM_ID")

Note
-----
   - Degree of Parallelism is 8 because of hint
   - PDML is disabled in current session

[세션 통계 · V$SESSION — 적재 한 번]
  경과                                   3분 18초
  queries parallelized                   1
  DML statements parallelized            0
  V$SESSION.PDML_STATUS                  DISABLED
  physical writes direct                 약 58,900 블록 (QC 한 세션이 썼다)
  redo size                              약 1.1 MB (NOLOGGING)

[V$PX_SESSION · ASH 표본 — 01:01:30]
  QC SID 88 · PX 서버 16개 (생산자 8 · 소비자 8)
  PX 서버 대기 1위  PX Deq Credit: send blkd (QC 가 받아 가기를 기다린다)

문제

실행계획의 어느 줄을 보면 적재(LOAD AS SELECT)가 직렬로 처리되는지 알 수 있는지 줄 번호로 설명하고, PX 서버 16개가 무엇을 기다렸는지 쓰시오.
적재까지 병렬로 처리되도록 배치를 고치시오.
병렬 DML로 바꿀 때의 부작용과 이 배치에서 미리 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매월 1일 01:00 · DW 이관용 스냅샷
TRUNCATE TABLE DW_ORD_ITEM_SNAP;

-- 병렬 DML 은 세션에서 따로 켠다 (12c 이상은 문장 힌트 ENABLE_PARALLEL_DML 도 된다)
ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL(t 8) */ INTO DW_ORD_ITEM_SNAP t
       (ORD_NO, ITEM_ID, ITEM_NM, ITEM_TYPE_CD, ORD_ITEM_QTY, ORD_DT, ORD_HMS)
SELECT /*+ PARALLEL(i 8) */
       i.ORD_NO, i.ITEM_ID, m.ITEM_NM, m.ITEM_TYPE_CD, i.ORD_ITEM_QTY, i.ORD_DT, i.ORD_HMS
  FROM ORD_ITEM i, ITEM m
 WHERE m.ITEM_ID = i.ITEM_ID;
COMMIT;                                   -- 병렬 DML 뒤에는 커밋해야 그 테이블을 다시 읽을 수 있다 (ORA-12838)

ALTER SESSION DISABLE PARALLEL DML;       -- 같은 세션의 다음 작업에 번지지 않게 되돌린다

SELECT COUNT(*) FROM DW_ORD_ITEM_SNAP;    -- 2,472,717 이어야 한다

-- 이관 로그에 남길 것: V$SYSSTAT 'Parallel operations downgraded%' 의 적재 전후 차이

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

xplan

[실행계획 — 병렬 DML 을 켠 뒤 (모양만)]
| Id | Operation                            | Name             |    TQ  | IN-OUT | PQ Distrib |
|  0 | INSERT STATEMENT                     |                  |        |        |            |
|  1 |  PX COORDINATOR                      |                  |        |        |            |
|  2 |   PX SEND QC (RANDOM)                | :TQ10001         |  Q1,01 | P->S   | QC (RAND)  |
|  3 |    LOAD AS SELECT (HYBRID TSM/HWMB)  | DW_ORD_ITEM_SNAP |  Q1,01 | PCWP   |            |
|  4 |     OPTIMIZER STATISTICS GATHERING   |                  |  Q1,01 | PCWP   |            |
|* 5 |      HASH JOIN                       |                  |  Q1,01 | PCWP   |            |
|  6 |       PX RECEIVE                     |                  |  Q1,01 | PCWP   |            |
|  7 |        PX SEND BROADCAST             | :TQ10000         |  Q1,00 | P->P   | BROADCAST  |
|  8 |         PX BLOCK ITERATOR            |                  |  Q1,00 | PCWC   |            |
|  9 |          TABLE ACCESS FULL           | ITEM             |  Q1,00 | PCWP   |            |
| 10 |       PX BLOCK ITERATOR              |                  |  Q1,01 | PCWC   |            |
| 11 |        TABLE ACCESS FULL             | ORD_ITEM         |  Q1,01 | PCWP   |            |
Note
-----
   - Degree of Parallelism is 8 because of hint

[세션 통계 — 운영 · 같은 2,472,717행]
  경과                          3분 18초 → 약 31초
  DML statements parallelized   0 → 1
  V$SESSION.PDML_STATUS         ENABLED (적재 동안)
  V$PQ_TQSTAT (Q1,01 소비자)    8개가 각각 약 30만 9천 행을 적재
  Parallel operations downgraded   0 (마감 집계와 겹친 날도 0 · 서버 32개를 꽉 채워 썼다)

모범 답안 서술

지금 계획에서 적재는 직렬이다. 1번 줄 LOAD AS SELECT가 3번 줄 PX COORDINATOR보다 위에(바깥에) 있다. PX 서버가 하는 일은 4번 줄 PX SEND QC까지이고, 조인한 247만 행을 QC(SID 88) 한 세션에 모두 보내면 QC가 혼자 블록을 쓴다. Note의 'PDML is disabled in current session'과 DML statements parallelized 0, PDML_STATUS DISABLED가 같은 사실을 말해 준다. PX 서버 16개는 일을 끝내 놓고도 QC가 행을 받아 가기를 기다렸다(PX Deq Credit: send blkd). 그래서 읽기를 8개로 나눠도 3분 18초가 줄지 않았다. 병렬 쿼리는 힌트나 테이블 차수만으로 켜지지만 병렬 DML은 따로 켜야 한다. 적재 앞에 ALTER SESSION ENABLE PARALLEL DML을 넣는다(12c 이상은 문장 힌트 ENABLE_PARALLEL_DML로도 된다). 그러면 LOAD AS SELECT가 PX SEND QC 아래(3번 줄, PCWP)로 내려가 소비자 서버 8개가 각자 블록을 쓰고, QC는 결과만 모은다. 3분 18초가 약 31초로 준다. 끝나면 DISABLE PARALLEL DML로 되돌려 같은 세션의 다음 작업에 번지지 않게 한다. 부작용이 셋 있다. 첫째, 병렬 DML로 고친 테이블은 커밋하기 전에는 같은 트랜잭션에서 다시 읽거나 고칠 수 없다(ORA-12838). 건수 확인 SELECT는 반드시 COMMIT 뒤에 둔다. 둘째, 병렬 DML은 커밋할 때까지 테이블에 배타 잠금(TM 6)을 걸어 다른 세션이 그 테이블을 고칠 수 없다. 이 스냅샷은 이 배치만 쓰므로 괜찮다. 셋째, DOP 8인 이 문장은 생산자와 소비자 두 벌로 PX 서버 16개를 쓴다. 같은 시각 DOP 8로 도는 마감 집계도 16개를 쓰므로 parallel_max_servers 32에 꽉 찬다. 하나라도 더 붙으면 서버를 다 얻지 못해 다운그레이드되고, 그날은 시간이 다시 늘어난다. 적재 전후의 'Parallel operations downgraded' 통계를 이관 로그에 남기고, 겹치는 날이 잦으면 DOP를 4로 낮추거나 시각을 나눈다. 미리 확인할 것도 있다. 대상에 트리거나 자식 쪽 FK가 생기면 병렬 DML과 직접 경로가 모두 꺼지고 에러 없이 직렬 일반 INSERT가 되므로, 적재 뒤 계획에서 LOAD AS SELECT가 PX 아래에 있는지 본다. redo가 1.1MB로 작은 것은 NOLOGGING 덕분이지 병렬 덕분이 아니다. 운영이 ARCHIVELOG라서 LOGGING 테이블이었다면 병렬로 바꿔도 redo 약 450MB는 그대로 남는다.

해설

병렬 직접 경로 적재는 서버마다 고수위선 위에 따로 쓴다. HYBRID TSM/HWMB는 서버들이 쓴 공간을 합쳐 빈 공간을 줄이는 방식이다. 그래도 직렬 적재보다 공간이 조금 더 들 수 있다.
병렬이 늘 답은 아니다. 이 배치는 3분 18초 중 대부분이 QC 혼자 쓰는 시간이라 효과가 컸지만, 01:00에 CPU와 I/O를 다른 배치와 나눠 쓴다. 교재 14화처럼 Free 같은 환경에서는 병렬이 아예 돌지 않고 힌트가 에러 없이 무시되므로, 효과는 운영에서 단계별 시각과 함께 잰다.
다른 방법: 직렬 APPEND를 두고 원천을 ROWID 범위나 날짜로 나눠 여러 세션이 따로 넣는 작업 분할도 있다. 병렬 DML의 배타 잠금 때문에 같은 테이블에 여러 세션이 직접 경로로 넣을 수는 없으므로, 그때는 파티션별로 넣거나 따로 담은 뒤 합친다(배치 프로그램 튜닝에서 다룬다).
커넥션 풀을 쓰는 배치라면 ENABLE PARALLEL DML이 켜진 세션을 다음 작업이 이어 쓸 수 있다. 끝에 되돌리는 줄을 빼먹지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황