화요일부터 읽은 블록 수 1위로 올라온 주문 요약 조회, 계획이 바뀌었으니 고정하자고 한다

기초SELECT한빛유통637 / 700

상황

고객센터 화면과 모바일 앱 홈 화면은 같은 SQL로 고객 한 명의 상태별 주문 수와 금액(「주문 요약」)을 보여 준다. 화요일 오후 박팀장이 AWR 보고서를 보냈다. 「SQL ordered by Gets」 1위가 이 SQL이고, 월요일 보고서에서는 37위였다. 화요일 오전에 모바일 앱의 새 판이 나갔지만 DB 쪽 배포는 없었다. 오과장은 이렇게 말했다. "월요일 밤에 자동 통계 수집이 돌았으니 계획이 바뀐 거예요. 월요일 계획을 AWR에서 꺼내 베이스라인으로 박죠." 조민우가 AWR의 SQL 이력을 뽑았다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · 고객 2,000명 · 고객당 주문 약 50건
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_CUS_DT (CUS_ID, ORD_DT)
통계: T_ORD의 LAST_ANALYZED는 지난주 목요일 22:07(월요일 밤에는 STALE이 아니어서 자동 수집이 건너뛰었다)
AWR: 1시간 간격 · 보존 8일 · Diagnostic Pack 계약 있음

원본 SQL

sql

-- 화면과 앱이 같이 쓰는 SQL (SQL_ID 8fz3m1wq2c7d4)
SELECT ORD_ST, COUNT(*) AS ORD_CNT, SUM(ORD_AMT) AS ORD_AMT
  FROM T_ORD
 WHERE CUS_ID = :cus_id
 GROUP BY ORD_ST;

-- 오과장 안 : 월요일 오전 스냅샷에서 계획을 꺼내 베이스라인으로 고정한다
DECLARE
  n PLS_INTEGER;
BEGIN
  n := DBMS_SPM.LOAD_PLANS_FROM_AWR(
         begin_snap   => 4417,
         end_snap     => 4419,
         basic_filter => q'[sql_id = '8fz3m1wq2c7d4']');
END;
/

실행계획 학습용 예시

xplan

[DBA_HIST_SQLSTAT · SQL_ID 8fz3m1wq2c7d4 · 1시간 스냅샷]
 SNAP_ID  시작       PLAN_HASH_VALUE  EXECUTIONS_DELTA  BUFFER_GETS_DELTA  ROWS_PROCESSED_DELTA  ELAPSED_TIME_DELTA
    4418  월 10:00      1840227316             2,012            104,630                 4,628             1.21초
    4419  월 11:00      1840227316             2,105            109,460                 4,842             1.27초
    4442  화 10:00      1840227316           121,480          6,317,110               279,404            72.60초
    4443  화 11:00      1840227316           118,960          6,185,920               273,608            71.41초

[DBA_HIST_ACTIVE_SESS_HISTORY · 같은 SQL_ID · 화 10:00 ~ 12:00 · MODULE별 표본 수]
 MOBILE_HOME   13
 CS_ORDER       1

[DBA_OPTSTAT_OPERATIONS · 월요일 밤]
 22:00:03 ~ 22:14:41  gather_database_stats (auto optimizer stats collection) · 모은 테이블 3개 (T_ORD 없음)

[지금 커서 · ALLSTATS LAST · 고객 한 명]
Plan hash value: 1840227316

---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                 |      1 |        |      2 |00:00:00.01 |      52 |
|   1 |  HASH GROUP BY                       |                 |      1 |      3 |      2 |00:00:00.01 |      52 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD           |      1 |     50 |     50 |00:00:00.01 |      52 |
|*  3 |    INDEX RANGE SCAN                  | IX_T_ORD_CUS_DT |      1 |     50 |     50 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("CUS_ID"=:CUS_ID)

문제

오과장의 진단(통계 수집 때문에 계획이 바뀌었다)이 맞는지 위 자료로 판단하시오. 화요일에 이 SQL이 1위가 된 이유를 실행당 숫자로 설명하시오.
이 SQL의 일량 변화를 계획과 호출 횟수로 나눠 보는 진단 SQL을 쓰고, 오과장 안을 받아들일지와 실제로 고칠 곳을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 스냅샷마다 계획과 실행당 숫자 (합계가 아니라 나눈 값으로 본다)
SELECT s.SNAP_ID,
       TO_CHAR(n.BEGIN_INTERVAL_TIME, 'DY HH24:MI') AS BEGIN_TIME,
       s.PLAN_HASH_VALUE,
       s.EXECUTIONS_DELTA AS EXECS,
       ROUND(s.BUFFER_GETS_DELTA    / NULLIF(s.EXECUTIONS_DELTA, 0), 1)        AS GETS_PER_EXEC,
       ROUND(s.ROWS_PROCESSED_DELTA / NULLIF(s.EXECUTIONS_DELTA, 0), 1)        AS ROWS_PER_EXEC,
       ROUND(s.ELAPSED_TIME_DELTA   / NULLIF(s.EXECUTIONS_DELTA, 0) / 1000, 2) AS MS_PER_EXEC
  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 s.SQL_ID = '8fz3m1wq2c7d4'
 ORDER BY s.SNAP_ID;

-- ② 늘어난 실행은 누가 부르나 (ASH 표본을 모듈별로)
SELECT MODULE, COUNT(*) AS SAMPLES
  FROM DBA_HIST_ACTIVE_SESS_HISTORY
 WHERE SQL_ID = '8fz3m1wq2c7d4'
   AND SAMPLE_TIME >= TRUNC(SYSDATE) + 10/24
 GROUP BY MODULE
 ORDER BY SAMPLES DESC;

-- 베이스라인은 올리지 않는다 : 계획(1840227316)과 실행당 52블록은 월요일과 같다
-- 앱 개발팀에 홈 화면의 주문 요약 호출 간격을 확인하고 줄여 달라고 한다

모범 답안 서술

네 스냅샷의 PLAN_HASH_VALUE는 1840227316 하나로 같다. 계획은 바뀌지 않았다. 게다가 T_ORD는 월요일 밤 자동 수집 대상도 아니었다(LAST_ANALYZED 지난주 목요일). 오과장의 진단은 틀렸다. 합계 대신 실행당으로 나눠 보면 원인이 드러난다. BUFFER_GETS_DELTA ÷ EXECUTIONS_DELTA는 월요일과 화요일 모두 52.0이고, 실행당 행 수(2.3)와 실행당 시간(약 0.6ms)도 같다. 달라진 것은 실행 횟수다. 시간당 약 2,000번에서 약 12만 번으로 60배 늘었고, 그래서 읽은 블록의 합계도 시간당 약 10만에서 약 630만으로 60배가 되어 1위에 올랐다. ASH 표본 14개 가운데 13개가 MOBILE_HOME 모듈이므로, 늘어난 호출은 화요일 오전에 나간 모바일 앱 새 판의 홈 화면이다. 오과장 안은 받아들이지 않는다. 같은 계획을 베이스라인으로 고정해도 실행당 52블록도, 호출 수도 그대로다. 고칠 곳은 호출하는 쪽이다. 앱 개발팀에 홈 화면이 주문 요약을 몇 초마다 다시 부르는지 확인하고, 화면을 열 때와 주문 상태가 바뀔 때만 부르도록(또는 앱에서 잠시 보관하도록) 호출 간격을 줄여 달라고 한다. 시간당 2,000번 수준으로 돌아가면 블록 합계도 월요일 수준이 된다.

해설

합계는 '얼마나'만 말한다. 「SQL ordered by Gets」는 구간 합계의 순위다. 합계가 늘었으면 먼저 실행 횟수로 나눠 보고, 실행당 숫자가 같으면 계획이 아니라 부르는 횟수가 바뀐 것이다(교재 17화의 평균과 같은 함정).
계획이 바뀌었는지는 PLAN_HASH_VALUE로 먼저 본다. 값이 같고 실행당 숫자도 같으면 계획 문제가 아니다. 값이 같은데 실행당 숫자만 늘었다면 조건절이 바뀌었는지 따로 봐야 한다. PLAN_HASH_VALUE는 Predicate Information을 셈에 넣지 않는다(OPT-054).
오과장 안의 basic_filter에는 plan_hash_value가 없다. 구간 안에 계획이 둘 이상이면 나쁜 계획까지 받아들인 계획으로 올라간다. AWR에서 계획을 올릴 때는 SQL_ID와 PLAN_HASH_VALUE를 함께 준다(OPT-051).
DB에서 할 일이 아예 없지는 않다. 실행당 52블록은 고객의 주문 50건을 테이블에서 하나씩 읽는 몫이라, (CUS_ID, ORD_ST, ORD_AMT) 인덱스면 테이블에 가지 않고 서너 블록으로 끝난다. 그러나 60배로 늘어난 호출을 인덱스로 덮으면 다음 앱 판에서 같은 일이 되풀이된다. 호출부터 바로잡고, 인덱스는 그 뒤에 적재 비용과 함께 판단한다.
DBA_HIST_*와 ASH는 Diagnostic Pack 기능이다. 팩이 없는 서버라면 Statspack으로 같은 나눗셈을 한다(교재 17화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황

테이블과 인덱스

원본 SQL

실행계획 학습용 예시

문제

모범 답안 SQL

모범 답안 서술

해설

관련 교재