반기 점검에서 진화 리포트 세 장을 받았는데 오과장은 통과한 것을 다 받아들이고 인덱스 정리도 대장대로 하자고 한다

실전PLSQL한빛유통647 / 700

상황

10월 반기 점검(T8) 날이다. 조민우가 대기 줄(ACCEPTED NO)이 있는 핵심 SQL 세 개에 진화 작업을 돌려 리포트를 가져왔다. 오과장은 결재 올릴 작업 목록을 내밀었다. "과제 셋을 IMPLEMENT로 받아들이고, 인덱스 정리 대장대로 11월에 IX_ORD_BIG_DT_SEQ도 지우죠. 대장에 사용 기록 0건이라고 적혀 있잖아요." 박팀장은 이렇게 말했다. "받아들일 것, 미룰 것, 지울 것을 하나씩 근거를 붙여서요."

테이블과 인덱스

T_ORD_STLM: 100,000행 · 640블록 · 2025년 7월에 IX_STLM_DT_AMT (STLM_DT, STLM_AMT)를 만들었다
T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 IX_ORD_BIG_DT · IX_ORD_BIG_DT_AMT · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)(2024년 4월, 09화)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID)
세 SQL의 베이스라인은 2023년 9월에 커서 캐시에서 올렸다. 그때는 IX_ORD_BIG_DT_SEQ와 IX_STLM_DT_AMT가 없었다.
A 「월 정산 합계」 하루 약 200번 · B 「등급별 정산 집계」 하루 약 50번 · C 「주문 목록 첫 페이지」(최신 10건) 하루 약 30번
2025-03-10 밤 T_ORD_STLM 재적재 중에 모인 빈 테이블 통계(NUM_ROWS 0)가 하루 동안 쓰였다

원본 SQL

sql

-- 오과장 안 ① 세 과제를 모두 받아들인다
DECLARE
  r NUMBER;
BEGIN
  r := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => 'SPM_T8_ALL');   -- A, B, C 를 한 과제로 돌렸다
END;
/

-- 오과장 안 ② 인덱스 정리 대장 2호대로 11월 정기 작업에서 지운다
DROP INDEX IX_ORD_BIG_DT_SEQ;

실행계획 학습용 예시

xplan

[DBA_SQL_PLAN_BASELINES · 대기 줄이 있는 셋]
 SQL  PLAN_NAME        ORIGIN                         ACCEPTED  CREATED   계획
 A    SQL_PLAN_…a1     MANUAL-LOAD-FROM-CURSOR-CACHE  YES       23-09-04  TABLE ACCESS FULL T_ORD_STLM
 A    SQL_PLAN_…a2     AUTO-CAPTURE                   NO        25-07-21  INDEX RANGE SCAN IX_STLM_DT_AMT
 B    SQL_PLAN_…b1     MANUAL-LOAD-FROM-CURSOR-CACHE  YES       23-09-04  HASH JOIN (M_CUS · T_ORD_STLM FULL)
 B    SQL_PLAN_…b2     AUTO-CAPTURE                   NO        25-03-10  NESTED LOOPS (T_ORD_STLM → PK_M_CUS → M_CUS)
 C    SQL_PLAN_…c1     MANUAL-LOAD-FROM-CURSOR-CACHE  YES       23-09-04  TABLE ACCESS FULL T_ORD_BIG + SORT ORDER BY STOPKEY
 C    SQL_PLAN_…c2     AUTO-CAPTURE                   NO        24-04-29  INDEX RANGE SCAN DESCENDING IX_ORD_BIG_DT_SEQ + COUNT STOPKEY

[진화 리포트 (REPORT_EVOLVE_TASK · SPM_T8_ALL) · 실행 통계와 FINDINGS만 옮겨 적음]
 SQL  계획   Elapsed(초)  Buffer Gets  Rows Processed  FINDINGS
 A    기준      .006100          640               1
      시험      .000210           13               1   passed the benefit criterion ... 48.92308 times better
 B    기준      .052000          672               3
      시험      .690000      102,741               3   failed the benefit criterion ... 0.00654 times worse
 C    기준      .118000        5,826              10
      시험      .000400           14              10   passed the benefit criterion ... 416.14286 times better

[인덱스 정리 대장 2호 · 2025-09-30 작성]
 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)
  근거 : 최근 30일 V$SQL_PLAN과 DBA_HIST_SQL_PLAN에서 이 인덱스를 쓴 계획 0건 · DBA_INDEX_USAGE 0
  결론 : 11월 정기 작업에서 DROP (적재할 때 고칠 인덱스를 하나 줄인다)

문제

세 리포트를 읽고 A, B, C의 대기 계획을 각각 받아들일지 정하시오. 리포트의 어느 칸을 근거로 삼았는지 쓰시오.
C의 리포트가 인덱스 정리 대장의 결론과 어긋나는 이유를 설명하고, 대장을 어떻게 고칠지 쓰시오.
고른 결정을 SQL로 쓰시오. 계획을 받아들이고 지우는 일의 부작용과 다음 점검 때 다시 볼 것도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① A, C : 통과한 계획을 받아들인다 (SQL 마다 그 계획 하나만 과제로 만든다)
DECLARE
  t VARCHAR2(128);
  e VARCHAR2(128);
  r NUMBER;
BEGIN
  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => '&handle_a', plan_name => 'SQL_PLAN_&a2',
                                   task_name  => 'SPM_T8_A');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => 'SPM_T8_A');
  r := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => 'SPM_T8_A');

  t := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => '&handle_c', plan_name => 'SQL_PLAN_&c2',
                                   task_name  => 'SPM_T8_C');
  e := DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name => 'SPM_T8_C');
  r := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(task_name => 'SPM_T8_C');
END;
/

-- ② B : 3월 10일 빈 테이블 통계로 들어온 NL 계획만 지운다 (받아들인 HASH b1 은 그대로)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => '&handle_b', plan_name => 'SQL_PLAN_&b2');
END;
/

-- ③ IX_ORD_BIG_DT_SEQ 는 지우지 않는다 : C 의 받아들인 계획이 쓴다 (인덱스 정리 대장 2호를 철회)
-- 확인 : 받아들인 계획이 쓰는 인덱스
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle => '&handle_c', format => 'BASIC'));

모범 답안 서술

A는 받아들인다. 기준 계획(2023년의 FULL)과 시험 계획(7월에 만든 IX_STLM_DT_AMT)의 Rows Processed가 1로 같아 같은 답을 낸다. 시험 계획은 Buffer Gets 640 → 13, 시간 .0061 → .0002초로 48.9배 낫다고 통과했다. 하루 200번이면 약 12만 8천 → 2,600블록이다. B는 받아들이지 않고 지운다. 시험 계획(NL)은 Buffer Gets 102,741로 기준(HASH, 672)보다 훨씬 나빠 0.00654로 떨어졌다. 이 NL이 대기 줄에 들어온 날은 3월 10일, T_ORD_STLM이 빈 테이블 통계(NUM_ROWS 0)로 하루 동안 돌던 날이다. 정산 테이블이 0행이라고 믿은 옵티마이저가 정산 한 건마다 고객을 찾아가는 NL을 만들었고, 베이스라인이 있어서 쓰이지는 않고 대기 줄에만 남았다. 다시 나올 이유가 없는 계획이므로 그 계획만 plan_name을 주어 지우고, 받아들인 HASH는 그대로 둔다. C는 받아들인다. Rows Processed가 10으로 같고 Buffer Gets 5,826 → 14로 416배 낫다. 대장과 어긋나는 이유: C의 기준 계획은 2023년 9월, IX_ORD_BIG_DT_SEQ가 생기기 전에 올린 FULL이다. 2024년 4월 인덱스가 생긴 뒤 옵티마이저는 날마다 인덱스 계획을 만들었지만 받아들인 계획이 아니어서 쓰지 못했고, 대기 줄(c2)에만 남았다. 실제로 돈 것은 언제나 FULL이었다. 그래서 V$SQL_PLAN, DBA_HIST_SQL_PLAN, DBA_INDEX_USAGE 어디에도 이 인덱스를 쓴 기록이 없었다. 대장의 '0건'은 쓸모없다는 증거가 아니라 베이스라인이 막아 왔다는 증거였다. 대장 2호는 철회하고, 인덱스를 지울 후보를 고를 때는 베이스라인의 대기 계획까지 함께 본다는 줄을 대장 양식에 더한다. 지웠다면 C를 받아들인 뒤에도 그 계획을 다시 만들지 못해 FULL로 돌아갔을 것이다. 부작용과 다음 점검: A와 C는 받아들인 계획이 둘이 된다. 옵티마이저는 자기가 고른 계획이 받아들인 목록에 있으면 그것을 쓰므로, 통계가 틀어지는 날에는 FULL로 돌아갈 수 있다. 받아들인 계획이 늘수록 고를 길이 늘고 지키는 힘은 약해진다. 그래서 B처럼 위험한 계획은 결코 받아들이지 않는다. 통과는 시험한 날의 데이터 기준이므로 베이스라인 대장에 그날의 규모와 재검토 조건(C는 IX_ORD_BIG_DT_SEQ에 기댄다, A는 IX_STLM_DT_AMT에 기댄다)을 적고, 다음 반기에 대기 줄을 다시 본다. 오과장 안처럼 한 과제로 돌리면 B는 떨어져서 받아들여지지는 않지만, 리포트를 SQL마다 따로 읽고 따로 결재하는 편이 다음 사람에게 근거를 남긴다.

해설

진화 리포트에서 먼저 볼 칸은 Rows Processed다. 두 계획이 같은 행 수를 냈는지 확인한 뒤에 Buffer Gets와 Elapsed를 견준다. 배수(times better)는 그다음이다.
'안 쓰이는 인덱스'의 근거는 실제로 돈 계획만 본다. 베이스라인이나 힌트, 프로파일이 막고 있는 인덱스는 쓰일 기회 자체가 없다. 인덱스를 감추거나 지우기 전에 DBA_SQL_PLAN_BASELINES의 계획(받아들인 것과 대기 줄 모두)이 그 인덱스를 쓰는지 본다(OPT-046).
FIXED로 바꾸지 않는다. A와 C를 FIXED로 박으면 앞으로 더 나은 계획이 생겨도 대기 줄에 기록되지 않는다(OPT-050).
Free에는 자동 진화가 없다(교재 18화 §3). 그래서 대기 줄은 사람이 반기마다 본다. 18화의 두 진화(63.7배 통과, 0.33배 탈락)와 오늘의 세 리포트를 같은 대장에 이어 적는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재