야간 TEMP를 줄이려는 적재 정리안을 개발 서버에 먼저 넣었더니 메뉴별 판매 추이 화면이 45배 무거워졌다

중급INSERT유통345 / 700

상황

지난달 03시에 배치 하나가 ORA-01652로 멈춘 뒤, DBA는 "힙 테이블은 넣은 순서를 약속하지 않으니 적재 SELECT의 ORDER BY는 정렬만 늘린다"며 야간 INSERT … SELECT에서 ORDER BY를 일괄로 지우는 정리안을 올렸다. 목록에는 MD팀의 분석 사본 CF11_ANL을 채우는 적재도 들어 있다. 정리안을 개발 서버에 먼저 넣고 다음 날 아침 MD팀 화면을 재 보니, 「메뉴별 일 판매 추이」가 메뉴 하나 한 해에 81블록이던 것이 3,689블록이 됐다. MD팀 20명이 하루 약 1,500번 여는 화면이다. 적재 SQL의 ORDER BY 옆에는 아무 주석도 없다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 / CF11 판매상품: 1,096,708행 · 8,204블록 · 둘 다 판매번호(곧 판매일) 순으로 쌓여 있다
CF11_ANL 분석 사본(이 문제에서 쓰는 표): ITM_NO · SLE_DT · SLE_NO · SHP_NO · SLE_QTY · SLE_UPRC · 1,096,708행 · 10,240블록(블록당 약 107행) · 03:00에 TRUNCATE한 뒤 APPEND로 다시 채운다 · MD팀 화면만 읽는다
인덱스 CF11_ANL_X01 (ITM_NO, SLE_DT): 리프 약 3,100블록 · CLUSTERING_FACTOR는 운영 10,262, 정리안대로 적재한 개발 서버 641,880
work area 하나의 상한(global memory bound)은 03:00에 약 20MB다. 02:30에는 TEMP를 많이 쓰는 다른 두 배치가 아직 돌지 않아 약 100MB다. 통계는 적재 뒤에 수집한다.

원본 SQL

sql

-- 정리안 : 03:00 분석 사본 적재에서 ORDER BY d.ITM_NO, h.SLE_DT, d.SLE_NO 를 지운 판
INSERT /*+ APPEND */ INTO CF11_ANL (ITM_NO, SLE_DT, SLE_NO, SHP_NO, SLE_QTY, SLE_UPRC)
SELECT d.ITM_NO, h.SLE_DT, d.SLE_NO, h.SHP_NO, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO;

-- MD 「메뉴별 일 판매 추이」
SELECT SLE_DT, SUM(SLE_QTY) AS QTY, SUM(SLE_QTY * SLE_UPRC) AS AMT
  FROM CF11_ANL
 WHERE ITM_NO = :itm
   AND SLE_DT BETWEEN :d_from AND :d_to
 GROUP BY SLE_DT
 ORDER BY SLE_DT;

실행계획 학습용 예시

xplan

[운영 · 지금 적재 (ORDER BY 있음) · 03:00]
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |          |      1 |        |      0 |00:00:09.62 |   13204 |       |       |          |         |
|   1 |  LOAD AS SELECT                  | CF11_ANL |      1 |        |      0 |00:00:09.62 |   13204 |       |       |          |         |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |      1 |   1096K|   1096K|00:00:07.84 |   12787 |       |       |          |         |
|   3 |    SORT ORDER BY                 |          |      1 |   1096K|   1096K|00:00:07.61 |   12787 |    81M|  3961K|   20M (1)|      67M|
|*  4 |     HASH JOIN                    |          |      1 |   1096K|   1096K|00:00:00.71 |   12787 |    19M|  3412K|   19M (0)|         |
|   5 |      TABLE ACCESS FULL           | CF10     |      1 |    500K|    500K|00:00:00.05 |    4583 |       |       |          |         |
|   6 |      TABLE ACCESS FULL           | CF11     |      1 |   1096K|   1096K|00:00:00.12 |    8204 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------
   4 - access("D"."SLE_NO"="H"."SLE_NO")

[운영 · MD 화면 · 카페모카 2023년 한 해 (6,183줄)]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |    365 |00:00:00.01 |      81 |
|   1 |  SORT GROUP BY NOSORT         |              |      1 |    365 |    365 |00:00:00.01 |      81 |
|*  2 |   FILTER                      |              |      1 |        |   6183 |00:00:00.01 |      81 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11_ANL     |      1 |   6200 |   6183 |00:00:00.01 |      81 |
|*  4 |     INDEX RANGE SCAN          | CF11_ANL_X01 |      1 |   6200 |   6183 |00:00:00.01 |      21 |
---------------------------------------------------------------------------------------------------------

[개발 서버 · 정리안대로 적재한 뒤 · 같은 메뉴, 같은 기간]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |    365 |00:00:00.09 |    3689 |
|   1 |  SORT GROUP BY NOSORT         |              |      1 |    365 |    365 |00:00:00.09 |    3689 |
|*  2 |   FILTER                      |              |      1 |        |   6183 |00:00:00.09 |    3689 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11_ANL     |      1 |   6200 |   6183 |00:00:00.09 |    3689 |
|*  4 |     INDEX RANGE SCAN          | CF11_ANL_X01 |      1 |   6200 |   6183 |00:00:00.01 |      21 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(:D_FROM<=:D_TO)
   4 - access("ITM_NO"=:ITM AND "SLE_DT">=:D_FROM AND "SLE_DT"<=:D_TO)

문제

두 MD 화면 계획이 모양은 같은데 Buffers가 81과 3,689로 갈린 까닭을 CF11_ANL_X01의 CLUSTERING_FACTOR와 적재 SQL로 설명하시오.
정리안을 이 적재에도 적용할지 판단하시오. 적재의 정렬 비용(첫 계획 3번 줄)과 화면의 이득을 하루 단위 숫자로 비교하시오.
적재 SQL을 어떻게 남길지 쓰고, 03시의 TEMP 부족 위험은 정렬을 지우지 않고 무엇으로 줄일지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 분석 사본 적재 : ORDER BY 를 남긴다 (TEMP 정리안에서 이 적재는 뺀다)
--   이유 = CF11_ANL_X01 (ITM_NO, SLE_DT) 의 CLUSTERING_FACTOR.
--          메뉴순으로 쌓아야 MD 「메뉴별 일 판매 추이」가 메뉴 하나를 이어진 블록에서 읽는다
--          (지우면 CF 10,262 → 641,880, 화면 81 → 3,689블록 · 개발 서버 실측)
--   일정 = TEMP 를 많이 쓰는 다른 두 배치보다 앞인 02:30 · 직렬로 돌린다 (병렬 적재는 순서를 흩뜨린다)
INSERT /*+ APPEND */ INTO CF11_ANL (ITM_NO, SLE_DT, SLE_NO, SHP_NO, SLE_QTY, SLE_UPRC)
SELECT d.ITM_NO, h.SLE_DT, d.SLE_NO, h.SHP_NO, d.SLE_QTY, d.SLE_UPRC
  FROM CF10 h, CF11 d
 WHERE d.SLE_NO = h.SLE_NO
 ORDER BY d.ITM_NO, h.SLE_DT, d.SLE_NO;

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

xplan

[02:30으로 옮긴 적재]
-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |          |      1 |        |      0 |00:00:03.08 |   13204 |       |       |          |         |
|   1 |  LOAD AS SELECT                  | CF11_ANL |      1 |        |      0 |00:00:03.08 |   13204 |       |       |          |         |
|   2 |   OPTIMIZER STATISTICS GATHERING |          |      1 |   1096K|   1096K|00:00:02.71 |   12787 |       |       |          |         |
|   3 |    SORT ORDER BY                 |          |      1 |   1096K|   1096K|00:00:02.48 |   12787 |    81M|  3961K|   72M (0)|         |
|*  4 |     HASH JOIN                    |          |      1 |   1096K|   1096K|00:00:00.69 |   12787 |    19M|  3412K|   19M (0)|         |
|   5 |      TABLE ACCESS FULL           | CF10     |      1 |    500K|    500K|00:00:00.05 |    4583 |       |       |          |         |
|   6 |      TABLE ACCESS FULL           | CF11     |      1 |   1096K|   1096K|00:00:00.12 |    8204 |       |       |          |         |
-----------------------------------------------------------------------------------------------------------------------------------------
   4 - access("D"."SLE_NO"="H"."SLE_NO")

[MD 화면]  그대로 · SORT GROUP BY NOSORT · TABLE ACCESS BY INDEX ROWID · Buffers 81

모범 답안 서술

두 MD 화면은 같은 인덱스 범위(6,183항목, 21블록)를 읽는다. 갈린 곳은 3번 줄 테이블 액세스다. 운영의 사본은 적재 SQL의 ORDER BY d.ITM_NO, h.SLE_DT, d.SLE_NO 덕분에 메뉴순, 같은 메뉴 안에서는 판매일 순으로 쌓였다. 직접 경로 적재는 SELECT가 내놓는 순서대로 새 블록을 채우므로 카페모카 한 해 6,183줄이 이어진 약 58블록에 모여 있고, CF11_ANL_X01의 CLUSTERING_FACTOR가 테이블 블록 수 가까이(10,262)다. 정리안대로 정렬을 지우면 사본은 해시 조인이 내놓는 순서, 곧 CF11의 판매번호(판매일) 순으로 쌓인다. 메뉴 하나의 줄이 그 해의 블록 전체에 흩어져 CF가 641,880이 되고, 같은 6,183줄을 읽으려고 블록을 3,600번 넘게 바꿔 탄다(Buffers 81 → 3,689). 정리안은 이 적재에는 맞지 않는다. SORT-039의 정렬이 쓸모없었던 것은 적재한 표를 해시로 통째로 묶어 순서를 쓰지 않았기 때문이다. 이 표는 MD 화면이 메뉴 하나의 범위를 인덱스로 읽으므로 물리 순서에 기댄다. 하루 단위로 비교하면, 정렬은 밤에 한 번 110만 줄을 정렬하며 TEMP 67MB를 쓰고 약 7초가 든다. 정렬을 지우면 화면이 1,500번 × 약 3,600블록, 곧 하루 약 540만 블록을 더 읽는다(지금은 하루 약 12만 블록). 그래서 ORDER BY는 남기고, 같은 정리안이 다시 나오지 않도록 옆에 이유(CF11_ANL_X01의 CF, 화면 81 → 3,689블록)를 주석으로 적는다. 03시의 TEMP 위험은 정렬을 지우지 않고도 줄일 수 있다. 적재 시각을 TEMP를 많이 쓰는 다른 두 배치보다 앞인 02:30으로 옮기면 work area 상한이 약 100MB라 81MB 정렬이 메모리 안에서 끝나고(72M (0)), TEMP를 쓰지 않으며 적재는 9.6초 → 약 3초가 된다. 순서를 지키려면 직렬로 돌린다. 병렬 직접 경로 적재는 서버마다 따로 블록을 채워 순서가 흩어진다.

해설

정렬은 사라지지 않고 자리를 옮길 뿐이다: 적재 순서를 버리고 화면 칸까지 담은 커버링 인덱스 (ITM_NO, SLE_DT, SLE_QTY, SLE_UPRC)로 테이블 액세스를 없애는 길도 있다. 하지만 인덱스를 만들 때도 110만 줄을 정렬하고, 테이블의 절반 가까운 인덱스를 매일 밤 다시 짓는다.
ATTRIBUTE CLUSTERING: ALTER TABLE CF11_ANL ADD CLUSTERING BY LINEAR ORDER (ITM_NO, SLE_DT)를 걸면 직접 경로 적재가 스스로 그 순서로 정렬해 쌓는다. SQL에서 ORDER BY를 지워도 순서가 유지되지만, 정렬 비용은 같고 순서를 지키는 장치가 테이블 정의로 옮겨 갈 뿐이다.
한 표는 한 가지 순서로만 쌓을 수 있다. 판매일로 범위를 읽는 화면이 새로 붙으면 그쪽 CF가 나빠지므로, 순서를 정할 때는 그 표를 읽는 SQL을 V$SQL_PLAN으로 모두 확인한다(교재 04화 §4, 적재 순서로 CF를 처음 바로잡는 이야기는 ACC-024).
일괄 지침은 판단의 근거를 지운다. 같은 ORDER BY라도 읽는 쪽이 순서에 기대는지에 따라 답이 다르므로, 지침에는 '순서에 기대는 읽기가 없을 때'라는 조건을 붙인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황