지난달 월마감 때보다 네 배 느려진 판매 대사 배치, 지난달 계획을 찾을 데가 없다

기초PLSQL유통638 / 700

상황

본사 유통의 「월 판매 대사」 배치는 매달 1일 02:00에 지난달 판매를 매장, 상품별로 묶어 POS 집계와 맞춰 본다. 배치 로그를 보면 6월 1일에는 대사 SQL이 0.4초였는데, 7월 1일 오늘은 1.4초가 걸렸다. 다음 단계가 이 결과를 기다리므로 박팀장은 지난달 계획과 무엇이 다른지부터 알고 싶어 한다. 조민우가 지난달 계획과 비교하려고 AWR의 SQL 이력을 조회했지만 오늘 것밖에 나오지 않았다. 선배가 물었다. "AWR이 며칠을 들고 있는지는 봤어?"

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 한 달 약 21,000건 · CF11 판매상품: 1,096,708행 · 8,204블록
대사 SQL은 한 달에 한 번, 1일 02:00에만 돈다(SQL_ID 2bq8r6d1x0f7c)
AWR: 1시간 간격 · 보존 8일 · TOPNSQL 기본값 · Diagnostic Pack 계약 있음
통계 이력 보존 31일 · 배치 로그에는 SQL마다 시작, 끝, 걸린 시간, 처리 행 수만 남는다

원본 SQL

sql

-- 조민우가 돌린 조회 : 지난달 1일 02:00의 계획을 찾는다
SELECT s.SNAP_ID, n.BEGIN_INTERVAL_TIME, s.PLAN_HASH_VALUE,
       s.EXECUTIONS_DELTA, s.BUFFER_GETS_DELTA, s.ELAPSED_TIME_DELTA
  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 s.SQL_ID = '2bq8r6d1x0f7c'
 ORDER BY s.SNAP_ID;
-- 결과 : 7월 1일 02:00 스냅샷 한 줄뿐

실행계획 학습용 예시

xplan

[DBA_HIST_WR_CONTROL]
 SNAP_INTERVAL       RETENTION           TOPNSQL
 +00 01:00:00.0      +08 00:00:00.0      DEFAULT

[DBA_HIST_SNAPSHOT]
 가장 오래된 스냅샷 06-23 09:00 · 가장 최근 07-01 09:00 · 193개

[DBA_HIST_SQLSTAT · SQL_ID 2bq8r6d1x0f7c]
 SNAP_ID  시작          PLAN_HASH_VALUE  EXECUTIONS_DELTA  BUFFER_GETS_DELTA  ELAPSED_TIME_DELTA
    6214  07-01 02:00      3802991541                 1             87,512             1.41초
 (6월 1일 02:00의 스냅샷은 6월 9일에 지워졌다)

[배치 로그 · 대사 SQL]
 06-01 02:03:11  0.4초  654행
 07-01 02:03:09  1.4초  654행

[DBA_TAB_STATS_HISTORY · 6월 1일 이후]
 CF10  06-28 23:40  ·  CF11  06-28 23:41

문제

지난달 계획을 AWR에서 찾을 수 없는 이유를 위 설정으로 설명하시오. 보존 기간이 충분했더라도 남지 않았을 수 있는 경우도 쓰시오.
다음 달부터는 월마감 계획을 지난달과 견줄 수 있도록 AWR 설정을 고치고, 월마감 구간을 따로 남기는 SQL을 쓰시오. 오늘 원인을 고친 뒤 그 계획을 지키는 방법도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 보존을 업무 주기(한 달)보다 길게 : 45일 · 간격은 1시간 그대로 · 스냅샷마다 남기는 상위 SQL 수를 늘린다
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 45 * 24 * 60,      -- 분 단위
    interval  => 60,
    topnsql   => 100);
END;
/

-- ② 오늘 월마감 구간(01:00 ~ 04:00 스냅샷)을 AWR 베이스라인으로 남긴다 : 보존 기간이 지나도 지워지지 않는다
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 6213,
    end_snap_id   => 6216,
    baseline_name => 'MON_CLOSE_202407',
    expiration    => 400);
END;
/

-- ③ 다음 달 월마감 구간은 미리 예약한다
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    start_time    => TO_DATE('2024-08-01 01:00', 'YYYY-MM-DD HH24:MI'),
    end_time      => TO_DATE('2024-08-01 04:00', 'YYYY-MM-DD HH24:MI'),
    baseline_name => 'MON_CLOSE_202408',
    template_name => 'TPL_MON_CLOSE_202408',
    expiration    => 400);
END;
/

-- ④ 대사 SQL 은 순위와 상관없이 AWR 에 남게 표시해 둔다
EXEC DBMS_WORKLOAD_REPOSITORY.ADD_COLORED_SQL(sql_id => '2bq8r6d1x0f7c');

-- ⑤ 원인을 고친 뒤 좋은 계획을 SQL plan baseline 으로 올린다 (계획 자체를 지키는 장치)
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id          => '2bq8r6d1x0f7c',
                                             plan_hash_value => &good_phv);
END;
/

모범 답안 서술

AWR의 보존 기간(RETENTION)이 8일이다. 6월 1일 02:00의 스냅샷은 6월 9일에 지워졌고, 지금 남은 가장 오래된 스냅샷은 6월 23일 것이다. 대사 SQL은 한 달에 한 번 도므로 8일 보존으로는 직전 회차가 늘 지워진 뒤에 다음 회차가 돈다. 그래서 비교할 지난달 계획이 없다. 보존 기간이 충분했더라도 남지 않았을 수 있다. AWR은 스냅샷마다 모든 SQL이 아니라 경과 시간, CPU, 읽은 블록 수 같은 기준별 상위 SQL(TOPNSQL)만 DBA_HIST_SQLSTAT에 남긴다. 6월 1일처럼 0.4초로 끝난 SQL은 월마감 새벽의 무거운 SQL들에 밀려 상위에 들지 못했을 수 있다. 다음 달부터 견줄 수 있게 하려면 보존 기간을 업무 주기보다 길게 잡는다. MODIFY_SNAPSHOT_SETTINGS로 보존을 45일로 늘리고 상위 SQL 수도 늘린다. 월마감 구간은 CREATE_BASELINE으로 AWR 베이스라인을 만들어 보존 기간과 상관없이 남기고, 다음 달 구간은 CREATE_BASELINE_TEMPLATE으로 미리 예약한다. 대사 SQL은 ADD_COLORED_SQL로 표시해 순위와 상관없이 남게 한다. 오늘 원인을 찾아 고친 뒤에는 그 계획을 LOAD_PLANS_FROM_CURSOR_CACHE로 SQL plan baseline에 올린다. AWR 베이스라인은 비교할 자료를 남기는 것이고, SQL plan baseline은 계획 자체를 지키는 것이다. 원인은 지금 남아 있는 단서에서 찾는다. 통계 이력은 31일을 보존하므로 6월 28일에 CF10과 CF11 통계가 바뀐 것을 볼 수 있다.

해설

베이스라인이라는 말이 둘이다. AWR 베이스라인(DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE)은 스냅샷 구간을 지우지 않고 남기는 것이고, SQL plan baseline(DBMS_SPM)은 문장마다 받아들인 계획을 지키는 것이다. 앞의 것은 비교할 자료를, 뒤의 것은 계획을 남긴다.
주기가 긴 SQL은 그 주기를 덮어야 한다. 보존 8일은 매일 도는 화면에는 충분하지만 월마감, 분기 마감 배치에는 모자라다(교재 17화: 이 교재의 AWR 보존은 8일). 보존을 늘리면 SYSAUX가 그만큼 커지므로, 늘리는 대신 마감 구간만 AWR 베이스라인으로 남기는 방법도 있다.
배치 로그를 고친다. 걸린 시간만이 아니라 SQL_ID, PLAN_HASH_VALUE, 읽은 블록 수를 함께 남기면 AWR이 지워진 뒤에도 지난 회차와 견줄 수 있다.
ADD_COLORED_SQL로 표시한 SQL은 상위 순위에 들지 않아도 스냅샷마다 남는다. 꼭 이력이 필요한 핵심 SQL 몇 개에만 쓴다.
AWR과 DBA_HIST_* 조회는 Diagnostic Pack 기능이다. 팩이 없는 서버라면 Statspack의 스냅샷 보존을 같은 기준(업무 주기 이상)으로 정한다(교재 17화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재