반기마다 흔들리는 핵심 화면, 힌트로 박을까 프로파일을 받을까

실전SELECT한빛유통594 / 700

상황

반기 보고에 '핵심 화면의 계획 관리' 항목이 한 줄 들어간다. 등급별 매출(Q2)은 지난 반기에 통계 한 번(일요일 밤의 빈 테이블 통계) 때문에 1,026,852블록을 읽은 적이 있다. 오과장의 의견은 이렇다. "USE_HASH를 박아 두면 통계가 무슨 일을 해도 해시잖아요." 정하은은 다른 안을 냈다. "튜닝 어드바이저로 SQL 프로파일을 받으면 배포 없이 계획이 고정돼요. 옛 판에 돌렸더니 99.42%라고 나왔어요." 박팀장은 이렇게 정리했다. "무엇으로 지킬지, 근거를 붙여서."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 매주 일요일 재적재 · 인덱스 셋
M_CUS: 2,000행 · 30블록 · PK_M_CUS · M_RGN: 8행
Q2 rev01(06화, 힌트 없음): 좋은 계획 HASH JOIN 2292855265 · 5,868블록 / 나쁜 계획 NESTED LOOPS · 1,026,852블록
Q2 rev00(옛 판): /*+ LEADING(o c) USE_NL(c) */가 박혀 있던 2018년 소스
Oracle Free · CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING(운영 라이선스는 확인 중)
아래 측정은 복사본(T_ORD_BIG_C18)과 세션 설정, 메모리 설정에서만 했다.

원본 SQL

sql

-- 오과장 안 — Q2 rev01 에 조인 순서 · 방법을 박는다
SELECT /*+ LEADING(c o) USE_HASH(o) */
       c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
       MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD;

-- 정하은 안 — 튜닝 어드바이저의 권고 프로파일을 받는다 (배포 없음)
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'T18_R0_Q2', name => 'PROF_T18_Q2');

실행계획 실측 · 교재 18화(복사본)

xplan

[① 힌트 — 06화 회의록]
  2018.11  LEADING(o c) USE_NL(c) · "3초 → 0.4초" · 첨부 끝 줄 "측정 조건 : 주문 20,000 건"
  2024.04  같은 힌트 · 주문 1,000,000 건 → 1,026,849 블록 (힌트를 떼자 5,869)

[② SQL 프로파일 — 튜닝 어드바이저 (옛 판 rev00 에 돌림)]
  권고                    SQL PROFILE × 1 · Buffer Gets 1,026,849 → 5,868 (99.42 %)
  프로파일 안의 힌트      OPTIMIZER_FEATURES_ENABLE(default) · IGNORE_OPTIM_EMBEDDED_HINTS   (두 줄뿐)
  받은 뒤                 HASH JOIN · 5,869 · Note: SQL profile PROF_T18_Q2 used · 소스에는 USE_NL 이 그대로
  팩 설정을 DIAGNOSTIC 으로 (메모리에서만) 내리고 커서를 지운 뒤
                          에러 없음 · NESTED LOOPS · 1026K · Note 에서 프로파일 줄이 사라짐
  같은 설정에서 베이스라인  쓰인다 (SQL plan baseline used)

[③ SQL plan baseline — 복사본]
  장면 B (Q2 꼴)
   [4] 정상 통계 · HASH 를 커서 캐시에서 올림     HASH JOIN     5,863   MANUAL-LOAD · ACCEPTED YES
   [5] 빈 테이블 통계 (NUM_ROWS 0 · 실제 100만)   HASH JOIN     5,868   E-Rows 1 · Note: SQL plan baseline used
                                                                     · 대기 줄에 NL (AUTO-CAPTURE · ACCEPTED NO)
   [6] 내 세션만 베이스라인 끔                     NESTED LOOPS  1026K
   [7] 대기 줄의 NL 을 진화                        "failed the benefit criterion ... 0.33281 times worse" → 대기 줄에 남음
  장면 A (Q1 꼴 · 인덱스가 없을 때의 FULL 을 올림)
   [1] FULL 을 베이스라인으로                      TABLE ACCESS FULL   5,821
   [2] (ORD_DT, ORD_AMT) 인덱스를 만듦             여전히 FULL 5,821 · 대기 줄에 INDEX RANGE SCAN (ACCEPTED NO)
   [3] 진화                                        "passed the benefit criterion ... 63.70970 times better" → INDEX RANGE SCAN 91
  자동 진화 (Free · 루트)                          ORA-38153: Software edition is incompatible with SQL plan management.

문제

세 장치(힌트, SQL 프로파일, SQL plan baseline)를 '어디에 있는가, 새 계획이 더 나은지 검사하는가, 무엇이 그 장치를 꺼뜨리는가'의 세 기준으로 비교하시오.
반기마다 일어나는 통계 변화로부터 Q2를 지킬 방법을 하나 고르고 SQL로 쓰시오.
고른 방법이 막지 못하는 것과, 그 방법을 유지하는 절차(누가, 언제, 무엇을 함께 적는지)를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- Q2 rev01 — 힌트를 박지 않는다 (계획을 고르는 일은 옵티마이저, 지키는 일은 베이스라인)
SELECT c.CUS_GD, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS amt,
       MAX((SELECT r.RGN_NM FROM M_RGN r WHERE r.RGN_ID = c.RGN_ID)) AS rgn_sample
  FROM T_ORD_BIG o, M_CUS c
 WHERE c.CUS_ID = o.CUS_ID
 GROUP BY c.CUS_GD;

-- ① 지금의 좋은 계획(HASH · 2292855265)을 커서 캐시에서 베이스라인으로 — 튜닝 팩이 아니다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => '&q2_sql_id',
                                             plan_hash_value => 2292855265);
END;
/

-- ② 반기 점검 (T8) — 대기 줄(ACCEPTED = NO)이 있으면 진화를 돌린다
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => '&q2_sql_handle');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => t);
END;
/
SELECT DBMS_SPM.REPORT_EVOLVE_TASK(task_name => '&evolve_task') FROM dual;
-- 리포트를 읽고, 통과한 계획만 : EXEC DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => '&evolve_task');

-- ③ 통계는 따로 지킨다 — 통계 수집 정책서 2호 (재적재 COMMIT 직후 수집) 그대로

모범 답안 서술

비교: 힌트는 코드 안에 있고, 새 계획이 더 나은지 검사하지 않고 그대로 실행된다. 2018년의 USE_NL은 주문 2만 건에서는 옳았지만 100만 건에서는 1,026,849블록을 읽었다. USE_HASH도 오늘 규모에 맞춘 판단이라 유효기간이 있다. SQL 프로파일은 코드 밖(딕셔너리)에 있지만 역시 검사 없이 붙는다. 정체는 IGNORE_OPTIM_EMBEDDED_HINTS 두 줄이다. 06화에서 사람이 한 일(힌트 떼기)을 코드 밖에서 한 것이라, 소스에는 USE_NL이 남아 다음 사람을 속인다. 무엇보다 튜닝 팩에 기댄다. 팩 설정을 내리자 프로파일은 에러 없이 무시되고 1026K로 돌아갔다. SQL plan baseline은 코드 밖에 있고 튜닝 팩 기능이 아니다(팩을 내려도 쓰였다). 옵티마이저가 새 계획을 만들어도 대기 줄(ACCEPTED NO)에 두고, 진화(시험 운행)로 실제로 돌려 더 나을 때만 받아들인다. 빈 테이블 통계의 NL은 0.33배로 거절됐고, 인덱스 계획은 63.7배로 통과했다. 고른 것: 베이스라인이다. Q2 rev01은 힌트 없이 두고, 지금의 해시 계획(2292855265)을 커서 캐시에서 베이스라인으로 올린다. 막지 못하는 것: ① 베이스라인은 통계를 고치지 않는다. [5]에서도 E-Rows는 1이었고, 같은 테이블을 읽는 다른 SQL(Q1 등)은 여전히 틀린 통계를 본다. 그래서 통계 수집 정책서(재적재 직후 수집)는 그대로 필요하다. ② 좋은 새 계획도 막는다. 인덱스를 만들어도 진화 전까지는 FULL이었다(장면 A). ③ 베이스라인은 '옳은 계획'이 아니라 그때의 데이터에서 시험을 통과한 계획이다. 고객이 2만 명이 되어 다른 계획이 이기는 날이 와도, 진화를 돌리지 않으면 해시가 계속 돈다. 유지 절차: Free에는 자동 진화가 없다(ORA-38153). 그래서 반기 점검(T8)에 '대기 줄이 있는 베이스라인 → 진화 → 리포트를 읽고 IMPLEMENT' 절차를 넣고 튜너가 돌린다. 베이스라인 대장에는 그때의 규모(주문 100만, 고객 2,000)와 재검토 조건을 함께 적는다. 결정만 적으면 다음 사람에게는 결정만 전해진다. 프로파일은 소스를 고칠 수 없는 패키지 제품이나 긴급 조치에만 쓰고, 쓸 때는 정체와 팩 확인란, 코드로 고칠 날짜를 적는다.

해설

오과장 안이 이기는 날도 있다. 힌트는 팩도 딕셔너리도 필요 없고 소스만 보면 보인다. 그러나 눈에 보인다고 해서 '검사했다'는 뜻은 아니다. 힌트를 박는다면 힌트 유효기간 대장에 규모와 재검토 조건을 함께 적는다(06화).
FIXED = YES: 문서에는 고정 계획이 새 계획을 대기 줄에 더하지 않는다고 적혀 있다(18화에서는 재지 않았다). 진화라는 검사를 스스로 끄는 셈이라 기본으로는 쓰지 않는다.
ENABLED = NO로 내리면 베이스라인이 없는 것처럼 계획을 세운다. 18화 [8]에서 그 일요일 밤의 NL(1026K)이 그대로 돌아왔다.
튜닝 어드바이저는 빠른지를 본다. 금액이 50배로 틀린 Q3 rev00에는 권고가 없었다. 도구의 권고문은 검사필증이 아니다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 18화(복사본) ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 실측 · 교재 18화(복사본)

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재