인프라팀의 세 가지 안: 「피킹을 끄고, 히스토그램을 지우고, 계획을 전부 잡아 두자」

실전DDL한빛유통573 / 700

상황

지난달 정산 상태 탭이 날마다 다른 탭에서 느렸던 일(PARSE-002)에 이어, 오늘 아침에는 「등급별 매출」 화면이 C 등급에서 3만 블록을 읽었다. 인프라팀이 메일을 보냈다. "바인드 값 때문에 계획이 흔들리는 일을 한 번에 없애겠습니다. 피킹을 끄고, 히스토그램을 지우고, 계획 베이스라인을 자동으로 잡아 두면 계획이 바뀔 일이 없습니다. 금요일 밤 반영 예정입니다." 박팀장이 덧붙였다. "세 가지를 따로 평가해 주세요. 반대한다면 대신 무엇을 할지도요."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · FREQUENCY 히스토그램 · 6월 한 달 27,390건
M_CUS: 2,000행 · 30블록 · CUS_GD A 20명 · B 180명 · C 1,800명 · FREQUENCY 히스토그램
인덱스 IX_ORD_BIG_DT · IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT) · PK_M_CUS
CURSOR_SHARING = EXACT · 바인드 피킹 켬 · SQL Plan Baseline 없음 · statistics_level = TYPICAL

원본 SQL

sql

-- 인프라팀 변경 요청 (금요일 22:00)
ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE SCOPE = BOTH;                       -- ① 피킹 끄기
EXEC DBMS_STATS.SET_GLOBAL_PREFS('METHOD_OPT', 'FOR ALL COLUMNS SIZE 1');           -- ② 히스토그램 지우기 (그날 밤 다시 수집)
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES = TRUE SCOPE = BOTH;          -- ③ 계획 자동 수집 · 고정

-- 영향을 받는 문장 셋
-- [S1] 등급별 매출 화면 (바인드 : 등급과 조회월)
SELECT c.CUS_GD, COUNT(*) AS ORD_CNT, SUM(o.ORD_AMT) AS AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = :gd
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TO_DATE(:p_ym || '01', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:p_ym || '01', 'YYYYMMDD'), 1)
 GROUP BY c.CUS_GD;

-- [S2] 정산 상태 확인 · 취소 탭 (2019 강도현 · 상수 · 완료와 미결 탭도 같은 모양)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
         WHERE ORD_ST = 'CANCEL' ORDER BY ORD_DT DESC, ORD_SEQ DESC)
 WHERE ROWNUM <= 1000;

-- [S3] 월 매출 Q1 (바인드 : 조회월 · 커버링 인덱스)
SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= TO_DATE(:p_ym || '01', 'YYYYMMDD')
   AND ORD_DT <  ADD_MONTHS(TO_DATE(:p_ym || '01', 'YYYYMMDD'), 1);

실행계획 학습용 예시 · 상태 탭과 피킹 끄기 수치는 교재 11화 실측

xplan

[S1 · 6월 · 지금 (피킹 켬 · 히스토그램 있음) · 등급마다 한 번의 Buffers]
                       A 등급    B 등급    C 등급
 A로 태어난 커서 (NL)      372     3,035    30,081    ← NESTED LOOPS + IX_ORD_BIG_CUS_DT
 C로 태어난 커서 (HASH)  5,858     5,858     5,858    ← T_ORD_BIG FULL + HASH JOIN
 등급마다 맞는 계획        372     3,035     5,858
 (A로 태어난 날은 A 372 → C 30,081 순서로 칸이 섞여 ACS가 한 번 뒤에 C를 HASH로 나눈다 · C로 태어난 날은 셋 다 1번 칸이라 그대로)

[S2 · 탭 셋 (상수)]  완료 1,016 · 미결 5,826 · 취소 5,826   (FREQUENCY 덕에 탭마다 맞는 계획)
[S3 · Q1]            어느 달이든 91                         (커버링 인덱스 범위 스캔)

[① 피킹 끄기를 내 세션에만 (ALTER SESSION SET "_optim_peek_user_binds" = FALSE)]
 S1 : 등급 값을 모른다 → CUS_GD E-Rows 667(1/3) → HASH 고정 · A 5,858 · B 5,858 · C 5,858
 S2 : 상수라 그대로 (1,016 · 5,826 · 5,826)
 S3 : 91 그대로
 (교재 11화) 상태 탭을 바인드로 쓴 문장 · 셋 다 인덱스 계획 · 취소 50,347 / OR가 든 바인드 페이징(rev02 모양) · 2페이지 1,005,991

[② 복사본 스키마에서 히스토그램을 지우고 다시 모은 뒤]
 S1 : CUS_GD E-Rows 667 → HASH 고정 · A 5,858
 S2 : 'CANCEL' E-Rows 333,333 → 인덱스 계획(INDEX FULL SCAN DESCENDING) · 취소 50,347 · 미결 13,510 · 완료 1,016
 S3 : 91 그대로

[③ 복사본에서 자동 수집을 켜고 하루 돌린 뒤 · DBA_SQL_PLAN_BASELINES]
 S1 : ACCEPTED 1개(그날 처음 수집된 NESTED LOOPS) · 그 뒤 HASH JOIN 계획은 ACCEPTED = NO로만 쌓인다 → C 30,081이 계속
 S2 : 탭마다 문장이 달라 베이스라인 셋 · 계획 그대로
 베이스라인 하루 1,940개 생성 · SYSAUX 사용 증가

문제

①, ②, ③을 하나씩 위 측정으로 평가하시오. 각각 무엇을 고치고 무엇을 망가뜨리는지 문장별로 쓰시오.
서버 설정 대신 무엇을 고칠지 SQL로 제안하시오. 바인드 값 때문에 계획이 흔들리는 문장을 찾아내는 방법도 쓰시오.
인프라팀이 걱정하는 "계획이 흔들리는 일"을 제안보다 작은 범위로 막는 방법과 그 기준을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ①②③ 은 걸지 않는다 (변경 요청 반려)

-- S1 : 등급은 값이 셋이고 값마다 맞는 계획이 다르다 → 등급마다 상수 문장 (조회월은 바인드 그대로)
--      화면 코드가 탭(등급)에 맞는 문장을 고른다 · 커서 셋 · 하드 파싱 세 번
SELECT c.CUS_GD, COUNT(*) AS ORD_CNT, SUM(o.ORD_AMT) AS AMT
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID = c.CUS_ID
   AND o.ORD_DT >= TO_DATE(:p_ym || '01', 'YYYYMMDD')
   AND o.ORD_DT <  ADD_MONTHS(TO_DATE(:p_ym || '01', 'YYYYMMDD'), 1)
 GROUP BY c.CUS_GD;
--   B 등급 : CUS_GD = 'B' (NL) · C 등급 : CUS_GD = 'C' (HASH) · 나머지는 같은 모양

-- S2 · S3 : 고치지 않는다

-- 흔들리는 문장 찾기 : 최근 2주 안에 계획 해시가 둘 이상이었던 문장 (일량 차이가 큰 순)
SELECT s.sql_id,
       COUNT(DISTINCT s.plan_hash_value) AS plans,
       ROUND(MIN(s.buffer_gets_delta / NULLIF(s.executions_delta, 0))) AS min_bg,
       ROUND(MAX(s.buffer_gets_delta / NULLIF(s.executions_delta, 0))) AS max_bg
  FROM dba_hist_sqlstat s
  JOIN dba_hist_snapshot n ON n.snap_id = s.snap_id AND n.instance_number = s.instance_number
 WHERE n.begin_interval_time > SYSDATE - 14
 GROUP BY s.sql_id
HAVING COUNT(DISTINCT s.plan_hash_value) > 1
 ORDER BY max_bg / NULLIF(min_bg, 0) DESC;

-- 지금 공유 풀에서 값에 따라 갈릴 수 있다고 표시된 문장
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware, executions,
       ROUND(buffer_gets / NULLIF(executions, 0)) AS bg_per_exec
  FROM v$sql
 WHERE is_bind_sensitive = 'Y'
 ORDER BY buffer_gets DESC;

개선 후 실행계획 학습용 예시 · 상태 탭과 피킹 끄기 수치는 교재 11화 실측

xplan

[S1 · 등급마다 상수 문장 · 6월]
 A  NESTED LOOPS + IX_ORD_BIG_CUS_DT      372
 B  NESTED LOOPS + IX_ORD_BIG_CUS_DT    3,035
 C  T_ORD_BIG FULL + HASH JOIN           5,858   (C로 태어난 날의 A 5,858 → 372 · A로 태어난 날의 C 30,081 → 5,858)
[S2] 1,016 · 5,826 · 5,826 그대로     [S3] 91 그대로

모범 답안 서술

① 피킹 끄기: 오라클이 바인드 값을 모른 채 계획을 세운다. S1은 등급 값을 몰라 셋 다 1/3(667명)로 보고 해시 조인 하나로 굳는다. C 등급의 30,081은 사라지지만, A 등급은 372 → 5,858(16배)로 늘 느려진다. 흔들림을 없애는 대신 늘 평균에 맞는 계획을 탄다. S2는 상수라, S3는 커버링 인덱스라 그대로다. 그러나 인스턴스 전체에 걸리는 스위치다. 교재 11화에서 잰 대로, 상태값을 바인드로 받는 문장은 세 탭 모두 인덱스 계획이 되어 취소가 50,347블록이고, OR가 든 바인드 페이징(rev02 모양)은 2페이지에서 1,005,991블록을 읽었다. 이 DB에 그런 모양의 문장이 얼마나 남았는지 모른 채 켤 수는 없다. 밑줄 파라미터라 지원 없이 바꾸지 않는다는 원칙도 있다. ② 히스토그램 지우기: S1은 ①과 같이 해시로 굳어 A가 5,858이 된다. 더 큰 문제는 S2다. 탭마다 상수를 쓴 까닭은 히스토그램으로 탭마다 맞는 계획을 타게 하려는 것이었다. 히스토그램이 없으면 'CANCEL'도 1/3(333,333건)로 보고 인덱스를 거꾸로 읽는 계획을 골라, 취소 탭이 5,826 → 50,347, 미결 탭이 5,826 → 13,510이 된다. 바인드 문장의 문제를 고치려다 일부러 쓴 상수 문장을 망가뜨린다. ③ 베이스라인 자동 수집: 되풀이 실행된 문장의 처음 본 계획을 ACCEPTED로 받아들이고, 그 뒤의 다른 계획은 받아들이지 않은 채 쌓아 둔다. S1이 A 등급으로 태어난 날 수집되면 NL이 고정되어, ACS가 C를 해시로 나누려 해도 쓸 수 없다. C 등급은 30,081블록이 계속된다. 첫 값의 함정을 잠깐이 아니라 영구히 만든다. 하루 1,940개씩 생기는 베이스라인의 관리 부담도 있다. 대신 고칠 것: 셋 모두 서버 스위치라 어느 값이 계획을 갈라야 하는지 모른다. 그 판단은 문장마다 SQL을 쓰는 사람이 한다. S1은 등급이 셋뿐이고 값마다 맞는 계획이 다르므로 등급마다 상수 문장으로 나눈다(커서 셋, 하드 파싱 세 번). A 372, B 3,035, C 5,858로 어느 날이든 맞는 계획을 탄다. 조회월은 값이 바뀌어도 계획이 같으므로 바인드로 둔다. S2와 S3는 고치지 않는다. 찾는 법과 범위: DBA_HIST_SQLSTAT에서 최근 2주 안에 계획 해시가 둘 이상이었던 문장을 실행당 일량의 차이가 큰 순으로 뽑고, 공유 풀에서 IS_BIND_SENSITIVE = Y인 문장을 함께 본다. 그중 실행당 일량이 열 배 넘게 갈리는 문장만 대상으로 삼아, 원인이 바인드 값인지(Peeked Binds, V$SQL_CS_*) 통계 변화인지 가린다. 바인드 값이 원인이면 그 문장만 상수 분리, 문장 모양 바꾸기, BIND_AWARE, 그 문장 하나의 베이스라인 중에서 고른다. 인스턴스 전체에 거는 것은 없다.

해설

인프라팀의 관찰은 맞다. 바인드 값 때문에 계획이 흔들리는 문장은 실제로 있다(S1, PARSE-002). 반박만 하지 말고 제안별 숫자를 나란히 적는다(A 372 → 5,858 · 취소 5,826 → 50,347 · C 30,081 계속).
SQL Plan Baseline 자체가 나쁜 것은 아니다. 문장 하나를 골라 검증한 계획을 넣는 데 쓰면 좋은 도구다. 문제는 "자동으로, 전부, 처음 본 계획"이라는 범위다(OPT 단원).
S1을 BIND_AWARE로 두는 것도 답이다. 화면 코드를 바꾸지 않아도 되지만, 등급마다 어느 계획을 타는지가 문장 밖(V$SQL_CS_SELECTIVITY)에 숨는다. 값이 셋인 코드는 상수 문장이 읽기 쉽다.
통계를 바꾸는 일(②)은 계획 해시가 바뀌는 문장을 미리 뽑아 전후를 비교하는 영향 분석과 함께 한다(교재 10화 통계 수집 정책서).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 상태 탭과 피킹 끄기 수치는 교재 11화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 상태 탭과 피킹 끄기 수치는 교재 11화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황