EXPLAIN PLAN에는 NL 조인인데 실행 통계에는 주문상품을 통째로 읽은 흔적이 있다

기초SELECT버거639 / 700

상황

본사 「주간 상품 판매」 리포트는 한 주의 주문을 상품별로 묶는다. 마케팅팀이 월요일마다 지난주를 넣어 열고, 한 번에 0.6초쯤 걸린다. 조민우가 EXPLAIN PLAN을 떠 보고 말했다. "주문 9천 건마다 주문상품 PK를 찾아가는 NL이에요. 해시 조인으로 바꾸게 힌트를 넣죠." 그런데 DBA가 보낸 V$SQL에는 이 SQL이 한 번에 55,523블록을 읽고 대부분이 디스크 읽기라고 나와 있다. NL로 9천 건을 찾아간 일량으로는 보이지 않는다.

테이블과 인덱스

ORD: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) · 하루 약 1,370건 · ORD_X01 (ORD_DT, ORD_HMS)(날짜순 적재, CF 22,223) · ORD_PK (ORD_NO)
ORD_ITEM: 2,472,717행 · 55,265블록 · 주문당 약 2.47줄 · ORD_ITEM_PK (ORD_NO, ITEM_ID)
통계: ORD는 2013년 7월 1일에 모은 통계 그대로다(NUM_ROWS 749,520 · ORD_DT 최댓값 '20130630'). ORD_ITEM은 최신이다.
optimizer_adaptive_plans 기본값(TRUE)

원본 SQL

sql

-- 조민우 안 : 해시 조인으로 고정한다
SELECT /*+ LEADING(o i) USE_HASH(i) */
       i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS QTY, COUNT(*) AS LINE_CNT
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20131216' AND '20131222'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY i.ITEM_ID

실행계획 학습용 예시

xplan

[조민우의 EXPLAIN PLAN · 힌트 없는 원래 SQL]
-----------------------------------------------------------------------------
| Id  | Operation                              | Name        | Rows  | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |             |    92 |  6243 |
|   1 |  HASH GROUP BY                         |             |    92 |  6243 |
|   2 |   NESTED LOOPS                         |             |  3384 |  6231 |
|   3 |    NESTED LOOPS                        |             |  3384 |  6231 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| ORD         |  1370 |    38 |
|*  5 |      INDEX RANGE SCAN                  | ORD_X01     |  1370 |     7 |
|*  6 |     INDEX RANGE SCAN                   | ORD_ITEM_PK |     2 |     3 |
|   7 |    TABLE ACCESS BY INDEX ROWID         | ORD_ITEM    |     2 |     2 |
-----------------------------------------------------------------------------
Note
-----
   - this is an adaptive plan

[DBA가 뜬 실제 실행 · DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST +ADAPTIVE') · 힌트 없는 원래 SQL]
Plan hash value: 2961807453

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |      1 |        |     87 |00:00:00.61 |   55523 |  55265 |
|   1 |  HASH GROUP BY                          |             |      1 |     92 |     87 |00:00:00.61 |   55523 |  55265 |
|*  2 |   HASH JOIN                             |             |      1 |   3384 |  23690 |00:00:00.58 |   55523 |  55265 |
|-  3 |    NESTED LOOPS                         |             |        |   3384 |        |            |         |        |
|-  4 |     NESTED LOOPS                        |             |        |   3384 |        |            |         |        |
|-  5 |      STATISTICS COLLECTOR               |             |        |        |        |            |         |        |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| ORD        |      1 |   1370 |   9590 |00:00:00.01 |     253 |      0 |
|*  7 |        INDEX RANGE SCAN                 | ORD_X01     |      1 |   1370 |   9590 |00:00:00.01 |      38 |      0 |
|-* 8 |      INDEX RANGE SCAN                   | ORD_ITEM_PK |        |      2 |        |            |         |        |
|-  9 |     TABLE ACCESS BY INDEX ROWID         | ORD_ITEM    |        |      2 |        |            |         |        |
|  10 |    TABLE ACCESS FULL                    | ORD_ITEM    |      1 |      2 |   2472K|00:00:00.35 |   55270 |  55265 |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("I"."ORD_NO"="O"."ORD_NO")
   7 - access("O"."ORD_DT">='20131216' AND "O"."ORD_DT"<='20131222')
   8 - access("I"."ORD_NO"="O"."ORD_NO")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

문제

EXPLAIN PLAN과 실제 실행의 계획이 다른 이유를 설명하시오. 실제로 어느 줄들이 돌았는지, 실행 중에 무엇이 조인 방법을 바꿨는지 쓰시오.
조민우의 USE_HASH 힌트가 필요한지 판단하고, 대신 할 일을 SQL로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 힌트는 넣지 않는다 : 이번 주처럼 큰 범위는 적응적 계획이 이미 해시 조인으로 돌았다
SELECT i.ITEM_ID, SUM(i.ORD_ITEM_QTY) AS QTY, COUNT(*) AS LINE_CNT
  FROM ORD o, ORD_ITEM i
 WHERE o.ORD_DT BETWEEN '20131216' AND '20131222'
   AND i.ORD_NO = o.ORD_NO
 GROUP BY i.ITEM_ID;

-- 실제로 돈 계획은 이렇게 본다 ('-' 가 없는 줄만 돌았다)
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST +ADAPTIVE'));

-- 기본 계획이 NL 로 서게 만든 원인 : 2013-06-30 에 멈춘 ORD 통계를 지금 데이터로 모은다
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD');

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

xplan

Plan hash value: 2961807453

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |     87 |00:00:00.60 |   55523 |  55265 |
|   1 |  HASH GROUP BY                       |          |      1 |     92 |     87 |00:00:00.60 |   55523 |  55265 |
|*  2 |   HASH JOIN                          |          |      1 |  23677 |  23690 |00:00:00.57 |   55523 |  55265 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |   9590 |   9590 |00:00:00.01 |     253 |      0 |
|*  4 |     INDEX RANGE SCAN                 | ORD_X01  |      1 |   9590 |   9590 |00:00:00.01 |      38 |      0 |
|   5 |    TABLE ACCESS FULL                 | ORD_ITEM |      1 |   2472K|   2472K|00:00:00.35 |   55270 |  55265 |
------------------------------------------------------------------------------------------------------------------

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

모범 답안 서술

EXPLAIN PLAN이 보여 준 것은 적응적 계획의 기본 계획(NL)이다. Note의 this is an adaptive plan이 그 표시다. 실제 실행을 +ADAPTIVE로 펼치면 '-'가 붙은 3, 4, 5, 8, 9번 줄은 돌지 않은 비활성 줄이고, 실제로 돈 것은 2번 해시 조인과 6, 7번(ORD를 날짜로 읽기), 10번(ORD_ITEM 전체 스캔)이다. 옵티마이저는 7번 줄의 주문을 1,370건으로 보고 NL을 기본으로 골랐지만, 해시 조인 쪽 줄도 함께 넣어 두었다. 실행 중에는 5번 STATISTICS COLLECTOR가 6번 줄에서 올라오는 행을 세다가, NL과 해시의 비용이 같아지는 문턱을 넘자 해시 조인으로 바꿨다. 실제 주문은 9,590건이었다. 그래서 ORD_ITEM을 한 번 통째로 읽었고(10번 줄 55,270블록, 대부분 디스크) 9천 번 찾아간 흔적은 없다. 1,370은 ORD 통계에서 나왔다. 통계가 2013년 6월 30일에 멈춰 있어 12월의 한 주는 통째로 최댓값 밖이고, 오라클은 이런 범위를 하루치(749,520 ÷ 547 = 1,370)로 본다. USE_HASH 힌트는 필요 없다. 이번 주 범위에서는 적응적 계획이 이미 해시 조인으로 돌았다. 힌트를 박으면 적응을 끄고 조인 방법을 고정하므로, 같은 리포트로 매장 하나나 하루를 볼 때 NL이 맞는데도 해시와 ORD_ITEM 전체 스캔을 강제한다. 대신 기본 계획을 틀리게 만든 원인인 ORD 통계를 다시 모은다. 그러면 7번 줄을 9,590건으로 보고 처음부터 해시 조인을 고르며, 일량은 같다(Buffers 55,523, 계획 해시 2961807453).

해설

실행계획을 어디서 보느냐에 따라 다른 계획이 보인다. EXPLAIN PLAN과 DISPLAY_CURSOR 기본 형식은 적응적 계획의 한쪽만 보여 준다. 실제로 돈 줄은 ALLSTATS LAST +ADAPTIVE로 펼쳐 '-'가 없는 줄만 읽는다. Note의 this is an adaptive plan 한 줄이 신호다(교재 08화 §5).
적응적 계획은 조인 방법만 바꾼다. 조인 순서와 테이블을 읽는 방법(인덱스로 갈지 FULL로 갈지)은 파싱 때 정한 그대로다. 그리고 첫 실행에서 정해진 최종 계획(V$SQL.IS_RESOLVED_ADAPTIVE_PLAN = Y)은 그 커서가 살아 있는 동안 다시 바뀌지 않는다.
힌트는 그날의 규모로 내린 판단이다. 지난주처럼 큰 범위에서는 결과가 같지만, 힌트는 범위가 작아진 날에도 그대로 적용된다. 힌트를 넣는다면 유효기간과 재검토 조건을 대장에 적는다(교재 06화).
블록을 줄이는 길은 따로 있다. 남은 55,523블록은 ORD_ITEM 전체 스캔 몫이다. ORD_ITEM에도 ORD_DT가 있으므로 i.ORD_DT BETWEEN '20131216' AND '20131222'를 함께 주면 ORD_ITEM_X01로 그 주의 줄만 읽는다(주문과 주문상품의 날짜가 늘 같은지 먼저 확인한다). 다만 날짜 조건이 두 테이블에서 두 번 곱해져 조인 결과의 E-Rows가 작아지는 부작용이 있다(OPT-053).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황