힌트 대장 정리 배포를 앞둔 리허설에서 세 SQL 가운데 하나만 느려졌다

실전SELECT한빛유통632 / 700

상황

06화에서 만든 힌트 유효기간 대장에 재검토 날짜가 지난 줄이 셋 있다. 박팀장의 방침은 18화 그대로다. "계획을 고르는 일은 옵티마이저에, 지키는 일은 베이스라인에." 그래서 작년에 세 SQL은 힌트가 박힌 글자 그대로 커서 캐시에서 베이스라인으로 올려 두었다(ACCEPTED YES). 조민우가 시험 DB(운영 복제, 베이스라인까지 옮김)에서 힌트를 뗀 판으로 리허설을 했다. Q1과 Q2는 괜찮았는데 Q3만 느려졌다. 조민우는 고개를 갸웃했다. "베이스라인이 있는데 왜 계획이 바뀌죠?"

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객당 500건 · ORD_DT 2022-01-01 ~ 2024-12-31
인덱스: IX_ORD_BIG_DT (ORD_DT)(클러스터링 팩터 1,000,000) · IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT)(02화) · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)
M_CUS: 2,000행 · 30블록 · CUS_GD A 1%(20명) · B 9% · C 90% · CUS_GD에 히스토그램 없음(통계 정책 SIZE 1)
힌트 대장: Q1 INDEX(o IX_ORD_BIG_CUS_DT) 2019 · Q2 INDEX(o IX_ORD_BIG_DT) 2019(그때는 하루 범위 화면) · Q3 LEADING(c o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) 2021

원본 SQL

sql

-- Q1 「고객 주문 이력」 (하루 8,000번)
SELECT /*+ INDEX(o IX_ORD_BIG_CUS_DT) */ o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :cus
 ORDER BY o.ORD_DT DESC;

-- Q2 「기간 매출 합계」 (하루 300번 · 지금은 한 달 범위)
SELECT /*+ INDEX(o IX_ORD_BIG_DT) */ COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to;

-- Q3 「VIP 최근 30일 주문」 (하루 2,000번)
SELECT /*+ LEADING(c o) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT) */
       c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= :v_from;

실행계획 학습용 예시

xplan

[리허설 — 시험 DB (운영 복제 · 베이스라인 포함)]
          힌트 있는 판 (지금 운영)                                힌트 뗀 판
 Q1   INDEX RANGE SCAN DESCENDING · PHV 2845019935 · 503      같은 PHV 2845019935 · 503
 Q2   IX_ORD_BIG_DT + 테이블 · 27,466                          IX_ORD_BIG_DT_AMT · 91
 Q3   NL (IX_ORD_BIG_CUS_DT) · 364 · Note: baseline used      HASH JOIN + T_ORD_BIG FULL · 5,856 · Note 없음

[V$SQL — Q3 두 판]
 판          SQL_ID          EXACT_MATCHING_SIGNATURE   PLAN_HASH_VALUE  SQL_PLAN_BASELINE
 힌트 있음   1q7xk2m9d4f0b   11820941736520384410       1730592648       SQL_PLAN_a4n2c8w0x1v9k5e2d7c31
 힌트 없음   8d3m0v6s2k9wq    4467302185903377719       3907114725       (없음)

[Q3 — 힌트 뗀 판 · 리허설]
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |    274 |00:00:00.11 |    5856 |
|*  1 |  HASH JOIN                   |                   |      1 |   9145 |    274 |00:00:00.11 |    5856 |
|*  2 |   TABLE ACCESS FULL          | M_CUS             |      1 |    667 |     20 |00:00:00.01 |      30 |
|*  3 |   TABLE ACCESS FULL          | T_ORD_BIG         |      1 |  27420 |  27397 |00:00:00.09 |    5826 |
-----------------------------------------------------------------------------------------------

[Q3 — 힌트 있는 판 · 지금 운영]
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |    274 |00:00:00.01 |     364 |
|   1 |  NESTED LOOPS                |                   |      1 |   9145 |    274 |00:00:00.01 |     364 |
|   2 |   NESTED LOOPS               |                   |      1 |   9145 |    274 |00:00:00.01 |      90 |
|*  3 |    TABLE ACCESS FULL         | M_CUS             |      1 |    667 |     20 |00:00:00.01 |      30 |
|*  4 |    INDEX RANGE SCAN          | IX_ORD_BIG_CUS_DT |     20 |     14 |    274 |00:00:00.01 |      60 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |    274 |     14 |    274 |00:00:00.01 |     274 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   (뗀 판)   1 - access("O"."CUS_ID"="C"."CUS_ID")  2 - filter("C"."CUS_GD"='A')  3 - filter("O"."ORD_DT">=:V_FROM)
   (있는 판) 3 - filter("C"."CUS_GD"='A')  4 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=:V_FROM)

문제

리허설 결과를 근거로 세 힌트를 각각 어떻게 처리할지 정하시오.
Q3에 베이스라인이 있는데도 계획이 바뀐 이유를 베이스라인이 문장을 알아보는 방식으로 설명하고, 힌트를 떼고도 지금 계획을 지키도록 배포 전에 할 일을 SQL로 쓰시오.
이 방식의 부작용과 유지 방법, Q3에 힌트가 필요했던 진짜 원인을 고치는 길을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- [배포 전] Q3 : 힌트 있는 판(운영 커서 캐시)의 계획을, 힌트 없는 글자의 베이스라인으로 옮긴다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         sql_id          => '1q7xk2m9d4f0b',          -- 힌트 있는 판
         plan_hash_value => 1730592648,               -- NL (IX_ORD_BIG_CUS_DT)
         sql_text        => 'SELECT c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
                               FROM M_CUS c, T_ORD_BIG o
                              WHERE c.CUS_GD = ''A''
                                AND o.CUS_ID = c.CUS_ID
                                AND o.ORD_DT >= :v_from');
END;
/

-- [배포] 세 SQL 의 힌트를 뗀다
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :cus
 ORDER BY o.ORD_DT DESC;

SELECT COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= :v_from
   AND o.ORD_DT <  :v_to;

SELECT c.CUS_ID, c.CUS_NM, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= :v_from;

-- [배포 뒤] Q3 새 글자의 Note 에 baseline used 확인 → 힌트 있는 옛 글자의 베이스라인 셋은 지운다
-- EXEC :n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&old_handle');
-- Q1 · Q2 는 첫 실행 뒤 지금 계획을 LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => 새 SQL_ID)로 올린다 (방침)

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

xplan

[Q3 — 힌트 뗀 판 · 베이스라인을 옮긴 뒤]
-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |    274 |00:00:00.01 |     364 |
|   1 |  NESTED LOOPS                |                   |      1 |   9145 |    274 |00:00:00.01 |     364 |
|   2 |   NESTED LOOPS               |                   |      1 |   9145 |    274 |00:00:00.01 |      90 |
|*  3 |    TABLE ACCESS FULL         | M_CUS             |      1 |    667 |     20 |00:00:00.01 |      30 |
|*  4 |    INDEX RANGE SCAN          | IX_ORD_BIG_CUS_DT |     20 |     14 |    274 |00:00:00.01 |      60 |
|   5 |   TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |    274 |     14 |    274 |00:00:00.01 |     274 |
-----------------------------------------------------------------------------------------------

Note
-----
   - SQL plan baseline SQL_PLAN_7bq4d1z8h0m2r3907a1c6 used for this statement

모범 답안 서술

세 힌트의 처리: Q1은 힌트를 떼도 같은 PHV(2845019935)라 옵티마이저가 이미 같은 결론이다. 힌트만 뗀다. Q2의 INDEX(o IX_ORD_BIG_DT)는 하루 범위 화면이던 2019년의 판단이다. 지금은 한 달 범위라 행마다 테이블을 찾아가 27,466블록을 읽고, 떼면 옵티마이저가 커버링 인덱스 IX_ORD_BIG_DT_AMT를 골라 91블록이다. 해로운 힌트이므로 뗀다(하루 300번이면 약 824만 → 2만 7천 블록). Q3는 떼면 364 → 5,856블록으로 나빠지므로 계획을 지켜야 한다. Q3의 계획이 바뀐 이유: SQL plan baseline은 정규화한 SQL 글자(대소문자와 공백을 정리한 텍스트)의 서명에 붙는다. 힌트도 그 글자의 일부다. V$SQL에서 두 판의 EXACT_MATCHING_SIGNATURE가 다르므로, 힌트 있는 글자에 붙은 베이스라인은 힌트 없는 글자를 알아보지 못한다. 힌트 없는 판은 베이스라인이 없는 새 문장이라 옵티마이저가 고른 해시가 그대로 돌았다(Note 없음). 배포 전에 할 일: 힌트 있는 판의 커서(SQL_ID 1q7xk2m9d4f0b, PHV 1730592648)를 DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE로 올리되 sql_text에 힌트 없는 글자를 넘긴다. 그러면 힌트 없는 글자의 서명으로 베이스라인이 새로 생기고, 그 안에 힌트로 만든 NL 계획이 받아들인 계획으로 들어간다. 배포 뒤 Q3의 Note에 SQL plan baseline ... used가 뜨고 364블록인지 확인한 다음, 힌트 있는 옛 글자의 베이스라인 셋은 지운다. 부작용과 유지: 베이스라인은 글자에 묶이므로 누가 Q3의 공백이 아닌 글자(주석, 힌트, 컬럼 순서)를 고치면 다시 옮겨야 한다. 힌트가 소스에서 사라지는 대신 계획의 이유는 딕셔너리 안으로 들어가므로, 힌트 대장의 Q3 줄을 베이스라인 대장으로 옮겨 "2021년 힌트의 계획을 옮김, 그때의 규모, 다시 볼 조건"을 적는다. 대기 줄에 해시가 쌓이면 정기 점검에서 진화로 시험한다. Q3의 진짜 원인: M_CUS의 E-Rows가 667인데 A-Rows는 20이다. CUS_GD에 히스토그램이 없어 A, B, C를 똑같이 1/3(2,000 ÷ 3 = 667명)로 보았고, 667명의 주문이라면 T_ORD_BIG을 통째로 읽는 해시가 싸 보였다. 2021년의 힌트는 이 오추정을 덮고 있었다. M_CUS의 통계 선호도에 CUS_GD FREQUENCY 히스토그램을 더하면 20명으로 보아 옵티마이저가 스스로 NL을 고를 것이다. 그때는 대기 줄의 계획과 진화로 확인하고 베이스라인을 정리한다.

해설

순서가 중요하다: 힌트 있는 판이 운영 커서 캐시에 살아 있을 때 옮겨야 한다. 배포 뒤에는 그 커서가 밀려나 없을 수 있다. 그럴 때는 시험 DB에서 힌트 있는 판을 한 번 돌려 옮기고 PACK_STGTAB_BASELINE으로 운영에 가져간다.
다른 방법: 힌트 없는 글자를 먼저 한 번 돌려 베이스라인을 만든 뒤(해시), sql_handle로 NL을 더하고 해시는 ENABLED = NO로 내리는 길도 있다. 결과는 같지만 한 번은 느린 계획이 운영에서 돈다.
Q2를 지키지 않는 이유: 힌트 있는 글자의 베이스라인이 27,466블록짜리 계획을 지키고 있었다. 베이스라인은 '그때 받아들인 계획'이지 옳은 계획이 아니다(18화 §2.5).
CUS_GD 히스토그램은 M_CUS를 쓰는 다른 SQL의 계획도 바꾸므로 영향 분석과 함께 통계 수집 정책서에 올린다(OPT-002, 10화 정책서).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황