DW로 넘기는 「판매 흐름」 추출이 정렬을 네 번 하고, 정렬마다 TEMP를 27MB씩 쓴다

실전INSERT유통361 / 700

상황

본사 DW 연동 배치는 매일 02시에 2년 치 판매 전체를 작업 테이블 W_SLE_FLOW로 옮기면서 판매마다 네 가지 값을 붙인다. 그날 매장 안의 판매 순번, 그날 매장 판매 수, 마감까지 남은 판매 수, 같은 날 직전 판매의 시각이다. DW는 이 표를 통째로 가져가 자기 테이블에 다시 정렬해 싣는다. 이 단계만 9초 가까이 걸리고 TEMP를 100MB 넘게 쓴다. 회의에 세 안이 올라왔다. ㉠ DBA는 이 세션만 정렬 메모리를 크게 잡자고 한다. ㉡ 개발팀은 (SHP_NO, SLE_DT, SLE_NO) 인덱스를 만들어 정렬을 없애자고 한다. ㉢ 정렬 기준을 맞춰 정렬 횟수를 줄이자는 안도 있다. 오과장은 "세 안을 숫자와 부작용으로 비교해서 하나를 골라 달라"고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15)는 판매일자와 그날의 순번으로 매겨('20231231-0612') 판매번호 순서가 곧 시각 순서다 · SHP_NO 매장 6곳 · SLE_DT · SLE_HMS · SLE_DOW
SLE_HMS는 33,241건이 앞자리 0 없이 들어 있다('93015'는 09시 30분 15초). 교정은 승인됐지만 다음 달 정기 점검 때 적용된다(교재 03화, 09화).
테이블은 판매번호 순으로 쌓여 있다. 한 매장의 하루 판매(약 114건)는 그날 판매가 든 블록 6 ~ 7개에 흩어져 있다.
W_SLE_FLOW는 배치가 비우고(TRUNCATE) 직접 경로로 채운다. 02시에는 다른 배치들도 돌아 정렬 하나가 받는 작업 공간이 작다. 통계는 최신이다.

원본 SQL

sql

TRUNCATE TABLE W_SLE_FLOW;

INSERT /*+ APPEND */ INTO W_SLE_FLOW
       (SLE_NO, SHP_NO, SLE_DT, SLE_HMS, DAY_SEQ, DAY_CNT, LEFT_CNT, PREV_HMS)
SELECT SLE_NO, SHP_NO, SLE_DT, SLE_HMS,
       ROW_NUMBER() OVER (PARTITION BY SHP_NO, SLE_DT ORDER BY SLE_NO)          AS day_seq,
       COUNT(*)     OVER (PARTITION BY SHP_NO, SLE_DT)                          AS day_cnt,
       ROW_NUMBER() OVER (PARTITION BY SHP_NO, SLE_DT ORDER BY SLE_NO DESC) - 1 AS left_cnt,
       LAG(SLE_HMS) OVER (PARTITION BY SHP_NO, SLE_DT ORDER BY SLE_HMS)         AS prev_hms
  FROM CF10
 ORDER BY SLE_NO;                  -- DW 가 판매번호 순으로 받으면 빠를 것 같아서 (2016)
COMMIT;

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:08.94 |    4701 | 13604 |  15712 |          |          |
|   1 |  LOAD AS SELECT                  | W_SLE_FLOW |      1 |        |      0 |00:00:08.94 |    4701 | 13604 |  15712 |          |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |      1 |    500K|    500K|00:00:08.31 |    4583 | 13604 |  13604 |  256K    |          |
|   3 |    SORT ORDER BY                 |            |      1 |    500K|    500K|00:00:08.12 |    4583 | 13604 |  13604 | 2190K (1)|      27M |
|   4 |     WINDOW SORT                  |            |      1 |    500K|    500K|00:00:06.20 |    4583 | 10203 |  10203 | 2190K (1)|      27M |
|   5 |      WINDOW SORT                 |            |      1 |    500K|    500K|00:00:04.21 |    4583 |  6802 |   6802 | 2190K (1)|      27M |
|   6 |       WINDOW SORT                |            |      1 |    500K|    500K|00:00:02.18 |    4583 |  3401 |   3401 | 2190K (1)|      27M |
|   7 |        TABLE ACCESS FULL         | CF10       |      1 |    500K|    500K|00:00:00.06 |    4583 |     0 |      0 |          |          |
-------------------------------------------------------------------------------------------------------------------------------------------

문제

정렬이 왜 네 번인지 분석 함수의 정렬 기준으로 설명하고, 9초가 어디서 나오는지 쓰시오.
㉠, ㉡, ㉢을 일량과 부작용으로 비교해 하나를 고르시오.
고른 안으로 SQL을 고치시오. 직전 판매 시각이 지금 결과와 달라진다면 어느 쪽이 맞는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

TRUNCATE TABLE W_SLE_FLOW;

-- ㉢ 네 값을 모두 (SHP_NO, SLE_DT, SLE_NO) 한 순서로 계산한다 → 정렬 한 번
INSERT /*+ APPEND */ INTO W_SLE_FLOW
       (SLE_NO, SHP_NO, SLE_DT, SLE_HMS, DAY_SEQ, DAY_CNT, LEFT_CNT, PREV_HMS)
SELECT SLE_NO, SHP_NO, SLE_DT, SLE_HMS,
       day_seq, day_cnt,
       day_cnt - day_seq AS left_cnt,              -- 역순 번호를 따로 매기지 않는다
       prev_hms
  FROM (SELECT SLE_NO, SHP_NO, SLE_DT, SLE_HMS,
               ROW_NUMBER() OVER (PARTITION BY SHP_NO, SLE_DT ORDER BY SLE_NO) AS day_seq,
               COUNT(*)     OVER (PARTITION BY SHP_NO, SLE_DT)                 AS day_cnt,
               LAG(SLE_HMS) OVER (PARTITION BY SHP_NO, SLE_DT ORDER BY SLE_NO) AS prev_hms   -- 시각 순서 = 판매번호 순서
          FROM CF10);
-- 바깥 ORDER BY 는 뗀다 : DW 가 다시 정렬해 싣는다 (꼭 필요하면 ORDER BY SHP_NO, SLE_DT, SLE_NO 로 창과 맞춘다)
COMMIT;

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

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes | Used-Mem | Used-Tmp |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |            |      1 |        |      0 |00:00:02.63 |    4701 |  3401 |   5509 |          |          |
|   1 |  LOAD AS SELECT                  | W_SLE_FLOW |      1 |        |      0 |00:00:02.63 |    4701 |  3401 |   5509 |          |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |            |      1 |    500K|    500K|00:00:02.11 |    4583 |  3401 |   3401 |  256K    |          |
|   3 |    VIEW                          |            |      1 |    500K|    500K|00:00:02.05 |    4583 |  3401 |   3401 |          |          |
|   4 |     WINDOW SORT                  |            |      1 |    500K|    500K|00:00:01.98 |    4583 |  3401 |   3401 | 2190K (1)|      27M |
|   5 |      TABLE ACCESS FULL           | CF10       |      1 |    500K|    500K|00:00:00.06 |    4583 |     0 |      0 |          |          |
-------------------------------------------------------------------------------------------------------------------------------------------

모범 답안 서술

3번부터 6번 줄까지 정렬이 네 번 있다. 분석 함수 네 개의 정렬 기준이 셋으로 갈렸기 때문이다. 판매 순번과 판매 수는 (매장, 판매일, 판매번호) 순서 하나로 계산되지만, 남은 판매 수는 판매번호 역순(DESC)으로, 직전 판매 시각은 SLE_HMS 순서로 다시 줄 세운다. 여기에 바깥 ORDER BY SLE_NO가 네 번째 정렬이다. 50만 행 정렬 하나가 27M쯤인데 02시의 작업 공간에 들어가지 않아 넷 다 한 번씩 TEMP로 내렸다 올렸다(Used-Mem (1), Used-Tmp 27M씩, Reads와 Writes 13,604). 블록은 CF10을 한 번 읽은 4,583뿐이고, 9초의 대부분은 정렬과 TEMP 왕복이다. ㉢을 고른다. 남은 판매 수는 '그날 판매 수 - 순번'이라 역순 번호를 따로 매길 필요가 없다. 직전 판매 시각은 SLE_HMS가 아니라 판매번호 순서로 찾는다. 판매번호 순서가 곧 시각 순서이고, SLE_HMS는 33,241건이 앞자리 0 없이 들어 있어('93015'가 '100000'보다 뒤로 간다) 문자열 순서가 시각 순서도 아니다. 그래서 원래 SQL의 직전 판매 시각은 오전 9시대 판매가 낀 날마다 틀려 있었고, 달라진 쪽이 맞는 값이다. 세 함수가 모두 (매장, 판매일, 판매번호) 한 순서로 계산되므로 정렬은 한 번이다. 바깥 ORDER BY는 DW가 다시 정렬해 실으므로 뗀다(꼭 필요하면 창과 같은 순서로 적어 정렬이 붙지 않게 한다). 정렬 4번 → 1번, TEMP 108M → 27M, 시간 8.94초 → 2.63초이고, 인덱스도 메모리도 늘리지 않는다. ㉠ 이 세션만 정렬 메모리를 크게 잡으면(MANUAL, SORT_AREA_SIZE 64MB) 네 정렬이 TEMP 없이 끝나 4초 안팎이 된다. 하지만 정렬 횟수는 그대로이고 판매가 늘면 다시 모자란다. 세션마다 메모리를 고정하는 설정은 PGA 자동 관리 밖에서 메모리를 쓰므로 같은 시각의 다른 배치와 온라인이 피해를 본다(JOIN-023). ㉢으로 정렬을 하나로 줄인 뒤에도 TEMP가 문제가 되면 그때 이 정렬 하나의 작업 공간을 본다. ㉡ (SHP_NO, SLE_DT, SLE_NO) 인덱스를 처음부터 끝까지 읽으면 정렬 없이 창을 계산할 수 있다(WINDOW NOSORT). 대신 인덱스 순서대로 테이블을 50만 번 찾아간다. 한 매장의 하루 판매가 블록 6 ~ 7개에 흩어져 있어 테이블 방문이 약 2만 7천 블록, 리프가 약 1,900블록이다. FULL 한 번(4,583)보다 여섯 배 많고, 버퍼 캐시에 없으면 한 블록씩 읽는 물리 읽기가 된다. 모든 컬럼을 인덱스에 넣으면 테이블은 안 읽지만 테이블 사본을 하나 더 두는 셈이고, POS가 판매를 넣을 때마다 그 인덱스를 고친다. 밤 배치 하나를 위해 들이기에는 대가가 크다.

해설

같은 정렬을 함께 쓰는 조건: PARTITION BY와 ORDER BY를 이어 붙인 열 순서가 같거나 한쪽이 다른 쪽의 앞부분이면 함수들이 정렬 하나를 함께 쓴다. COUNT(*) OVER (PARTITION BY SHP_NO, SLE_DT)가 판매 순번과 같은 정렬에 얹히는 것도 그래서다. 실행계획의 WINDOW SORT 줄 수로 확인한다.
다른 정렬 기준이 꼭 필요할 때: 업무상 다른 순서가 꼭 필요하면 정렬이 하나 더 드는 것은 피할 수 없다. 그때는 기준이 같은 함수끼리 모아 정렬 수를 최소로 한다.
결과 대조: 직전 판매 시각은 원래 결과와 달라지므로, SLE_HMS의 앞자리가 빠진 날 몇 개를 골라 판매번호 순서와 실제 시각을 대조한 기록을 남긴다. 나머지 세 값은 원래 결과와 MINUS 양방향 0 / 0이어야 한다.
다음 달 SLE_HMS 교정이 적용되면 SLE_HMS 순서도 시각 순서가 된다. 그래도 같은 초에 겹치는 판매가 있으므로(교재 09화에서 13,585쌍) 판매번호로 순서를 정하는 편이 낫다.
9초짜리 단계라도 02시 체인에서 TEMP를 함께 쓰는 배치들은 서로의 정렬을 밀어낸다. TEMP 108M를 27M로 줄인 효과는 이 단계보다 체인 전체에 더 크다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황