JOIN-079

「지역 고객 오늘 주문」에 연간 누적 칸을 붙이고 나서 화면이 0.01초에서 1.7초가 됐다

중급SELECT한빛유통229 / 700

상황

CRM 화면의 「지역 고객 오늘 주문」은 지역 하나를 고르면, 그 지역 고객 가운데 오늘 주문한 고객마다 오늘 주문 건수와 금액을 보여 준다. 2022년에 외주 업체가 튜닝하면서 실행계획의 Outline에서 힌트 한 줄을 옮겨 SQL 맨 앞에 붙였다. 지난주 CRM팀이 "최근 12개월 누적 금액" 칸을 더해 달라고 했고, 개발자가 같은 모양의 인라인 뷰를 하나 더 만들어 FROM 절에 끼웠다. 새 뷰만 따로 돌리면 0.1초 남짓인데 화면 전체는 1.7초가 걸린다. 아래는 2024-06-14, 지역 RGN03으로 조회한 실행계획이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 하루 약 913건 · 최근 12개월 333,029행 · 적재 순서가 날짜와 무관하다
인덱스 IX_ORD_BIG_DT (ORD_DT): 리프 2,653블록 · 클러스터링 팩터 약 1,000,000
M_CUS: 2,000행 · 30블록 · RGN_ID 8종(지역마다 250명)
통계 최신. 변경 전 화면은 947블록 · 0.01초였다

원본 SQL

sql

SELECT /*+ INDEX(@SEL$2 o IX_ORD_BIG_DT) */
       c.CUS_ID, c.CUS_NM, t.ord_cnt, t.ord_amt, y.yr_amt
  FROM M_CUS c,
       (SELECT o.CUS_ID, SUM(o.ORD_AMT) AS yr_amt              -- 이번에 더한 뷰
          FROM T_ORD_BIG o
         WHERE o.ORD_DT >= ADD_MONTHS(:v_dt, -12) + 1
           AND o.ORD_DT <  :v_dt + 1
         GROUP BY o.CUS_ID) y,
       (SELECT o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
          FROM T_ORD_BIG o
         WHERE o.ORD_DT >= :v_dt
           AND o.ORD_DT <  :v_dt + 1
         GROUP BY o.CUS_ID) t
 WHERE c.RGN_ID = :rgn
   AND t.CUS_ID = c.CUS_ID
   AND y.CUS_ID = c.CUS_ID

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |      1 |        |     92 |00:00:01.71 |    335K |          |
|*  1 |  HASH JOIN                              |               |      1 |     91 |     92 |00:00:01.71 |    335K | 1180K (0)|
|*  2 |   HASH JOIN                             |               |      1 |     91 |     92 |00:00:00.01 |     947 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL                    | M_CUS         |      1 |    250 |    250 |00:00:00.01 |      30 |          |
|   4 |    VIEW                                 |               |      1 |    730 |    731 |00:00:00.01 |     917 |          |
|   5 |     HASH GROUP BY                       |               |      1 |    730 |    731 |00:00:00.01 |     917 | 1290K (0)|
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |      1 |    913 |    913 |00:00:00.01 |     917 |          |
|*  7 |       INDEX RANGE SCAN                  | IX_ORD_BIG_DT |      1 |    913 |    913 |00:00:00.01 |       4 |          |
|   8 |   VIEW                                  |               |      1 |   2000 |   2000 |00:00:01.70 |    334K |          |
|   9 |    HASH GROUP BY                        |               |      1 |   2000 |   2000 |00:00:01.70 |    334K | 1420K (0)|
|  10 |     TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG     |      1 |    333K|    333K|00:00:01.55 |    334K |          |
|* 11 |      INDEX RANGE SCAN                   | IX_ORD_BIG_DT |      1 |    333K|    333K|00:00:00.09 |     884 |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("Y"."CUS_ID"="C"."CUS_ID")
   2 - access("T"."CUS_ID"="C"."CUS_ID")
   3 - filter("C"."RGN_ID"=:RGN)
   7 - access("O"."ORD_DT">=:V_DT AND "O"."ORD_DT"<:V_DT+1)
  11 - access("O"."ORD_DT">=ADD_MONTHS(:V_DT,-12)+1 AND "O"."ORD_DT"<:V_DT+1)

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / C@SEL$1
   4 - SEL$3 / T@SEL$1
   5 - SEL$3
   6 - SEL$3 / O@SEL$3
   8 - SEL$2 / Y@SEL$1
   9 - SEL$2
  10 - SEL$2 / O@SEL$2

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
---------------------------------------------------------------------------

  10 -  SEL$2 / "O"@"SEL$2"
         -  INDEX(@SEL$2 o IX_ORD_BIG_DT)

문제

새 뷰를 더한 뒤 10번과 11번 줄이 인덱스로 33만 행을 읽게 된 이유를, 힌트의 @SEL$2가 무엇을 가리키는지로 설명하시오. Query Block 칸과 Hint Report를 근거로 쓰시오.
블록이 더 늘어나도 힌트가 의도한 블록에만 걸리도록 SQL을 고치시오. 고친 뒤의 일량과 남는 일량도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT /*+ INDEX(@TODAY o IX_ORD_BIG_DT) */
       c.CUS_ID, c.CUS_NM, t.ord_cnt, t.ord_amt, y.yr_amt
  FROM M_CUS c,
       (SELECT /*+ QB_NAME(YEAR12) */
               o.CUS_ID, SUM(o.ORD_AMT) AS yr_amt
          FROM T_ORD_BIG o
         WHERE o.ORD_DT >= ADD_MONTHS(:v_dt, -12) + 1
           AND o.ORD_DT <  :v_dt + 1
         GROUP BY o.CUS_ID) y,
       (SELECT /*+ QB_NAME(TODAY) */
               o.CUS_ID, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
          FROM T_ORD_BIG o
         WHERE o.ORD_DT >= :v_dt
           AND o.ORD_DT <  :v_dt + 1
         GROUP BY o.CUS_ID) t
 WHERE c.RGN_ID = :rgn
   AND t.CUS_ID = c.CUS_ID
   AND y.CUS_ID = c.CUS_ID
-- 블록마다 QB_NAME 으로 이름을 붙이고, 힌트는 번호(@SEL$2)가 아니라 이름(@TODAY)으로 가리킨다

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

xplan

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |      1 |        |     92 |00:00:00.16 |    6773 |          |
|*  1 |  HASH JOIN                              |               |      1 |     91 |     92 |00:00:00.16 |    6773 | 1180K (0)|
|*  2 |   HASH JOIN                             |               |      1 |     91 |     92 |00:00:00.01 |     947 | 1236K (0)|
|*  3 |    TABLE ACCESS FULL                    | M_CUS         |      1 |    250 |    250 |00:00:00.01 |      30 |          |
|   4 |    VIEW                                 |               |      1 |    730 |    731 |00:00:00.01 |     917 |          |
|   5 |     HASH GROUP BY                       |               |      1 |    730 |    731 |00:00:00.01 |     917 | 1290K (0)|
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG     |      1 |    913 |    913 |00:00:00.01 |     917 |          |
|*  7 |       INDEX RANGE SCAN                  | IX_ORD_BIG_DT |      1 |    913 |    913 |00:00:00.01 |       4 |          |
|   8 |   VIEW                                  |               |      1 |   2000 |   2000 |00:00:00.15 |    5826 |          |
|   9 |    HASH GROUP BY                        |               |      1 |   2000 |   2000 |00:00:00.15 |    5826 | 1420K (0)|
|* 10 |     TABLE ACCESS FULL                   | T_ORD_BIG     |      1 |    333K|    333K|00:00:00.11 |    5826 |          |
--------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   4 - TODAY  / T@SEL$1
   6 - TODAY  / O@TODAY
   8 - YEAR12 / Y@SEL$1
  10 - YEAR12 / O@YEAR12

모범 답안 서술

@SEL$2는 블록 번호다. 오라클은 이름을 붙이지 않은 블록에 문장 안에서 나오는 순서대로 SEL$1, SEL$2, SEL$3을 매긴다. 2022년 SQL에는 인라인 뷰가 오늘 주문 뷰 하나뿐이라 그 뷰가 SEL$2였고, 외주 업체가 Outline에서 옮긴 INDEX(@SEL$2 o IX_ORD_BIG_DT)는 그 뷰를 가리켰다. 이번에 FROM 절에서 오늘 뷰 앞에 12개월 뷰를 끼우자 번호가 하나씩 밀렸다. Query Block 칸을 보면 8 ~ 10번 줄(12개월 뷰 Y)이 SEL$2, 4 ~ 6번 줄(오늘 뷰 T)이 SEL$3이다. 새 뷰도 테이블 별칭을 o로 썼다. 그래서 힌트는 버려지지 않고 SEL$2의 o, 곧 12개월 뷰의 T_ORD_BIG을 찾아 적용됐다. Hint Report의 10 - SEL$2 / "O"@"SEL$2" 아래에 코드 없이 찍힌 줄이 힌트가 쓰였다는 뜻이다. 클러스터링 팩터가 약 1,000,000인 인덱스로 12개월 333,029행을 찾아가니 행마다 다른 블록을 읽어, 10번 줄에서만 약 33만 4천 블록을 읽었다. 새 뷰를 따로 돌릴 때는 이 힌트가 없어서 FULL로 0.1초 남짓이었다. 블록마다 QB_NAME으로 이름(TODAY, YEAR12)을 붙이고, 힌트는 @TODAY처럼 이름으로 가리킨다. 이름은 블록이 늘거나 순서가 바뀌어도 그대로다. 12개월 뷰는 힌트 없이 옵티마이저가 FULL을 고른다. Buffers는 335K → 6,773, 시간은 1.71 → 0.16초로 준다. 남는 일량은 12개월 뷰가 T_ORD_BIG을 한 번 FULL로 읽는 5,826블록이다. 지역 고객 250명분만 필요한데 고객 2,000명의 12개월을 모두 묶는다. 더 줄이려면 월별 고객 집계 테이블을 두는 방안을 따로 검토한다.

해설

오늘 뷰의 힌트는 원래 필요 없었다: 하루 913행이면 옵티마이저도 인덱스를 고른다. 2022년의 힌트는 해가 없어서 남아 있었고, 번호가 밀리자 해로운 힌트가 됐다. 힌트를 그냥 떼는 것도 정답이다. 남긴다면 이름으로 가리키고 대장에 전제("하루 범위")를 적는다 (교재 06화 §8).
Outline의 힌트를 옮길 때: +OUTLINE으로 본 힌트에는 @"SEL$2", "O"@"SEL$2" 같은 시스템 이름이 들어 있다. 그 SQL의 그 모양에서만 맞는 이름이다. 손으로 옮길 때는 QB_NAME을 먼저 붙인다.
블록 확인: DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST +ALIAS')로 Query Block 칸을 보면 어느 줄이 어느 블록인지 알 수 있다. 힌트를 단 SQL을 고칠 때는 이 칸이 바뀌는지 본다.
블록마다 같은 별칭(o)을 되풀이해 쓰는 습관 때문에 힌트가 엉뚱한 블록에 붙어도 오류가 나지 않았다. 블록마다 별칭을 달리 쓰면(o1, o2) 번호가 밀렸을 때 적어도 N으로 드러난다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황