JOIN-004

읽는 블록 수는 정상인데 연간 리포트가 6초 걸리고 TEMP 경보가 울린다

중급SELECT버거154 / 700

상황

본사 마케팅팀의 「연간 매장 · 상품별 판매 수량」은 올해 처음 만든 리포트다. 2013년 한 해를 조회하면 6초가 걸리고, 돌릴 때마다 DBA에게 TEMP 사용 경보가 간다. 오과장은 "Buffers는 두 테이블을 한 번씩 읽은 만큼이라 정상이다. 서버 메모리를 늘려야 하나?"라고 묻는다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 2013년 500,218행 · SHOP_NO 200개 매장 · 통계 최신 (지난주)
ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · ORD_ITEM_PK (ORD_NO, ITEM_ID) · 주문당 평균 2.47줄
ORD_ITEM의 통계는 2012년 1월 오픈 첫 주(NUM_ROWS 24,580 · BLOCKS 545)에 모은 뒤 잠겨 있다 (DBMS_STATS.LOCK_TABLE_STATS, 그때 "야간 통계가 계획을 흔든다"는 민원이 있었다)
pga_aggregate_target은 512M이고, 해시 조인 하나가 쓸 수 있는 work area는 그보다 훨씬 작다

원본 SQL

sql

SELECT o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO, i.ITEM_ID

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |  18400 |00:00:06.12 |   77687 |       |       |          |         |
|   1 |  HASH GROUP BY      |          |      1 |  12290 |  18400 |00:00:06.12 |   77687 |  2152K|  2152K| 3164K (0)|         |
|*  2 |   HASH JOIN         |          |      1 |  12290 |   1236K|00:00:05.71 |   77687 |   176M|    12M|   98M (1)|      96M|
|   3 |    TABLE ACCESS FULL| ORD_ITEM |      1 |  24580 |   2472K|00:00:00.58 |   55265 |       |       |          |         |
|*  4 |    TABLE ACCESS FULL| ORD      |      1 |    500K|    500K|00:00:00.31 |   22422 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   4 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

문제

두 테이블을 한 번씩 읽었으므로 Buffers는 정상이다. 그런데도 6초가 걸리는 까닭을 실행계획의 어느 줄, 어느 열에서 알 수 있는지 설명하시오.
옵티마이저가 이 build를 고른 이유를 E-Rows와 A-Rows로 설명하고, 근본 해결과 임시 해결(통계 변경 승인 전)을 SQL로 작성하시오. 메모리를 늘리자는 제안에 대한 의견도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 근본 : 통계를 지금 크기로 (잠금을 풀고 모은다 — 잠근 이유였던 '계획 흔들림' 은 SQL Plan Baseline 으로 지킨다)
EXEC DBMS_STATS.UNLOCK_TABLE_STATS('DEV', 'ORD_ITEM');
EXEC DBMS_STATS.GATHER_TABLE_STATS('DEV', 'ORD_ITEM');

-- 통계 변경 승인 전의 임시 해결 : build 를 작은 쪽(2013년 주문 50만 행)으로 고정한다
SELECT /*+ LEADING(o i) USE_HASH(i) */
       o.SHOP_NO, i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS qty
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20130101' AND '20131231'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY o.SHOP_NO, i.ITEM_ID
-- 같은 뜻 : USE_HASH(o i) SWAP_JOIN_INPUTS(o) — 조인 순서는 두고 build 만 ORD 로 바꾼다

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

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |  18400 |00:00:01.52 |   77687 |       |       |          |
|   1 |  HASH GROUP BY      |          |      1 |  18400 |  18400 |00:00:01.52 |   77687 |  2152K|  2152K| 3164K (0)|
|*  2 |   HASH JOIN         |          |      1 |   1236K|   1236K|00:00:01.17 |   77687 |    37M|  6418K|   39M (0)|
|*  3 |    TABLE ACCESS FULL| ORD      |      1 |    500K|    500K|00:00:00.24 |   22422 |       |       |          |
|   4 |    TABLE ACCESS FULL| ORD_ITEM |      1 |   2472K|   2472K|00:00:00.45 |   55265 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   3 - filter(("O"."ORD_DT">='20130101' AND "O"."ORD_DT"<='20131231'))

모범 답안 서술

두 계획 모두 Buffers가 77,687(ORD 22,422 + ORD_ITEM 55,265)로, 읽은 블록은 같다. 시간을 설명하는 것은 2번 줄 HASH JOIN의 메모리 열이다. 해시 조인은 첫 번째 자식(3번 줄)을 build 입력으로 삼아 PGA에 올리는데, 원본은 ORD_ITEM 247만 행을 build로 잡았다. OMem 176M이 work area에 들어가지 않아 Used-Mem이 98M (1), 곧 one-pass로 돌았다. 넘친 build 파티션과 그 짝인 probe 파티션 96M(Used-Tmp)을 TEMP에 썼다가 다시 읽었고, 6초의 대부분이 이 디스크 왕복에 들었다. 큰 쪽을 build로 잡은 까닭은 3번 줄에 있다. E-Rows는 24,580인데 A-Rows는 2,472K다. ORD_ITEM의 통계가 오픈 첫 주(24,580행)에 멈춘 채 잠겨 있어서, 옵티마이저는 ORD_ITEM이 2013년 주문(E-Rows 500K)보다 훨씬 작다고 믿었다. 해시 조인은 작은 쪽을 build로 잡으므로, 옵티마이저가 믿은 숫자로는 옳은 선택이었다. 근본 해결은 통계를 지금 크기로 다시 모으는 것이다(잠금을 풀고 GATHER). 그러면 옵티마이저가 스스로 작은 쪽인 2013년 주문 50만 행을, 그것도 ORD_NO와 SHOP_NO 두 컬럼만 build로 올린다. 통계 변경 승인 전이라면 LEADING(o i) USE_HASH(i)나 SWAP_JOIN_INPUTS(o)로 build를 바꾼다. 그러면 Used-Mem은 98M (1) → 39M (0)으로 optimal이 되고, TEMP는 0, 시간은 6.1초 → 1.5초로 준다. 메모리 증설은 답이 아니다. 블록 수는 그대로이고, build만 바로잡으면 지금 메모리 안에서 optimal로 끝난다. 통계를 잠근 이유(계획이 흔들린다)는 잠금이 아니라 SQL Plan Baseline으로 해결한다. 통계를 잠가 두면 데이터가 100배로 늘어도 옵티마이저는 그 사실을 모른다.

해설

T0의 사각지대: Buffers만 보면 두 계획이 같다. 해시 조인이나 정렬이 있는 계획에서는 Used-Mem의 괄호(패스 수)와 Used-Tmp를 함께 본다 (교재 06화 §6, 09화).
힌트는 임시다: LEADING(o i)와 SWAP_JOIN_INPUTS(o)는 오늘의 크기를 보고 내린 판단이다. 통계를 고친 뒤 옵티마이저가 같은 build를 고르는지 확인하고 힌트를 뗀다.
잠긴 통계 찾기: SELECT table_name, stattype_locked, last_analyzed, num_rows FROM user_tab_statistics WHERE stattype_locked IS NOT NULL로 찾는다. 통계가 잠긴 채 실제 행 수와 크게 달라진 테이블은 다른 SQL에서도 같은 오판을 부른다 (교재 10화, 18화 SPM).
ORD_ITEM에도 ORD_DT가 있다. i.ORD_DT BETWEEN …을 더하면 probe 쪽 행이 절반으로 줄지만, 두 컬럼 값이 늘 같은지(반정규화 컬럼의 정합성)를 먼저 대조해야 한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황