테마
힌트 유효기간 대장 (템플릿)
06화에서 제정했다. T2 「인덱스·힌트 변경 승인서」의 자매 문서다. T2가 "지금 바꿔도 되는가"를 묻는다면, 이 대장은 "언제 다시 물어야 하는가"를 못 박아 둔다.
왜 이 문서가 있는가
2018-11-06 성능개선 회의록
3. 등급별 매출 집계 화면 지연
- 현상 : 조회 시 약 3초
- 조치 : 조인 방식을 USE_NL 로 고정
- 결과 : 3초 -> 0.4초
- 결론 : 적용 승인이 회의록에 틀린 줄은 하나도 없다. 현상도, 조치도, 결과도, 승인도 사실이었다. 그런데 몇 년 뒤, 그 힌트 때문에 조회 한 번에 102만 블록을 더 읽고 있었다.
빠진 것은 두 줄이었다.
| 빠진 것 | 결과 |
|---|---|
| "그때 데이터는 2만 건이었다" | 데이터가 50배가 됐는데도 그것을 판단이 바뀔 일로 연결 지은 사람이 없었다 |
| "언제 다시 잴 것인가" | 다시 잴 책임이 누구에게도 없었다 |
힌트는 성능 지식이 아니라 그 시점의 데이터에 대한 판단이다.판단을 기록할 때 전제를 같이 적지 않으면, 전제가 바뀐 것을 아무도 알아채지 못한다.
작성 규칙
| 규칙 | |
|---|---|
| 힌트 하나당 한 행 | 같은 SQL에 힌트가 3개면 3행이다 |
| 「그때의 데이터 규모」와 「다음 재검토 조건」은 비울 수 없다 | 비어 있으면 반려한다. 이 문서는 이 두 칸 때문에 있다 |
| 재검토 조건은 숫자로 쓴다 | "나중에", "필요시"는 쓰지 않는다. "M_CUS가 20,000행을 넘으면"처럼 쓴다 |
| 유지 판정도 기록한다 | 힌트를 떼려고 만든 문서가 아니다. 전제를 적어 두려고 만든 문서다 |
| 힌트가 없는 선택도 올린다 | 힌트를 뗀 결정도 데이터에 기댄 판단이다. 똑같이 늙는다 |
| 알 수 없으면 「불명」이라고 적는다 | 빈칸과 「불명」은 다르다. 「불명」은 조사 과제가 된다 |
양식
| # | 힌트 | 위치 (조각/SQL_ID) | 붙인 시점 | 그때의 근거 | 그때의 데이터 규모 ★ | 그때의 효과 | 현재 규모 | 현재 재측정값 | 판정 | 다음 재검토 조건 ★ | 담당 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | |||||||||||
| 2 |
열 설명
| 열 | 적는 것 | 비었을 때 |
|---|---|---|
| 힌트 | 힌트 문자열 그대로. /*+ ... */ 안의 한 조각 | — |
| 위치 | 조각 ID + SQL_ID + 소스 파일·줄 | 찾을 수가 없다 |
| 붙인 시점 | 커밋 날짜 / 회의 날짜 | 「불명」 |
| 그때의 근거 | 커밋 로그 · 회의록 · 장애 리포트의 문서 ID | 「근거 못 찾음」 ← 그 자체가 위험 신호다 |
| 그때의 데이터 규모 ★ | 관련 테이블의 행수. 추정이면 "추정"이라고 쓴다 | 반려 |
| 그때의 효과 | 그때 기록된 수치(초 단위여도 그대로 옮긴다) | 「불명」 |
| 현재 규모 | 오늘의 행수 + 몇 배인가 | 반려 |
| 현재 재측정값 | 힌트 있음 / 없음 양쪽의 Buffers. T0 규약대로 | 반려 |
| 판정 | 폐기 / 유지 / 재검토 / 조사필요 | 반려 |
| 다음 재검토 조건 ★ | 숫자 조건 또는 주기. 둘 다면 더 좋다 | 반려 |
| 담당 | 재검토 조건이 걸렸을 때 잴 사람 | 반려 |
판정 기준
| 판정 | 언제 |
|---|---|
| 폐기 | 힌트를 뺀 쪽이 더 싸고, 결과가 같고, 환경 의존성을 확인했다 |
| 유지 | 힌트 쪽이 여전히 싸다. 그 이유와 전제를 같은 행에 적는다 |
| 재검토 | 지금은 차이가 작지만 추세로 보면 뒤집힌다. 조건을 걸어 둔다 |
| 조사필요 | 근거를 못 찾았다. 떼기 전에 왜 붙었는지부터 찾는다 |
⚠️ 「조사필요」를 그냥 「폐기」로 넘기지 않는다
근거를 못 찾은 힌트는 이유 없이 붙은 힌트가 아니라 이유를 아직 못 찾은 힌트다. 02화에서 본 것처럼, 그 이유가 성능이 아니라 정합성일 수도 있다. 느린 코드를 빠르게 만드는 것이 튜닝이고, 맞는 코드를 틀리게 만드는 것은 장애다.
작성 예 (06화 · 1호)
| # | 힌트 | 위치 | 붙인 시점 | 그때의 근거 | 그때의 규모 | 그때의 효과 | 현재 규모 | 현재 재측정값 | 판정 | 다음 재검토 조건 | 담당 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | USE_NL(c) | PKG_ORD_STLM.Q2 | 2018-11-06 | 2018-11-06 성능개선 회의록 안건3 | 주문 20,000 · M_CUS 2,000 | 3초 → 0.4초 | 주문 1,000,000 (50배) | NL 1,026,849 / 해시 5,869 | 폐기 | — | (튜너) |
| 2 | LEADING(o c) | PKG_ORD_STLM.Q2 | 2018-11-06 | 〃 (1번과 한 세트) | 〃 | 〃 | 〃 | 〃 | 폐기 | — | (튜너) |
| 3 | (힌트 없음 = 해시 자율선택) | PKG_ORD_STLM.Q2 rev01 | 4월 8일 (06화) 〔18화 정정: 처음에는 04화의 날짜(3월 25일)가 연도와 함께 적혀 있었다〕 | 06화 T1 리포트 4호 | 주문 1,000,000 · M_CUS 2,000(30블록) · PGA 512M | 1,026,849 → 5,869 | 〃 | 〃 | 유지 | M_CUS 20,000행 초과 시 또는 pga_aggregate_target 변경 시 또는 6개월 | (튜너) |
3번 행이 이 대장의 핵심이다
오늘 내가 내린 판단도 똑같이 늙는다. 차이는 하나뿐이다. 2018년의 판단에는 전제가 적혀 있지 않았고, 오늘의 판단에는 적혀 있다.
3번 행의 전제가 왜 이 세 가지인지는 다음과 같다.
| 전제 | 왜 |
|---|---|
M_CUS 2,000행 (30블록) | 해시의 상수항이 30블록이라 손익분기점이 21건이다. M_CUS가 커지면 분기점이 뒤로 밀린다 |
| PGA 512M | 해시 조인이 optimal(0 패스)로 돌 수 있는 전제다. 모자라면 Buffers는 그대로인 채 수십 배 느려진다 |
| 6개월 | 숫자 조건에 걸리지 않아도 시간만으로 한 번은 다시 본다 |
18화 졸업 재측정 (7월 8일 · ora-hanorder · 대장 전체)
운용표의 마지막 줄은 "졸업 판정 (18화): 대장 전체를 재측정한다."이다. 3번 행의 '붙인 시점'에는 연도가 박혀 있었고 날짜도 06화의 것이 아니었다. 정정 표시를 남기고 고쳤다. 대장에 없던 힌트가 하나 있었다. 14화에서 붙인 P1 rev02의
APPEND힌트다. 힌트를 붙인 그 자리에서 행을 올린다는 규칙이 지켜지지 않았다. 5번 행으로 올린다.
| # | 힌트 | 위치 | 붙인 시점 | 그때의 근거 | 그때의 규모 | 그때의 효과 | 현재 규모 (18화) | 현재 재측정값 (18화) | 판정 | 다음 재검토 조건 | 담당 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | USE_NL(c) | PKG_ORD_STLM.Q2 rev00 | 2018-11-06 | 회의록 안건3 | 주문 20,000 · M_CUS 2,000 | 3초 → 0.4초 | 주문 1,000,000 · M_CUS 2,000 | rev00 그대로 돌리면 1,026,849 (계획 2289018667: 01화와 같은 계획 · 같은 블록) | 폐기 (확인) | — | (튜너) |
| 2 | LEADING(o c) | 〃 | 〃 | 〃 | 〃 | 〃 | 〃 | 〃 | 폐기 (확인) | — | (튜너) |
| 3 | (힌트 없음 = 해시 자율선택) | Q2 rev01 | 4월 8일 (06화) | T1 4호 | 주문 1,000,000 · M_CUS 2,000 (30블록) · PGA 512M | 1,026,849 → 5,869 | 주문 1,000,000 · M_CUS 2,000 (28블록) · PGA 512M (pga_aggregate_target 536870912) | 5,868 · CPU 10회 115cs | 대체: rev02로 (조건에 걸려서가 아니라 T1 12호의 CPU 근거로) | — | (튜너) |
| 4 | (힌트 없음 = 먼저 묶기 · 인라인 뷰 GROUP BY CUS_ID) | Q2 rev02 | 7월 8일 (18화) | T1 12호 | 주문 1,000,000 · M_CUS 2,000 · 해시 조인 입력 1,000,000 → 2,000 · PGA 512M | Buffers 5,868 → 5,868 · CPU 10회 115 → 33cs | 〃 | 〃 | 채택 | M_CUS 20,000행 초과 (묶은 결과가 고객 수만큼 커진다: HASH GROUP BY 일감) · pga_aggregate_target 변경 · 고객 1인당 주문 수가 10건 아래로 (지금 500. 묶는 이득이 줄어든다) · 6개월 (1월 8일) | (튜너) |
| 5 | APPEND | PKG_ORD_STLM.P1 rev02 | 6월 3일 (14화) · 18화에 소급 등록 | 14화 T3 1호 · P1_rev02.sql | 적재 100,000행 · NOARCHIVELOG · 적재 중 같은 테이블에 쓰는 세션 없음 (야간) | 0.11초 · redo 0.08MB · undo 0.02MB | 적재 100,000행 · NOARCHIVELOG (V$DATABASE.LOG_MODE) | 복사본 90ms · 논리 읽기 2,875 · redo 0.10MB · undo 0.03MB · 커밋 2 | 유지 | ARCHIVELOG로 바뀔 때 (14화: redo가 작은 것은 NOARCHIVELOG라서다) · 낮에 돌리게 될 때 (14화 §4.1: 직접 경로는 그 테이블에 TM 6) · T_ORD_STLM에 FK(자식 쪽) · 트리거가 생길 때 (14화 §4.3: APPEND가 조용히 꺼진다) · 적재 행수 10배 · 6개월 (12월 3일) | (튜너) |
1~2번의 재측정은 "힌트를 다시 붙이면 어떻게 되나"를 잰 것이다. 운영판에는 힌트가 없다. 다시 붙이면 지금도 1026K이므로 지금도 폐기가 옳다. 4번의 재검토 조건 '고객 1인당 주문 수'는 이 리비전의 이득이 어디서 오는지(해시 조인에 대어 보는 행 1,000,000 → 2,000)를 보고 정했다. 조건을 숫자로 적을 수 있었던 것은 이득의 출처를 쟀기 때문이다.
운용
| 언제 | 무엇 |
|---|---|
| 힌트를 새로 붙일 때 | 그 자리에서 한 행을 추가한다. 전제를 적기 전에는 커밋하지 않는다 |
| 힌트를 뗄 때 | 「폐기」로 판정하고, 뗀 선택을 새 행으로 올린다 |
| 재검토 조건이 걸렸을 때 | 담당자가 재측정 → 현재 규모·재측정값·판정 갱신 |
| 정기 점검 (T8) | 모든 행의 현재 규모를 갱신하고, 조건에 걸린 행을 뽑는다 |
| 졸업 판정 (18화) | 대장 전체를 재측정한다. 「조사필요」가 남아 있으면 미완성이다 |
규모 갱신용 쿼리
sql
-- 대장의 '현재 규모' 칸을 채운다. 통계가 최신인지부터 확인한다
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables
WHERE table_name IN ('T_ORD_BIG','M_CUS','M_RGN','T_ORD')
ORDER BY table_name;힌트가 박힌 SQL을 찾는 쿼리
sql
-- 공유 풀에 올라와 있는 것 중 힌트를 달고 있는 SQL
SELECT sql_id, plan_hash_value, executions, buffer_gets, SUBSTR(sql_text,1,120) AS head
FROM v$sql
WHERE sql_text LIKE '%/*+%'
AND parsing_schema_name = USER
ORDER BY buffer_gets DESC;공유 풀에서 밀려난 SQL은 잡히지 않는다. 소스 전수 검색이 정본이고 이 쿼리는 보조다.
관련: T1_단건SQL_튜닝리포트.md · 06화 2_파고들기.md §8
개정 이력: 06화에 제정했다(1~3번 행). 18화에 졸업 재측정을 하면서 3번 행 날짜를 정정하고 4번(Q2 rev02) · 5번(
APPEND소급 등록) 행을 추가했다.