PART-031

판매상품 재집계를 칸마다 하나씩 여섯 작업으로 나눴는데 두 작업만 두 배 늦게 끝난다

기초DDL유통406 / 700

상황

매월 1일 「판매상품 재집계」 배치는 CF11_H를 칸마다 작업 하나씩 맡겨 여섯 작업으로 나눠 돌린다. 작업마다 읽은 행을 상품 단가 이력과 맞춰 보는 PL/SQL 처리가 이어서 돌아서, 작업 시간은 읽은 행 수에 거의 비례한다. 지난달 작업 1, 2, 5, 6은 약 6분 10초에 끝났고 작업 3과 4는 약 12분 30초가 걸렸다. 배치는 가장 늦은 작업이 끝나야 끝난다. 2019년 설계 메모에는 "작업 슬롯 6개 → 해시 6칸."이라고 적혀 있고, 올해 배치 서버의 동시 작업 한도는 8로 늘었다.

테이블과 인덱스

CF11_H 판매상품(파티션 복사본, 문제 설정): CF11 1,096,708행 · 8,204블록을 PARTITION BY HASH (SLE_NO) PARTITIONS 6으로 나눠 CTAS로 지었다. SLE_NO는 판매번호로 값이 약 50만 가지다.
LOCAL 인덱스 CF11_H_PK (SLE_NO, ITM_NO). 다른 인덱스는 없다. 통계는 최신이다.
작업 하나는 칸 하나를 PARTITION (칸 이름)으로 지목해 읽고 상품별로 합쳐 작업 테이블 CF11_SUM_W에 넣는다.

원본 SQL

sql

CREATE TABLE CF11_H
PARTITION BY HASH (SLE_NO) PARTITIONS 6
AS SELECT * FROM CF11;

-- 작업 k (k = 1 ~ 6) : 칸 하나를 맡는다 (아래는 작업 3)
INSERT INTO CF11_SUM_W (JOB_NO, ITM_NO, SLE_QTY, SLE_AMT)
SELECT :job_no, ITM_NO, SUM(SLE_QTY), SUM(SLE_QTY * SLE_UPRC)
  FROM CF11_H PARTITION (SYS_P3103)
 GROUP BY ITM_NO;

실행계획 학습용 예시

xplan

[작업 1 · SYS_P3101]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 | 00:00:00.21 |    1031 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL | CF11_SUM_W |      1 |        |      0 | 00:00:00.21 |    1031 |        |       |
|   2 |   HASH GROUP BY          |            |      1 |    109 |    109 | 00:00:00.20 |    1026 |        |       |
|   3 |    PARTITION HASH SINGLE |            |      1 |   137K |   137K | 00:00:00.09 |    1026 |      1 |     1 |
|   4 |     TABLE ACCESS FULL    | CF11_H     |      1 |   137K |   137K | 00:00:00.07 |    1026 |      1 |     1 |
-------------------------------------------------------------------------------------------------------------------

[작업 3 · SYS_P3103]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 | 00:00:00.42 |    2056 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL | CF11_SUM_W |      1 |        |      0 | 00:00:00.42 |    2056 |        |       |
|   2 |   HASH GROUP BY          |            |      1 |    109 |    109 | 00:00:00.40 |    2051 |        |       |
|   3 |    PARTITION HASH SINGLE |            |      1 |   274K |   274K | 00:00:00.18 |    2051 |      3 |     3 |
|   4 |     TABLE ACCESS FULL    | CF11_H     |      1 |   274K |   274K | 00:00:00.14 |    2051 |      3 |     3 |
-------------------------------------------------------------------------------------------------------------------

[USER_TAB_PARTITIONS]
  PARTITION_NAME  POSITION   NUM_ROWS   BLOCKS
  SYS_P3101              1    137,092    1,026
  SYS_P3102              2    137,071    1,025
  SYS_P3103              3    274,180    2,051
  SYS_P3104              4    274,203    2,051
  SYS_P3105              5    137,085    1,026
  SYS_P3106              6    137,077    1,025

문제

3번과 4번 칸만 다른 칸의 두 배인 까닭을 해시 파티션의 칸 수로 설명하시오.
테이블을 새로 짓지 않고 칸을 고르게 만드는 DDL을 쓰고, 그 DDL이 하는 일과 돌리기 전후에 확인할 것을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 6칸 → 7칸 : 오라클이 아직 나뉘지 않은 3번 칸을 골라 다시 해시해 둘로 나눈다 (로컬 인덱스도 같이 고친다)
ALTER TABLE CF11_H ADD PARTITION UPDATE INDEXES;
-- 7칸 → 8칸 : 4번 칸을 나눈다
ALTER TABLE CF11_H ADD PARTITION UPDATE INDEXES;

-- 돌린 뒤 : 통계를 모아 여덟 칸이 고른지 확인한다
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CF11_H')
SELECT partition_name, partition_position, num_rows, blocks
  FROM user_tab_partitions
 WHERE table_name = 'CF11_H'
 ORDER BY partition_position;

-- 로컬 인덱스가 모두 쓸 수 있는 상태인가
SELECT partition_name, status
  FROM user_ind_partitions
 WHERE index_name = 'CF11_H_PK';

-- 작업은 여덟 개 (작업 k 가 칸 k 를 맡는다)

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

xplan

[ADD PARTITION 두 번 · 한 번에 약 27만 행을 다시 해시해 약 13만 7천 행을 새 칸으로 옮긴다 · 각 약 2초]

[USER_TAB_PARTITIONS · 돌린 뒤]
  PARTITION_NAME  POSITION   NUM_ROWS   BLOCKS
  SYS_P3101              1    137,092    1,026
  SYS_P3102              2    137,071    1,025
  SYS_P3103              3    137,102    1,026
  SYS_P3104              4    137,090    1,026
  SYS_P3105              5    137,085    1,026
  SYS_P3106              6    137,077    1,025
  SYS_P3107              7    137,078    1,025
  SYS_P3108              8    137,113    1,026

[작업 3 · SYS_P3103 · 돌린 뒤]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 | 00:00:00.21 |    1031 |        |       |
|   1 |  LOAD TABLE CONVENTIONAL | CF11_SUM_W |      1 |        |      0 | 00:00:00.21 |    1031 |        |       |
|   2 |   HASH GROUP BY          |            |      1 |    109 |    109 | 00:00:00.20 |    1026 |        |       |
|   3 |    PARTITION HASH SINGLE |            |      1 |   137K |   137K | 00:00:00.09 |    1026 |      3 |     3 |
|   4 |     TABLE ACCESS FULL    | CF11_H     |      1 |   137K |   137K | 00:00:00.07 |    1026 |      3 |     3 |
-------------------------------------------------------------------------------------------------------------------

  배치 (작업 8개 동시) : 약 12분 30초 → 약 6분 10초

모범 답안 서술

파티션 통계를 보면 3번과 4번 칸은 약 27만 4천 행(2,051블록)이고 나머지 넷은 약 13만 7천 행(1,026블록)이다. 작업 3의 실행계획도 칸 하나(PARTITION HASH SINGLE, 3 / 3)를 읽는데 Buffers가 2,056으로 작업 1(1,031)의 두 배다. 작업 시간은 읽은 행 수에 거의 비례하므로 작업 3과 4가 두 배 늦게 끝나고, 배치는 가장 늦은 작업이 끝나야 끝난다. 까닭은 칸 수 6이 2의 거듭제곱이 아니라는 데 있다. 오라클의 해시 파티션은 키를 해시해 2의 거듭제곱 개 조각(여기서는 8조각)으로 나누고, 칸 수가 4와 8 사이이면 칸 일부만 둘로 나눈 상태로 둔다(선형 해싱). 4칸에서 5칸이 될 때 1번 칸이, 6칸이 될 때 2번 칸이 나뉘었다. 그래서 1, 2, 5, 6번 칸은 한 조각씩(각 1/8), 아직 나뉘지 않은 3번과 4번 칸은 두 조각씩(각 1/4) 받는다. 키 SLE_NO는 값이 약 50만 가지라 조각끼리는 고르다. 칸 수만 문제다. 테이블을 새로 짓지 않고 ALTER TABLE CF11_H ADD PARTITION UPDATE INDEXES를 두 번 돌려 8칸으로 맞춘다. 해시 칸을 하나 더하면 오라클이 아직 나뉘지 않은 칸 하나(처음에는 3번, 다음에는 4번)를 골라 그 칸의 행을 다시 해시하고, 절반쯤을 새 칸으로 옮긴다. 한 번에 약 27만 행을 다시 쓰므로 그동안 그 칸의 DML은 기다린다. 그래서 배치가 없는 시간에 돌린다. UPDATE INDEXES를 빼면 나뉜 두 칸의 로컬 인덱스 CF11_H_PK가 UNUSABLE이 된다. 돌린 뒤에는 통계를 다시 모아 여덟 칸이 모두 약 13만 7천 행인지, 로컬 인덱스가 모두 쓸 수 있는 상태인지 확인하고, 작업도 여덟 개로 나눈다(동시 작업 한도 8). 가장 큰 칸이 배치 시간을 정하므로 작업 3의 Buffers는 2,056 → 약 1,031, 배치는 약 12분 30초 → 약 6분 10초로 준다.

해설

선형 해싱: 칸 수가 2의 거듭제곱 사이에 있으면 일부 칸만 나뉜 상태라서 어떤 칸은 다른 칸의 두 배다. 그래서 해시 칸 수는 2, 4, 8, 16처럼 2의 거듭제곱으로 정한다.
COALESCE PARTITION을 두 번 돌려 4칸으로 맞추는 것도 고르게 만드는 답이다. 다만 작업 넷이 1/4씩 맡으면 가장 늦은 작업은 지금과 같은 12분 30초라서, 동시 작업 한도가 8인 이 배치에는 8칸이 맞다.
칸 수를 작업 수에 맞춰 정한 것이 처음 잘못이다. 칸 수는 고르게 나뉘는 수(2의 거듭제곱)로 정하고, 작업은 칸 여러 개를 묶어 맡기면 된다.
해시 키는 값 종류가 많아야 이 규칙이 통한다. 매장 번호처럼 값이 몇 개뿐이면 칸 수를 맞춰도 고르지 않다(PART-030).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황