정산 대사 화면이 하루치 137건에 139블록을 읽고, 일주일을 고르면 정산 테이블을 통째로 읽는다

중급INSERT한빛유통99 / 700

상황

정산팀의 「일자별 정산 대사」는 정산일 하루(가끔 일주일)를 골라 그 기간의 정산 목록을 보여 준다. 월말에는 날짜를 바꿔 가며 하루 수천 번 연다. 정산 테이블은 야간 배치가 매일 밤 비우고(TRUNCATE) 주문 테이블에서 한 문장으로 다시 채운다. 조민우는 "정산일 인덱스를 만들었는데 효과가 없다. 매일 밤 적재가 끝나면 인덱스를 REBUILD하자"고 한다. 아래는 2024-06-14 하루와 2024-06-10 ~ 06-16 일주일이다.

테이블과 인덱스

T_ORD(원천): 100,000행 · 896블록 · ORD_SEQ 순으로 쌓였다 · ORD_DT DATE(시분초 있음, 2023 ~ 2024) · ORD_DT는 주문 번호와 상관없이 730일을 돌아가며 붙어 있어, 같은 날짜의 주문은 ORD_SEQ가 730씩 떨어져 있다
T_ORD_STLM 정산: 100,000행 · 640블록(블록당 약 156행) · 주문 한 건에 정산 한 건 · STLM_DT는 ORD_DT를 그대로 옮긴 값 · 하루 137건 · 일주일 959건
인덱스 IX_STLM_DT (STLM_DT): BLEVEL 1 · 리프 약 270블록 · CLUSTERING_FACTOR 99,986 · IX_STLM_CUS (CUS_ID): 「고객별 정산 내역」용 · CLUSTERING_FACTOR 약 100,000 · 통계는 적재 뒤에 수집한다
STLM_SEQ(정산 번호)는 지금 주문 번호 순서와 같다 · 정산 번호로 대사표를 맞추는 보고서가 있다 · DB는 NOARCHIVELOG

원본 SQL

sql

-- 야간 적재 (매일 밤, TRUNCATE 뒤)
INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT ROWNUM, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD;
COMMIT;

-- 「일자별 정산 대사」
SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND STLM_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY STLM_DT, ORD_SEQ;

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[대사 · 하루 2024-06-14]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |      1 |        |    137 |00:00:00.01 |     139 |          |
|   1 |  SORT ORDER BY                        |            |      1 |    137 |    137 |00:00:00.01 |     139 |   18432 |
|*  2 |   FILTER                              |            |      1 |        |    137 |00:00:00.01 |     139 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_STLM |      1 |    137 |    137 |00:00:00.01 |     139 |          |
|*  4 |     INDEX RANGE SCAN                  | IX_STLM_DT |      1 |    137 |    137 |00:00:00.01 |       2 |          |
------------------------------------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   4 - access("STLM_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "STLM_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

[대사 · 일주일 2024-06-10 ~ 06-16]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |      1 |        |    959 |00:00:00.01 |     640 |          |
|   1 |  SORT ORDER BY                        |            |      1 |    959 |    959 |00:00:00.01 |     640 |  124K   |
|*  2 |   FILTER                              |            |      1 |        |    959 |00:00:00.01 |     640 |          |
|*  3 |    TABLE ACCESS FULL                  | T_ORD_STLM |      1 |    959 |    959 |00:00:00.01 |     640 |          |
------------------------------------------------------------------------------------------------------------------
   3 - filter(("STLM_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "STLM_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[USER_INDEXES · USER_TABLES]  IX_STLM_DT  CLUSTERING_FACTOR 99,986 · T_ORD_STLM BLOCKS 640 · NUM_ROWS 100,000

문제

하루치 137건에 테이블을 137블록 읽는 이유를 CLUSTERING_FACTOR와 야간 적재 SQL로 설명하시오. 일주일이면 옵티마이저가 FULL로 바꾸는 이유와, REBUILD 제안이 효과가 있는지도 설명하시오.
대사 화면 SQL은 그대로 두고 적재를 고쳐 하루치가 몇 블록으로 끝나게 하시오. 정산 번호(STLM_SEQ)의 뜻이 바뀌지 않게 하고, 고친 적재의 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 야간 적재: 번호는 주문 순서로 (뜻을 바꾸지 않는다) · 쌓는 순서는 정산일 순으로
INSERT /*+ APPEND */ INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT ROW_NUMBER() OVER (ORDER BY ORD_SEQ), ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD
 ORDER BY ORD_DT, ORD_SEQ;
COMMIT;
-- 통계는 지금처럼 적재 뒤에 수집한다 → IX_STLM_DT 의 CF 가 약 640 으로 내려온다

-- 「일자별 정산 대사」는 그대로
SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND STLM_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY STLM_DT, ORD_SEQ;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[대사 · 하루 2024-06-14 · 정렬 적재 뒤]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |            |      1 |        |    137 |00:00:00.01 |       4 |          |
|   1 |  SORT ORDER BY                        |            |      1 |    137 |    137 |00:00:00.01 |       4 |   18432 |
|*  2 |   FILTER                              |            |      1 |        |    137 |00:00:00.01 |       4 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_STLM |      1 |    137 |    137 |00:00:00.01 |       4 |          |
|*  4 |     INDEX RANGE SCAN                  | IX_STLM_DT |      1 |    137 |    137 |00:00:00.01 |       2 |          |
------------------------------------------------------------------------------------------------------------------

[야간 적재 · 정렬 적재]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                      |            |      1 |        |      0 |00:00:00.24 |    1206 |          |
|   1 |  LOAD AS SELECT                       | T_ORD_STLM |      1 |        |      0 |00:00:00.24 |    1206 |    2070K |
|   2 |   OPTIMIZER STATISTICS GATHERING      |            |      1 |    100K|    100K|00:00:00.15 |     896 |          |
|   3 |    SORT ORDER BY                      |            |      1 |    100K|    100K|00:00:00.12 |     896 |    5120K |
|   4 |     WINDOW SORT                       |            |      1 |    100K|    100K|00:00:00.08 |     896 |    4608K |
|   5 |      TABLE ACCESS FULL                | T_ORD      |      1 |    100K|    100K|00:00:00.02 |     896 |          |
------------------------------------------------------------------------------------------------------------------

[일주일]  INDEX RANGE SCAN 으로 바뀐다 · Buffers 640 → 약 12 · [USER_INDEXES] IX_STLM_DT CLUSTERING_FACTOR 99,986 → 약 640

모범 답안 서술

대사 계획의 4번 줄(인덱스)은 2블록이고, 3번 줄 테이블 액세스에서 137행에 137블록을 읽었다. IX_STLM_DT의 CLUSTERING_FACTOR는 99,986으로 행 수와 같다. 같은 날짜의 정산이 행마다 다른 블록에 있다는 뜻이다. 원인은 야간 적재다. INSERT … SELECT에 ORDER BY가 없어서 정산은 T_ORD를 읽은 순서, 곧 주문 번호 순으로 쌓였다. T_ORD에서 같은 날짜의 주문은 730건씩 떨어져 있고, 블록 하나에 156행이 들어가므로 그날 정산 137건이 137블록에 흩어졌다. 일주일(959건)을 고르면 인덱스 경로가 약 960블록이 되어 테이블 전체 640블록보다 비싸다. 그래서 옵티마이저가 FULL로 바꾼다. 이 테이블에서 손익분기는 (LEAF 270 + CF 99,986) × s = 640에서 약 0.64%, 5일 남짓이다. REBUILD는 효과가 없다. 인덱스를 다시 만들어도 테이블 행의 순서는 그대로이므로 CF도 99,986 그대로다. 바꿔야 할 것은 테이블이 쌓이는 순서이고, 이 테이블은 매일 밤 통째로 다시 채우므로 그 순서를 정하는 것은 적재 SQL 한 줄이다. 적재에 ORDER BY ORD_DT, ORD_SEQ를 넣는다. 직접 경로 적재는 받은 순서대로 블록을 채우므로, 하루치 137건이 한두 블록에 모인다. CF가 약 640으로 내려와 하루치 Buffers는 139 → 4, 일주일은 FULL 640 → 약 12다. 월말 하루 수천 번이면 수십만 블록이 수천 블록으로 준다. 정산 번호의 뜻도 지킨다. 원래 STLM_SEQ는 ROWNUM, 곧 주문 번호 순이었다. ROWNUM은 ORDER BY보다 먼저 매겨지므로 정렬을 넣고 ROWNUM을 그대로 두면 번호가 엉뚱하게 매겨진다. 그래서 번호는 ROW_NUMBER() OVER (ORDER BY ORD_SEQ)로 주문 순서대로 매기고, 쌓는 순서만 날짜로 바꾼다. 정산 번호로 맞추던 대사 보고서는 그대로 쓸 수 있다. 대가는 적재에 정렬이 둘(WINDOW SORT, SORT ORDER BY) 더 든다는 것이다. 10만 행이라 메모리 안(Used-Mem 약 5MB)에서 끝나고 적재 시간은 0.1 ~ 0.2초쯤 는다. 또 지금까지 주문 번호 순이던 물리 순서가 날짜 순으로 바뀌므로, 주문 번호로 범위를 읽는 SQL이 있었다면 그쪽 CF가 나빠진다. 고객 번호 인덱스(IX_STLM_CUS)는 원래도 CF가 약 100,000이라 잃는 것이 없다. 적재 순서를 바꾸기 전에 T_ORD_STLM을 읽는 SQL을 V$SQL_PLAN으로 확인한다.

해설

함정: SELECT ROWNUM, … FROM T_ORD ORDER BY ORD_DT로 쓰면 행은 날짜 순으로 쌓이지만 정산 번호는 정렬 전 순서로 매겨진다. 대사 화면은 빨라져도 정산 번호가 날짜와 엇갈려 보고서가 틀어진다.
이 처방이 오래 가는 것은 테이블을 매일 밤 다시 채우기 때문이다. 쌓아 두고 덧붙이기만 하는 테이블이라면 한 번 정렬해 다시 쌓아도 이후의 적재 순서에 따라 CF가 다시 나빠진다(교재 04화 §4: 5%를 섞어 넣자 23,387 → 122,193).
다른 정답: 대사 화면이 쓰는 컬럼을 다 담은 커버링 인덱스도 테이블 액세스를 없앤다. 하지만 컬럼이 다섯 개라 인덱스가 테이블만큼 커진다. 적재 SQL 한 줄로 끝나는 이 경우에는 적재 순서를 고치는 편이 싸다.
ACC-015의 고객별 화면은 이 처방으로 좋아지지 않는다. 테이블은 한 가지 순서로만 쌓을 수 있으므로 고객 쪽은 인덱스 컬럼 순서와 커버링으로 푼다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황