계획 변경 감시 메일은 날마다 스무 건씩 오는데 정작 느려진 점장 화면은 목록에 없었다

중급SELECT유통642 / 700

상황

조민우는 지난달 본사 유통 DB에 「계획 변경 감시」를 만들었다. 매일 08:00에 AWR에서 SQL_ID마다 어제 하루 읽은 블록의 합계를 지난 7일 평균과 견주어, 두 배가 넘는 것 가운데 합계 상위 50개를 메일로 보낸다. 메일에는 날마다 스무 건 가까이 올라오지만 열어 보면 계획이 바뀐 것은 거의 없다. 지난 수요일 오후 점장들이 「매장 일 판매」 화면이 느려졌다고 했는데, 목요일 아침 메일에는 그 화면이 없었다(원인인 주말 인덱스 작업은 그날 오후에 찾았다). 선배가 말했다. "감시가 무엇과 무엇을 견주는지부터 다시 보자."

테이블과 인덱스

AWR: 1시간 간격 · 보존 45일 · TOPNSQL 100 · Diagnostic Pack 계약 있음
DBA_HIST_SQLSTAT: 스냅샷마다 SQL_ID와 PLAN_HASH_VALUE별로 한 줄 · EXECUTIONS_DELTA · BUFFER_GETS_DELTA · FORCE_MATCHING_SIGNATURE(리터럴을 바인드처럼 바꿔 셈한 서명. 리터럴만 다른 문장은 같은 값)
점장 화면 「매장 일 판매」: 매장 번호와 날짜를 글자로 박는 리터럴 SQL · 하루 약 600번 · 매장 6곳이 날마다 새 날짜로 부르므로 하루에 SQL_ID 6개가 새로 생긴다

원본 SQL

sql

-- 지금의 감시 SQL (매일 08:00)
WITH d AS (
  SELECT s.SQL_ID,
         SUM(CASE WHEN n.BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE) - 1 THEN s.BUFFER_GETS_DELTA END)     AS GETS_1D,
         SUM(CASE WHEN n.BEGIN_INTERVAL_TIME <  TRUNC(SYSDATE) - 1 THEN s.BUFFER_GETS_DELTA END) / 7 AS GETS_7D_AVG
    FROM DBA_HIST_SQLSTAT s, DBA_HIST_SNAPSHOT n
   WHERE n.SNAP_ID = s.SNAP_ID
     AND n.DBID = s.DBID
     AND n.INSTANCE_NUMBER = s.INSTANCE_NUMBER
     AND n.BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE) - 8
     AND n.BEGIN_INTERVAL_TIME <  TRUNC(SYSDATE)
   GROUP BY s.SQL_ID
)
SELECT *
  FROM (SELECT * FROM d ORDER BY GETS_1D DESC NULLS LAST)
 WHERE ROWNUM <= 50
   AND GETS_1D > GETS_7D_AVG * 2

실행계획 학습용 예시

xplan

[목요일 08:00 감시 메일 · 20건 가운데 앞 5건 · 오른쪽은 선배가 확인한 실제]
 SQL_ID          어제 블록     7일 평균     배율   실제
 7tq2m0x4c1v9d   21,604,330    4,320,866    5.0   월중 정산 재집계 · 수요일에 두 번 돌았다 · 계획 그대로
 1kf8w2s0n7c3b   12,880,415    1,840,059    7.0   본부 주간 회의 자료 · 수요일에만 돈다 · 계획 그대로
 4mz1q9d6t0x2r    9,312,700    3,104,233    3.0   가격 개편 반영 · 개편 주간이라 실행 3배 · 계획 그대로
 0c6v3n8k2w5yh    6,418,220    2,139,407    3.0   발주 마감 · 월말 앞이라 실행이 늘었다 · 계획 그대로
 8s2d7f1m4q9xa    5,902,881    1,967,627    3.0   점장 재고 화면 · 실행 3배 · 계획 그대로

[DBA_HIST_SQLSTAT · 「매장 일 판매」 · FORCE_MATCHING_SIGNATURE 1840522197306614721로 묶어 날마다 합친 값]
 날짜        SQL_ID 수  PLAN_HASH_VALUE   실행    읽은 블록   실행당
 06-10(월)          6     2207186133      598      22,724       38
 06-11(화)          6     2207186133      611      23,218       38
 06-12(수)          6     3771904125      604   2,784,440    4,610
 · 날마다 SQL_ID 6개가 새로 생겨 어제의 SQL_ID에는 지난 7일 기록이 없다
 · SQL_ID 하나하나는 하루 46만 블록 안팎이라 어제 합계 순위로는 60위 밖이다

문제

지금의 감시 SQL이 오탐을 많이 내는 이유와 「매장 일 판매」를 놓친 이유를 각각 쓰시오.
계획이 바뀌고 실행당 일이 늘어난 SQL만 고르도록 감시 SQL을 고쳐 쓰시오. 리터럴 SQL도 잡히게 하시오.

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

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

모범답안

모범 답안 SQL

sql

WITH h AS (
  SELECT CASE WHEN s.FORCE_MATCHING_SIGNATURE = 0 THEN s.SQL_ID
              ELSE TO_CHAR(s.FORCE_MATCHING_SIGNATURE) END             AS SQL_KEY,   -- 리터럴만 다른 문장을 묶는다
         s.PLAN_HASH_VALUE,
         CASE WHEN n.BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE) - 1
              THEN 'NEW' ELSE 'OLD' END                                AS PERIOD,
         s.EXECUTIONS_DELTA                                            AS EXECS,
         s.BUFFER_GETS_DELTA                                           AS GETS,
         s.SQL_ID
    FROM DBA_HIST_SQLSTAT s
    JOIN DBA_HIST_SNAPSHOT n
      ON n.SNAP_ID = s.SNAP_ID
     AND n.DBID = s.DBID
     AND n.INSTANCE_NUMBER = s.INSTANCE_NUMBER
   WHERE n.BEGIN_INTERVAL_TIME >= TRUNC(SYSDATE) - 8
     AND n.BEGIN_INTERVAL_TIME <  TRUNC(SYSDATE)
),
p AS (                                                                 -- 계획별 · 기간별 실행당 블록
  SELECT SQL_KEY, PLAN_HASH_VALUE, PERIOD,
         SUM(EXECS)                         AS EXECS,
         SUM(GETS) / NULLIF(SUM(EXECS), 0)  AS GETS_PER_EXEC,
         MIN(SQL_ID)                        AS SAMPLE_SQL_ID
    FROM h
   GROUP BY SQL_KEY, PLAN_HASH_VALUE, PERIOD
)
SELECT nw.SQL_KEY, nw.SAMPLE_SQL_ID,
       od.PLAN_HASH_VALUE                                   AS OLD_PHV,
       ROUND(od.GETS_PER_EXEC)                              AS OLD_GETS_PER_EXEC,
       nw.PLAN_HASH_VALUE                                   AS NEW_PHV,
       ROUND(nw.GETS_PER_EXEC)                              AS NEW_GETS_PER_EXEC,
       nw.EXECS                                             AS NEW_EXECS,
       ROUND(nw.EXECS * (nw.GETS_PER_EXEC - od.GETS_PER_EXEC)) AS EXTRA_GETS
  FROM p nw
  JOIN p od
    ON od.SQL_KEY = nw.SQL_KEY
   AND od.PERIOD = 'OLD'
 WHERE nw.PERIOD = 'NEW'
   AND nw.PLAN_HASH_VALUE <> od.PLAN_HASH_VALUE
   AND NOT EXISTS (SELECT 1                                            -- 지난 7일에 없던 계획
                     FROM p x
                    WHERE x.SQL_KEY = nw.SQL_KEY
                      AND x.PERIOD = 'OLD'
                      AND x.PLAN_HASH_VALUE = nw.PLAN_HASH_VALUE)
   AND nw.EXECS >= 50
   AND nw.GETS_PER_EXEC >= 2 * od.GETS_PER_EXEC
 ORDER BY EXTRA_GETS DESC;                                             -- 상위 N개로 자르지 않는다

모범 답안 서술

오탐이 많은 이유: 지금 감시는 SQL_ID마다 어제 하루의 블록 합계를 지난 7일 평균과 견준다. 합계는 실행 횟수에 따라 움직인다. 수요일에 두 번 돈 재집계, 수요일에만 도는 주간 회의 자료, 개편 주간이라 실행이 세 배가 된 가격 반영은 계획이 그대로여도 그날 합계가 평균의 몇 배가 된다. 메일 다섯 줄이 모두 그랬다. 합계로는 호출이 늘어난 것과 계획이 바뀐 것을 구별할 수 없다. 놓친 이유: 「매장 일 판매」는 매장과 날짜를 글자로 박은 리터럴 SQL이라 날마다 SQL_ID가 새로 생긴다. 수요일의 SQL_ID에는 지난 7일 기록이 없으므로 평균이 NULL이고, NULL과의 비교는 참이 되지 않아 걸러졌다. 게다가 SQL_ID 하나하나는 가벼워서 상위 50개 안에도 들지 못했다. 같은 모양의 문장을 FORCE_MATCHING_SIGNATURE로 묶어 보면 수요일에 PLAN_HASH_VALUE가 2207186133에서 3771904125로 바뀌었고, 실행당 블록이 38에서 4,610으로 늘었다. 고친 감시: 묶는 단위를 FORCE_MATCHING_SIGNATURE(0이면 SQL_ID)로 바꾸고, 계획(PLAN_HASH_VALUE)별로 실행당 블록(블록 합계 ÷ 실행 횟수)을 낸다. 지난 7일에 없던 계획이 어제 나타났고, 그 계획의 실행당 블록이 예전 계획의 두 배 이상이며, 실행이 50번 이상인 것만 고른다. 순서는 늘어난 블록(실행 횟수 × 실행당 차이)으로 정하고 상위 50개로 자르지 않는다. 그러면 메일의 다섯 줄은 계획이 그대로라 빠지고, 「매장 일 판매」는 하루 약 276만 블록이 늘어난 첫 줄로 올라온다.

해설

감시는 두 가지만 묻는다. 계획이 바뀌었나(새 PLAN_HASH_VALUE)와 실행당 일이 늘었나(실행당 블록)다. 실행 횟수가 늘어난 것은 계획 감시가 아니라 호출 감시의 일이다(OPT-047).
계획 해시가 같아도 실행당 블록이 크게 늘 수 있다. 조건절이 바뀐 경우다(OPT-054). 그런 경우까지 잡으려면 같은 계획 안에서 실행당 블록이 몇 배로 늘었는지를 따로 한 줄 더 본다.
AWR에는 상위 SQL만 남는다(OPT-048). 리터럴 SQL처럼 하나하나가 가벼운 문장은 아예 AWR에 남지 않는 날도 있다. 감시를 고치는 것과 별개로 이런 화면은 바인드 변수로 바꾸는 것이 먼저다(교재 11화).
감시는 숫자를 알려 줄 뿐이다. 올라온 줄은 두 계획을 DBMS_XPLAN.DISPLAY_AWR로 나란히 펼쳐 어느 줄이 바뀌었는지 확인하고, 그 시각의 DDL과 통계 이력을 맞춰 본 뒤에 판단한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재