JOIN-032

혼자 돌리면 2초인 연간 상품 · 요일 리포트가 월초 아침에는 10초 가까이 걸린다

실전SELECT유통182 / 700

상황

매월 1일 07시에 본사 리포트 다섯 개가 한꺼번에 돌고, 같은 시각에 BI팀의 대량 추출도 시작된다. 그중 「연간 상품 · 요일 판매」 리포트는 낮에 혼자 돌리면 2초인데 월초 아침에는 10초 가까이 걸린다. 다섯 리포트는 모두 2023년 CF10 × CF11 조인을 묶는 기준만 달리한 SQL이다. 인프라팀은 pga_aggregate_target을 512M에서 1G로 올리자고 하고, 개발팀은 리포트 세션만 MANUAL로 크게 잡자고 한다. 오과장이 두 안을 포함해 비교 의견서를 내라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · 2023년 250,000건 (SLE_DT가 문자라 옵티마이저는 50,763건으로 본다, 03화)
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 2023년 판매상품 548,213줄
다섯 리포트: ① 상품 × 요일 (아래 SQL) ② 매장 × 월 ③ 상품 × 월 ④ 매장 × 요일 ⑤ 매장 × 상품. 조인과 기간은 모두 같다
V$PGASTAT: 낮 15시 global memory bound 104,857,600 · 1일 07시 02분 5,242,880 (total PGA allocated 509M, 그중 BI 추출 세션의 정렬이 약 350M)
통계 최신 · workarea_size_policy AUTO

원본 SQL

sql

SELECT b.ITM_NO, a.SLE_DOW, COUNT(*) AS sle_lines, SUM(b.SLE_QTY) AS sle_qty
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT BETWEEN '20230101' AND '20231231'
   AND b.SLE_NO = a.SLE_NO
 GROUP BY b.ITM_NO, a.SLE_DOW

실행계획 학습용 예시

xplan

[낮 15시 · 혼자]
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    616 |00:00:01.92 |   12787 |     0 |      0 |       |       |          |         |
|   1 |  HASH GROUP BY      |      |      1 |    616 |    616 |00:00:01.92 |   12787 |     0 |      0 |  1321K|  1321K| 1255K (0)|         |
|*  2 |   HASH JOIN         |      |      1 |    111K|    548K|00:00:01.71 |   12787 |     0 |      0 |    15M|  2420K|   16M (0)|         |
|*  3 |    TABLE ACCESS FULL| CF10 |      1 |  50763 |    250K|00:00:00.21 |    4583 |     0 |      0 |       |       |          |         |
|   4 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.63 |    8204 |     0 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------

[1일 07시 02분 · 리포트 다섯 개와 BI 추출이 함께]
---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |    616 |00:00:09.64 |   12787 |  1871 |   1871 |       |       |          |         |
|   1 |  HASH GROUP BY      |      |      1 |    616 |    616 |00:00:09.64 |   12787 |  1871 |   1871 |  1321K|  1321K| 1255K (0)|         |
|*  2 |   HASH JOIN         |      |      1 |    111K|    548K|00:00:09.42 |   12787 |  1871 |   1871 |    15M|  2420K| 5120K (1)|      15M|
|*  3 |    TABLE ACCESS FULL| CF10 |      1 |  50763 |    250K|00:00:00.24 |    4583 |     0 |      0 |       |       |          |         |
|   4 |    TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.66 |    8204 |     0 |      0 |       |       |          |         |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("B"."SLE_NO"="A"."SLE_NO")
   3 - filter(("A"."SLE_DT">='20230101' AND "A"."SLE_DT"<='20231231'))

문제

같은 계획, 같은 Buffers인데 07시에만 다섯 배 느린 이유를 두 실행계획의 메모리 열과 V$PGASTAT으로 설명하시오.
① pga_aggregate_target 증설 ② 리포트 세션만 MANUAL로 크게 ③ 리포트 실행 시각 나누기 ④ 다섯 리포트가 함께 쓰는 일 요약 테이블, 네 안을 효과와 대가로 비교하시오.
권하는 안을 SQL(DDL 포함)로 작성하고, 다섯 리포트 전체의 일량이 어떻게 바뀌는지 숫자로 정리하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ④ 다섯 리포트가 함께 쓰는 일 요약: 판매일 · 매장 · 상품 단위 (요일은 판매 헤더의 SLE_DOW 를 그대로 옮긴다)
CREATE TABLE CF_SLS_DAY (
  SLE_DT    VARCHAR2(8)  NOT NULL,
  SHP_NO    VARCHAR2(10) NOT NULL,
  ITM_NO    VARCHAR2(10) NOT NULL,
  SLE_DOW   VARCHAR2(1)  NOT NULL,
  SLE_LINES NUMBER       NOT NULL,
  SLE_QTY   NUMBER       NOT NULL,
  SLE_AMT   NUMBER       NOT NULL,
  CONSTRAINT CF_SLS_DAY_PK PRIMARY KEY (SLE_DT, SHP_NO, ITM_NO)
);

-- 처음 한 번: 2년 치를 한 번 조인해 채운다 (이 한 번만 큰 해시 조인을 한다. 07시가 아닌 시간에)
INSERT /*+ APPEND */ INTO CF_SLS_DAY
SELECT a.SLE_DT, a.SHP_NO, b.ITM_NO, a.SLE_DOW, COUNT(*), SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE b.SLE_NO = a.SLE_NO
 GROUP BY a.SLE_DT, a.SHP_NO, b.ITM_NO, a.SLE_DOW;
COMMIT;

-- 매일 06:30: 어제 하루치만 더한다 (CF10 하루 약 700건 · CF11_PK 범위 약 1,500줄)
INSERT INTO CF_SLS_DAY
SELECT a.SLE_DT, a.SHP_NO, b.ITM_NO, a.SLE_DOW, COUNT(*), SUM(b.SLE_QTY), SUM(b.SLE_QTY * b.SLE_UPRC)
  FROM CF10 a, CF11 b
 WHERE a.SLE_DT = :v_yday
   AND b.SLE_NO >= :v_yday
   AND b.SLE_NO <  :v_today
   AND b.SLE_NO = a.SLE_NO
 GROUP BY a.SLE_DT, a.SHP_NO, b.ITM_NO, a.SLE_DOW;
COMMIT;

-- 리포트 ① (나머지 넷도 같은 표에서 묶는 기준만 바꾼다)
SELECT ITM_NO, SLE_DOW, SUM(SLE_LINES) AS sle_lines, SUM(SLE_QTY) AS sle_qty
  FROM CF_SLS_DAY
 WHERE SLE_DT BETWEEN '20230101' AND '20231231'
 GROUP BY ITM_NO, SLE_DOW;

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

xplan

[리포트 ① · 요약 표에서]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |      1 |        |    616 |00:00:00.15 |    1812 |       |       |          |
|   1 |  HASH GROUP BY     |            |      1 |    616 |    616 |00:00:00.15 |    1812 |  1321K|  1321K| 1255K (0)|
|*  2 |   TABLE ACCESS FULL| CF_SLS_DAY |      1 |    154K|    155K|00:00:00.04 |    1812 |       |       |          |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SLE_DT">='20230101' AND "SLE_DT"<='20231231'))

[다섯 리포트 합계]  원본 5 × 12,787 = 63,935블록 · 해시 조인 work area 15M × 5
                    →  요약 표 5 × 약 1,812 = 약 9,060블록 · 해시 조인 없음 (GROUP BY 1M 남짓씩) · 06:30 적재 약 45블록

모범 답안 서술

원인: 두 실행은 계획도 Buffers(12,787)도 같다. 다른 것은 2번 줄 HASH JOIN의 메모리 열이다. 낮에는 build(2023년 판매 25만 건)에 필요한 15M(OMem)를 다 받아 16M (0), 곧 optimal로 끝났다. 07시에는 Used-Mem이 5,120K (1)로 onepass가 되어 TEMP에 15M를 쓰고 다시 읽었다(Reads/Writes 1,871). AUTO 정책에서 work area 하나가 받을 수 있는 크기(global memory bound)는 PGA를 함께 쓰는 세션이 많을수록 줄어든다. 07시에는 리포트 다섯 개와 BI 추출(정렬 약 350M)이 함께 돌아 total PGA allocated가 509M로 목표(512M)에 닿았고, bound가 100M에서 5M로 내려갔다. 혼자 돌리면 이 상황이 재현되지 않아 낮에 재면 늘 2초다. ① PGA 증설: 1G로 올리면 bound가 커져 이 리포트는 다시 optimal이 될 가능성이 높다. 하지만 BI 추출 같은 다른 세션의 정렬도 함께 커져 늘어난 몫을 먼저 가져갈 수 있고, 서버 메모리와 PGA_AGGREGATE_LIMIT까지 다시 봐야 한다. 데이터가 늘면 같은 일이 또 생긴다. 리포트 다섯 개가 같은 조인을 다섯 번 하는 구조는 그대로다. ② MANUAL: 리포트 세션에서 workarea_size_policy = MANUAL로 hash_area_size를 32M쯤 잡으면 이 리포트는 확실히 optimal이 된다. 대신 세션마다 고정 크기를 잡으므로 동시 세션 수와 그 세션의 정렬, 해시 수만큼 곱해지고, 인스턴스가 조절하지 못한다. 월초에 모두가 그렇게 하면 PGA 한도를 넘어 세션이 ORA-04036으로 끊길 수 있다. 2013년 한빛유통 배치의 반대 방향 실수다. ③ 시각 나누기: 리포트를 5분 간격으로 띄우거나 BI 추출을 06시로 옮기면 함께 도는 work area가 줄어 bound가 올라간다. 비용 없이 바로 할 수 있는 임시 처방이다. 하지만 다섯 리포트가 같은 조인을 다섯 번 하는 것은 그대로다(Buffers 5 × 12,787). ④ 일 요약 테이블: 다섯 리포트는 모두 같은 기간의 같은 조인을 묶는 기준만 달리한다. 판매일, 매장, 상품 단위로 한 번만 조인해 CF_SLS_DAY에 담아 두고, 다섯 리포트는 이 표에서 묶는다. 리포트 하나는 해시 조인 없이 표를 한 번 읽어 약 1,812블록, 0.15초다. 매일 06:30에는 어제 하루치만 더한다(CF10 하루 약 700건, CF11_PK 범위 약 1,500줄, 약 45블록). 권고: ④를 권하고, 표를 만들기까지는 ③을 쓴다. 다섯 리포트 전체로 보면 Buffers는 63,935 → 약 9,060블록이고, 07시에 15M짜리 해시 조인 다섯 개가 동시에 PGA를 다투던 일이 사라진다. 대가는 요약 표의 적재 단계와 정합성 관리다. 지난 판매가 정정되면 그날 행을 지우고 다시 만들어야 하고, 월마다 요약 표의 합계를 원장(CF11) 합계와 대조한다.

해설

혼자 잰 숫자의 함정: work area 크기는 그 순간 PGA를 함께 쓰는 세션에 따라 달라진다. 성능을 잴 때는 혼자 잰 값과 운영 시간대의 V$SQL_WORKAREA.LAST_EXECUTION(OPTIMAL · 1 PASS)을 함께 본다 (교재 09화 §6).
E-Rows의 과소 추정: 3번 줄 E-Rows 50,763은 문자 날짜 범위를 추정한 값으로, 실제의 5분의 1이다(03화 §2.2). build 쪽 크기를 작게 본 탓에 동시 실행 상황에서 더 불리해질 수 있다. 근본 처방은 03화의 가상 컬럼이나 날짜 타입이다.
다른 정답: 다섯 리포트를 GROUPING SETS로 묶어 한 SQL로 한 번만 조인하고, 결과를 리포트 표 다섯 개에 나눠 넣는 방법도 있다. 조인은 한 번이 되지만 묶는 기준이 늘 때마다 SQL이 커진다. 판매번호 앞 8자리가 판매일이라는 규칙을 CHECK와 FK로 보장한 뒤 CF10 조인을 빼는 방법은 요일을 판매일에서 다시 계산해야 하고 NLS 설정에 따라 요일 번호가 달라질 수 있어 권하지 않는다 (JOIN-020).
요약 표의 단위: 리포트가 쓰는 기준(판매일, 매장, 상품, 요일)을 모두 담는 가장 작은 단위로 만든다. 판매번호 단위로 만들면 조인을 옮긴 것일 뿐이고, 월 단위로 만들면 일별 리포트를 못 만든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황