주문상품 정제 적재를 작업 8개로 나눴는데 3분 10초가 2분 31초로밖에 줄지 않고 디스크 읽기는 여덟 배가 됐다

기초INSERT버거487 / 700

상황

버거원 분석 DB는 매월 1일 01:00에 주문상품 전체를 DW 전송용 정제 테이블 DW_ORD_ITEM_CLN에 다시 담는다. 한 문장으로 3분 10초가 걸렸고, 01:30에 DW 전송이 시작되므로 데이터가 늘면 겹칠 것이 걱정이었다. 분석 DB는 Standard Edition 2라 병렬 쿼리와 병렬 DML을 쓸 수 없어서, 개발자가 작업 스케줄러로 작업 8개를 띄우고 ORA_HASH(ORD_NO, 7)로 행을 나눠 맡겼다. 이번 달 실행은 2분 31초였다. 개발자: "여덟 개로 나눴으면 30초 남짓이어야 하는데요." 그 시간 대기의 91%가 direct path read였다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록(약 432MB) · ORD_ITEM_PK (ORD_NO, ITEM_ID) · ORD_ITEM_X01 (ORD_DT, ORD_HMS)
DW_ORD_ITEM_CLN(정제 테이블, 이 문제용): 인덱스 · 제약 · 트리거 없음 · 적재 전에 TRUNCATE한다. 01:00 ~ 01:30에는 이 배치만 쓴다.
서버는 CPU 4개다. 공유 스토리지라 여러 세션이 함께 읽어도 합쳐서 초당 약 25MB까지 나온다.
배치 계정에는 CREATE JOB 권한이 있다(작업 스케줄러와 DBMS_PARALLEL_EXECUTE를 쓸 수 있다).

원본 SQL

sql

-- 매월 1일 01:00 · 작업 8개 (k = 0 ~ 7) 가 각자 아래 문장을 실행하고 커밋한다
INSERT INTO DW_ORD_ITEM_CLN (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS, ORD_YM)
SELECT ORD_NO, ITEM_ID, NVL(ORD_ITEM_QTY, 0), ORD_DT, ORD_HMS, SUBSTR(ORD_DT, 1, 6)
  FROM ORD_ITEM
 WHERE ORA_HASH(ORD_NO, 7) = :k;
COMMIT;

실행계획 학습용 예시

xplan

[작업 하나 (k = 0) — DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST')]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                 |      1 |        |      0 |00:02:29.61 |   62414 | 55265 |
|   1 |  LOAD TABLE CONVENTIONAL | DW_ORD_ITEM_CLN |      1 |        |      0 |00:02:29.61 |   62414 | 55265 |
|*  2 |   TABLE ACCESS FULL      | ORD_ITEM        |      1 |  24727 |    309K|00:02:18.20 |   55269 | 55265 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ORA_HASH("ORD_NO",7)=:K)

[작업 8개 — 스케줄러 기록과 세션 통계]
  작업          시작       끝         넣은 행     physical reads
  DW_CLN_0     01:00:02   01:02:31    309,412       55,265
  DW_CLN_1     01:00:02   01:02:33    308,977       55,265
  …            …          …           …             …
  DW_CLN_7     01:00:02   01:02:32    309,106       55,265
  합계                                2,472,717     442,120    (한 문장으로 돌 때 55,265)
  대기: direct path read 91% · CPU 8%

문제

작업을 8개로 나눴는데도 2분 31초가 걸리고 디스크 읽기가 여덟 배가 된 이유를 실행계획으로 설명하시오.
작업마다 서로 다른 블록만 읽도록 나누는 방법으로 배치를 다시 작성하시오. 어느 작업이 실패했을 때 어떻게 다시 돌릴지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매월 1일 01:00
TRUNCATE TABLE DW_ORD_ITEM_CLN;

BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('DW_CLN_' || TO_CHAR(SYSDATE, 'YYYYMM'));

  -- 테이블이 놓인 블록 범위로 나눈다 · 870블록씩이면 약 64청크 (청크끼리 겹치는 블록이 없다)
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
    task_name   => 'DW_CLN_' || TO_CHAR(SYSDATE, 'YYYYMM'),
    table_owner => USER,
    table_name  => 'ORD_ITEM',
    by_row      => FALSE,
    chunk_size  => 870);

  -- 청크마다 이 문장을 실행하고 커밋한다 · 동시 작업은 CPU 4개에 맞춰 4개
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name      => 'DW_CLN_' || TO_CHAR(SYSDATE, 'YYYYMM'),
    sql_stmt       => 'INSERT INTO DW_ORD_ITEM_CLN (ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS, ORD_YM)
                       SELECT ORD_NO, ITEM_ID, NVL(ORD_ITEM_QTY, 0), ORD_DT, ORD_HMS, SUBSTR(ORD_DT, 1, 6)
                         FROM ORD_ITEM
                        WHERE ROWID BETWEEN :start_id AND :end_id',
    language_flag  => DBMS_SQL.NATIVE,
    parallel_level => 4);
END;

-- 끝난 뒤: 청크 상태를 본다. PROCESSED_WITH_ERROR 가 있으면 원인을 고치고 그 청크만 다시 돌린다
SELECT status, COUNT(*) FROM USER_PARALLEL_EXECUTE_CHUNKS
 WHERE task_name = 'DW_CLN_' || TO_CHAR(SYSDATE, 'YYYYMM')
 GROUP BY status;
-- EXEC DBMS_PARALLEL_EXECUTE.RESUME_TASK('DW_CLN_' || TO_CHAR(SYSDATE, 'YYYYMM'))
-- 건수 대조: SELECT COUNT(*) FROM DW_ORD_ITEM_CLN;  → 2,472,717

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

xplan

[청크 하나 — ROWID 범위 870블록]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT              |                 |      1 |        |      0 |00:00:03.08 |    1764 |   864 |
|   1 |  LOAD TABLE CONVENTIONAL      | DW_ORD_ITEM_CLN |      1 |        |      0 |00:00:03.08 |    1764 |   864 |
|*  2 |   TABLE ACCESS BY ROWID RANGE | ORD_ITEM        |      1 |  38636 |  38634 |00:00:00.41 |     868 |   864 |
-----------------------------------------------------------------------------------------------------------------
   2 - access(ROWID>=CHARTOROWID(:START_ID) AND ROWID<=CHARTOROWID(:END_ID))

[작업 전체]
  청크 64개 · 모두 PROCESSED · 넣은 행 2,472,717
  physical reads 합계 약 55,300 · 경과 52초 · 대기 CPU 약 70%

모범 답안 서술

ORA_HASH(ORD_NO, 7) = :k는 행마다 해시 값을 계산해야 참과 거짓을 가릴 수 있는 조건이다. 이 값으로 정렬된 인덱스도, 이 값으로 나뉜 파티션도 없으므로 작업마다 2번 줄에서 ORD_ITEM 전체 55,265블록을 읽고 그중 8분의 1(약 309K행)만 남긴다. E-Rows 24,727은 함수 조건의 기본 선택도(1%)로 본 값이다. 여덟 작업이 같은 테이블을 처음부터 끝까지 따로 읽으니 physical reads는 8 × 55,265 = 442,120블록이다. 큰 테이블의 전체 스캔이라 버퍼 캐시를 거치지 않는 direct path read로 읽었고, 합쳐서 초당 25MB 남짓인 스토리지를 여덟 세션이 나눠 쓰느라 대기의 91%가 읽기 대기였다. CPU 일은 나뉘었지만 읽는 일은 여덟 배가 되어, 3분 10초가 2분 31초로밖에 줄지 않았다. 나누는 기준을 행의 값이 아니라 행이 놓인 자리로 바꾼다. DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID는 테이블의 익스텐트를 보고 870블록씩 겹치지 않는 ROWID 범위(청크) 64개를 만든다. 청크마다 WHERE ROWID BETWEEN :start_id AND :end_id 문장이 실행되어 그 범위의 블록만 읽으므로(TABLE ACCESS BY ROWID RANGE), 64개를 합쳐도 테이블을 한 번 읽는 양이다. physical reads는 442,120 → 약 55,300, 경과는 2분 31초 → 52초로 준다. 이제는 CPU가 병목이라 동시 작업 수를 CPU 수(4)에 맞췄다. Standard Edition에서도 이 패키지는 스케줄러 작업으로 돌기 때문에 쓸 수 있다. RUN_TASK는 청크마다 문장을 실행하고 커밋하며, 청크별 상태를 USER_PARALLEL_EXECUTE_CHUNKS에 남긴다. 어떤 청크가 실패하면 그 청크만 롤백되어 PROCESSED_WITH_ERROR로 남고 나머지는 PROCESSED다. 원인을 고친 뒤 RESUME_TASK를 부르면 끝나지 않은 청크만 다시 돌리므로, 끝난 청크를 두 번 넣지 않는다. 처음부터 다시 돌릴 때만 TRUNCATE부터 한다.

해설

해시 분할이 맞는 경우: 테이블이 ORD_NO 해시 파티션 8칸으로 나뉘어 있다면 작업 k가 칸 k만 읽으므로 해시로 나눠도 읽는 양이 늘지 않는다. 나누는 기준은 '테이블이 이미 나뉘어 있는 기준'과 같아야 한다.
ROWID 범위 분할의 조건: 같은 행을 두 청크가 고치거나 행끼리 순서가 있는 계산(누적, 행 사이 비교)에는 쓸 수 없다. 이 적재처럼 행마다 독립인 일에 맞는다. 쓰는 쪽의 같은 행을 여러 작업이 고치는 경우는 LOCK-021을, 처리할 행이 한쪽에 몰려 청크마다 일의 양이 다른 경우는 DML-064를 참고한다.
동시 작업 수: 읽기를 줄인 뒤의 병목은 CPU다. CPU 4개에서 작업 8개를 띄우면 서로 CPU를 기다릴 뿐이다. 같은 시간대에 다른 배치가 있으면 그만큼 줄인다(T3 리포트 1번과 7번 칸).
EE라면: INSERT /*+ APPEND PARALLEL */과 병렬 DML이 오라클이 알아서 블록 범위로 나누는 같은 원리다(DML-043). 직접 경로 적재로 청크마다 따로 넣으면 TM 6 잠금 때문에 청크끼리 줄을 서므로, 이 판은 일반 INSERT로 넣었다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황