테마
13화 · 실습과 기록
여기서 하는 일: 커밋 주기를 재고, 작은 언두에서 ORA-30036과 ORA-01555를 직접 띄워 보고, 블록 덤프로 ITL을 읽고, 커밋 주기 기준을 쓴다.
1_사건.md를 읽은 것은 "선배가 하는 걸 지켜본 것"이고, 이 문서를 끝내야 직접 해 본 것이 된다. 개념이 막히면2_파고들기.md를 펼친다.시간이 없는 주에는 STEP 0 → 1 → 2 → 6 → 8만 한다(약 50분). SYS가 필요한 03, 04, 06은 건너뛰어도 본체는 끝난다.
이 화의 실습 지도
| 스크립트 | 무엇 | 계정 |
|---|---|---|
실습/00_준비와_점검.sql | 12화 종료 상태 확인 · 복사본·배치 함수·스냅샷 수첩 만들기 | sqlp |
실습/01_P1_커밋주기.sql | ★ 커밋 주기별 redo·undo·커밋·log file sync (본체) | sqlp |
실습/02_변경벡터와_커밋.sql | 변경 벡터 · COMMIT이 쓰는 것 · dirty 31 (WAL) | sqlp |
실습/03_작은언두_ORA30036_SYSDBA.sql | 작은 언두로 바꾸기 + 한 번 커밋 → ORA-30036 | SYS |
실습/04_churner.sql · 04_reader.sql | ★ 긴 읽기 + 동시 커밋 → ORA-01555 (두 세션) | sqlp |
실습/05_원복_SYSDBA.sql | ★★ 언두 원복. 반드시 돌린다 | SYS |
실습/06_지연클린아웃과_ITL_SYSDBA.sql | 지연 클린아웃 첫 읽기가 redo를 쓴다 · 블록 덤프 ITL | SYS |
실습/07_반쯤_반영.sql | 쪼갠 배치가 중간에 죽으면 반쯤 반영 (정합성) | sqlp |
실습/09_정리.sql | _C13 삭제 · 12화 상태 확인 · 언두 원복 확인 | sqlp |
⚠️ 03을 돌렸으면 반드시 05로 원복한다. 03은 인스턴스의 언두를 작게 바꾼다. 04를 마친 뒤 05를 돌리지 않으면 다른 실습까지 영향을 받는다. 원복됐는지는 09가 한 번 더 확인한다. PowerShell에서는 먼저
$OutputEncoding = New-Object System.Text.UTF8Encoding $false한 줄을 실행한다.
STEP 0 — 직전 리비전을 연다 · 전제를 확인한다 (5분) ★ 매주 처음 하는 일
bash
docker start ora-hanorder
cat 리비전/P1_rev00.sql # 오늘 '재는' 조각. 강도현 원본. 1,000건마다 COMMITP1은 01화부터 고치지 않고 들고만 온 조각이다. 오늘도 고치지 않는다. 읽고, 재고, 근거를 남길 뿐이다. 함수 본문의 커밋 구조와 그 위의 주석을 적어 둔다.
본문 : IF MOD(n,1000)=0 THEN COMMIT; END IF; (10만 건 적재)
주석 : 트랜잭션은 짧게 (2020.07 강도현)
근거 : 2020-07-22 hd.kang "야간 배치 중 UNDO 부족 장애. 트랜잭션 분할."그다음 전제를 점검하고 실습을 준비한다.
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 실습/00_준비와_점검.sql| 확인 | 기대 |
|---|---|
| [전제] big_rows · big_idx · stlm_rows · last_no · pkg | 1000000 · 3 · 100000 · 0 · VALID |
| 만든 것 | stlm_c13 100000 · idx 1 · p1_target 0 · 무효 0 |
00은 원본
T_ORD_STLM,T_ORD,PKG_ORD_STLM에는 아무것도 쓰지 않는다. 모두_C13복사본을 만들어 쓴다.
STEP 1 — 예측을 봉인한다 (5분)
Q1. P1 로 10만 건을 적재한다. '한 번만 COMMIT' 과 '1,000건마다 COMMIT' 의 redo 양은? 같다 / 1000쪽이 크다
Q2. '매 건 COMMIT' 하면 redo·undo 는 한 번 COMMIT 보다? 같다 / 약 2배 / 절반
Q3. PL/SQL 루프에서 10만 번 COMMIT 하면 내 세션의 log file sync 대기는 몇 번? ______
Q4. 보정 배치를 '한 번만 COMMIT' 으로 작은 언두에서 돌리면 무슨 에러? ______
Q5. '1,000건마다 COMMIT' 으로 (긴 읽기와 함께) 돌리면 무슨 에러? ______
Q6. 커밋했는데 그 데이터 블록이 디스크에 안 쓰였다. 왜 안전한가? ______▶ 적고 나서 펼치기
| 대부분의 답 | 실측 | |
|---|---|---|
| Q1 | "1000쪽이 크다" | 거의 같다 (둘 다 redo 약 3,000만 · undo 약 680만) |
| Q2 | "같거나 적다" | 약 2배 (redo 약 5,700만 · undo 약 1,360만). 과하다 |
| Q3 | "10만 번" | 2번 (PL/SQL 커밋에서는 세션이 매번 기다리지 않는다). 단, COMMIT WRITE WAIT면 커밋 수만큼 |
| Q4 | "언두 부족" | ORA-30036 (맞다. 큰 트랜잭션이 언두를 다 쥔다) |
| Q5 | "에러 안 남" / "같은 에러" | ORA-01555 (반대쪽 장애. 긴 읽기의 과거가 사라진다) |
| Q6 | "안 안전하다" | 바꾼 내용이 redo(WAL)로 디스크에 있다. 블록은 DBWn이 나중에 쓴다 |
STEP 2 — 커밋 주기를 잰다 (10분) ★ 오늘의 본체
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 실습/01_P1_커밋주기.sql| 열 | 기대 | ☐ |
|---|---|---|
| once vs 1000 | redo와 undo가 거의 같다 (약 3,000만 · 680만) | ☐ |
| each | redo 약 5,700만 (1.9배) · undo 약 1,360만 (2배) · 논리읽기 약 3.4배 | ☐ |
| log file sync (세션) | once·1000·each 모두 2 · WAIT는 약 100 | ☐ |
| LGWR log file parallel write | once 약 50 → 1000 약 120 → each 약 650 (LGWR가 쓰는 횟수는 돌릴 때마다 몇십씩 달라진다) | ☐ |
눈으로 짚을 것
☐ '자주 커밋하면 빨라진다' 가 왜 틀렸나? → each 가 redo·undo·논리읽기 모두 최악
☐ log file sync 가 2 인데 LGWR write 는 왜 늘어나나? → 세션은 안 기다려도 LGWR 은 더 자주 쓴다
☐ COMMIT WRITE WAIT 의 log file sync 가 100 인 이유? → '기다려라' 를 켜면 커밋마다 디스크를 기다린다P1은 fetch across commit이 아니다. 바뀌지 않는
T_ORD를 읽어 '빈' 목적지에 넣는다. 그래서 P1의 1,000건 커밋은 ORA-01555를 부르지 않는다. 그 함정은 보정 배치(STEP 4)에서 본다.
STEP 3 — 변경 벡터 · COMMIT · dirty (10분)
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 실습/02_변경벡터와_커밋.sql| 블록 | 확인 | 기대 | ☐ |
|---|---|---|---|
| [A] | 한 행 UPDATE | redo entries 약 5 · undo change vector 약 464바이트 (바꾸기 전 값) | ☐ |
| [B] | COMMIT 하나 | redo 약 1,212바이트 (커밋 레코드) · undo 거의 안 늚 · log file sync +1 | ☐ |
| [C] | dirty: CTAS 직후 → UPDATE+COMMIT 뒤 | 4 → 31 | ☐ |
📌 [C]의 31 — 07화가 넘긴 그 숫자
COMMIT뒤에도 그 블록은 dirty다. 커밋은 데이터 블록을 디스크에 쓰지 않는다. 그래도 안전한 이유는 바꾼 내용이 redo(WAL)로 디스크에 적혀 있기 때문이다. 블록은 DBWn이 체크포인트 때 쓴다.
STEP 4 — ORA-30036과 ORA-01555 (20분) ★ SYS 필요
⚠️ 여기서 돌리는 03과 04는 언두를 작게 바꾼다. 05로 반드시 원복한다. 시간이 없으면 STEP 6과 8로 건너뛴다.
① 작은 언두 + 한 번 커밋 → ORA-30036 (실습/03)
bash
MSYS_NO_PATHCONV=1 docker exec -i ora-hanorder sqlplus -s / as sysdba < 실습/03_작은언두_ORA30036_SYSDBA.sql기대: 언두가 UNDO_C13(6M), retention 1로 바뀌고, 한 번 커밋 배치가 약 42,000행째에서 ORA-30036으로 죽는다.
☐ 큰 트랜잭션이 왜 언두를 다 쥐나? → 커밋해야 놓는다. 한 번에 다 하면 끝까지 쥔다 = 강도현 2020.07② 긴 읽기 + 동시 커밋 → ORA-01555 (실습/04, 두 세션)
일꾼(등록 화면 = churner)을 먼저 백그라운드로 띄우고, 보정 배치의 긴 읽기(reader)를 돌린다.
bash
H="docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1"
$H < 실습/04_churner.sql > /dev/null & $H < 실습/04_reader.sql ; waitpowershell
$OutputEncoding = New-Object System.Text.UTF8Encoding $false
Start-Job { Get-Content 실습\04_churner.sql -Encoding UTF8 | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 } | Out-Null
Get-Content 실습\04_reader.sql -Encoding UTF8 | docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1
Get-Job | Wait-Job | Remove-Job기대: 긴 읽기가 약 200행째에서 ORA-01555로 죽는다(일꾼이 20초 동안 언두를 덮어쓴다).
☐ 왜 긴 읽기가 죽나? → 읽는 그 테이블을 누군가 고치고 커밋해서, '읽던 시점' 의 과거가 사라졌다
☐ 언두를 늘리면 이 에러가 풀릴까? → 30036 은 풀리지만 01555 는 안 풀린다 (사라진 과거는 안 돌아온다)③ ★★ 원복 (실습/05) — 반드시
bash
MSYS_NO_PATHCONV=1 docker exec -i ora-hanorder sqlplus -s / as sysdba < 실습/05_원복_SYSDBA.sql기대: UNDOTBS1 · 900 · 0(UNDO_C13 삭제). 이 줄을 확인하기 전에는 끝난 게 아니다.
STEP 5 — 지연 클린아웃 · 블록 덤프로 ITL 읽기 (15분, 선택, SYS 필요)
bash
MSYS_NO_PATHCONV=1 docker exec -i ora-hanorder sqlplus -s / as sysdba < 실습/06_지연클린아웃과_ITL_SYSDBA.sql| 블록 | 기대 | ☐ |
|---|---|---|
| [A] 지연 클린아웃 | 첫 읽기 redo 약 152,000바이트 · cleanouts 약 1,694 / 둘째 읽기 거의 0 | ☐ |
| [B] 블록 덤프 | --U-(활성) → 커밋 후 C--- · ----(빈 칸) | ☐ |
덤프한 블록의 ITL을 트레이스에서 꺼내 읽는다(경로는 06 출력의 TRC에 나온다).
bash
MSYS_NO_PATHCONV=1 docker exec ora-hanorder bash -c "grep -n -A3 'Itl' <TRC 경로>"☐ Flag --U- 의 Lck 가 1 인가? → 이 칸이 이 블록에서 한 행을 잠갔다
☐ Xid(usn.slot.wrap)가 12화의 트랜잭션 이름표와 같은 꼴인가?
☐ COMMIT 뒤 그 칸이 C--- 로 바뀌고 커밋 SCN 이 적혔나?읽었을 뿐인데 redo를 쓴다(지연 클린아웃). 01화의 '설치 직후 5,842'도 이것으로 설명하려 했지만, 재 보면 APPEND 적재에는 지연 클린아웃이 없다. 5,842의 추가분은 첫 파싱과 재귀 조회다. →
2_파고들기 §4
STEP 6 — 반쯤 반영 (10분) ★ 정합성
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 실습/07_반쯤_반영.sql기대: 같은 배치가 50,000행째에서 장애를 만난다. 한 번 커밋 = 반영 0 / 1,000건 커밋 = 반영 50,000.
☐ 한 번 커밋이 왜 0 인가? → 전부 아니면 전무. ROLLBACK 이 다 되돌린다
☐ 1,000건 커밋이 왜 50,000 인가? → 이미 커밋한 만큼은 남는다 (반쯤 반영)
☐ 토요일에 죽은 오과장 배치는 지금 어떤 상태인가? → 반쯤 보정. 다시 돌리면 이중 보정 위험STEP 7 — 커밋 주기 기준 ★ 새 산출물
이 화에서 만드는 표준이다. 양식은 커밋주기_기준.md에 있다.
배치나 루프를 짤 때 위에서부터 차례로 묻고, 답이 나오면 멈춘다.
1. 집합 연산 한 문장으로 쓸 수 있나? ──예──▶ 그렇게 쓴다 (커밋 한 번). 언두 걱정이면 14화
2. 구동 커서가 읽는 테이블을 그 루프가 고치며 COMMIT 하나? (fetch across commit)
──예──▶ 읽기와 쓰기를 분리한다 (목록 선적재 · 키 범위 · 집합 연산)
3. 한 트랜잭션 언두가 언두 TS 에 비해 큰가? ──예──▶ 끊는다 (너무 잘게는 X) + 재시작 지점 설계
4. 그 밖 ──────▶ 한 번에 COMMIT (원자성이 따라온다)★ 12화 포스트모템 1호의 빈 「리스크」 칸을 채운다
12화 재발방지 2번 "배치는 N건마다 COMMIT"의 리스크 칸이 비어 있었다. 오늘 그 칸을 채운다.
리스크:
- N 건마다 COMMIT 이 fetch across commit 이면 긴 커서가 ORA-01555 로 죽는다.
→ 커밋 주기가 아니라 '읽기와 쓰기 분리' 로 푼다.
- 쪼갠 배치가 중간에 죽으면 '반쯤 반영' 이 남는다 (전부 아니면 전무가 깨진다).
→ 재시작 지점 설계 + 멱등. 정합성 회귀 체크리스트 E·H.
- 반대쪽: 한 번에 COMMIT 하면 큰 트랜잭션이 언두를 다 쥐어 ORA-30036. 주기만으로는 둘 다 못 피한다.STEP 8 — T1 / T4 혼합 리포트 10호 ★ 오늘의 산출물
오늘은 조각의 측정(T1)과 장애(T4)가 섞인다. 토요일 보정 배치 장애는 T4로, P1과 커밋 주기 측정은 T1으로 쓴다.
양식: T1 · T4 · 정합성 회귀 체크리스트
┌──────────────────────────────────────────────────────────────────┐
│ T4 장애 포스트모템 — 2호 (T1 측정 동봉) │
├──────────────────────────────────────────────────────────────────┤
│ 1. 사건 요약 : 5/25(토) 새벽 주간 보정 배치 ORA-01555 로 중단 │
│ 2. 타임라인 : 5/20 포스트모템 재발방지 2번(1000건 COMMIT) → │
│ 5/2x 오과장 적용 → 5/25 새벽 첫 실행 ORA-01555 │
│ 6. 5 Whys : 왜 죽었나 → fetch across commit · 긴 읽기 과거 소실 │
│ 왜 그 구조였나 → '커밋을 짧게' 를 읽으며 고치는 배치에 적용 │
│ 왜 그 처방이 나왔나 → 12화 리스크 칸이 비어 있었다 │
│ 7. 조치 : 즉시 반쯤 반영 확인 · 재실행 보류 | 항구 읽기/쓰기 분리(14화) │
│ 9. 정합성 : 반쯤 반영 (E·H) — 반영 N / 미반영 (100000-N) │
│10. 재발방지 : 「커밋 주기 기준」 제정 · 12화 2번 리스크 칸 회수 │
├──────────────────────────────────────────────────────────────────┤
│ [T1 동봉] P1 적재 커밋 주기 : once≈1000 (redo 3천만) · each 2배 │
│ log file sync : PL/SQL 2 · WAIT 100 │
└──────────────────────────────────────────────────────────────────┘9번 「정합성」 — 반쯤 반영은 반드시 넣는다 (E·H)
| # | 항목 | 이 화에서 |
|---|---|---|
| E | 경계 데이터 | 반쯤 반영: 배치가 중간에 죽은 상태 (실습/07: 반영 50,000 / 미반영 50,000) |
| H | 영향 분석 | 재실행할 때 이미 보정된 건을 또 보정하나 (이중 보정) · 재시작 지점·멱등 여부 |
STEP 9 — 서술 답안 (400~600자)
오과장에게 보낼 회신을 쓴다. "1,000건으로 바꿨는데 왜 죽었나"에 답하는 글이다.
▶ 선배의 첨삭 포인트 (쓰고 나서)
① "커밋을 자주 해서"로 끝내지 않는다. 실측에서 1,000건 커밋 자체로는 redo와 undo가 거의 늘지 않았다. 원인은 읽으면서 고치고 커밋하는 구조(fetch across commit)다.
② 처방을 바꾼다. "커밋 간격을 늘려라"가 아니라 "읽기와 쓰기를 분리하라"고 써야 한다. 간격을 늘려도 긴 커서가 열려 있으면 과거는 계속 풀린다.
③ 반쯤 반영부터 짚는다. 다시 돌리기 전에 토요일에 어디까지 보정됐는지 확인해 달라고 하고, 이중 보정 위험을 경고한다.
④ 숫자 하나를 넣는다. "한 번 커밋과 1,000건 커밋의 redo가 거의 같다(약 3,000만)". 이 숫자를 들면 '짧게'가 비용 문제가 아니었고 '구조'가 문제였다는 것을 보여 줄 수 있다.
STEP 10 — 파인만 자가설명
1단 백지 설명 → 2단 막힘 태그(최소 1개) → 3단 원천 복귀 → 4단 재설명 순서로 진행한다.
| 태그 | 전형적인 문장 |
|---|---|
#인과 | "커밋을 자주 하면 왜 ORA-01555가 가까워지고, 왜 ORA-30036은 멀어지는지 설명하다가 막혔다" → §6 |
#용어 | "change vector · redo record · undo · CR 블록을 말로 구분 못 하겠다" → §1 · §4.5 |
#수치 | "ITL의 --U- · C---를 못 읽겠다 / Xid를 usn.slot.wrap으로 못 풀겠다" → §3 |
4단 되묻기 3문 (오늘 버전)
- 조건 반전: "보정 배치가 읽는 테이블과 고치는 테이블이 다르면(= P1처럼) 1,000건 커밋은 안전한가?"
- 반례 요구: "커밋을 더 드물게 해서(한 번에) 01555를 피하려 하면 어떤 대가를 치르나?"
- 측정 지목: "'이 배치는 fetch across commit이라 위험하다'를 증명하려면 무엇을 재야 하나?"
태그는 _막힘로그.md로 옮긴다.
정리 — 한빛유통을 12화 종료 상태로
bash
docker exec -i ora-hanorder sqlplus -s sqlp/sqlp@localhost/FREEPDB1 < 실습/09_정리.sql기대: c13_left 0 · stlm_rows 100000 · last_no 0 · pkg VALID · big_idx 3 · undo_tablespace UNDOTBS1
undo_tablespace가 UNDOTBS1이 아니면 03을 돌리고 05를 안 돌린 것이다.실습/05_원복_SYSDBA.sql을 SYS로 돌린다.
완료 체크리스트
- [ ] STEP 0:
P1_rev00의 커밋 구조와 2020.07 근거를 적었다 - [ ] STEP 1: 보기 전에 예측 6개를 적었다
- [ ] STEP 2: once≈1000, each 약 2배, log file sync 2(WAIT 100)를 확인했다
- [ ] STEP 3: 한 행 UPDATE의 undo change vector와 COMMIT 뒤 dirty 31을 봤다
- [ ] STEP 4: 작은 언두에서 ORA-30036(한 번 커밋)과 ORA-01555(긴 읽기+동시 커밋)를 띄웠다
- [ ] STEP 4: ★ 05로 언두를 원복하고 UNDOTBS1·900·0을 확인했다
- [ ] STEP 5: (선택) 지연 클린아웃 첫 읽기의 redo와 블록 덤프 ITL
--U-/C---를 봤다 - [ ] STEP 6: 반쯤 반영(0 vs 50,000)을 확인했다
- [ ] STEP 7: 커밋 주기 기준을 쓰고 12화 2번 리스크 칸을 채웠다
- [ ] STEP 8: T4 2호(+T1)를 쓰고 정합성에 반쯤 반영(E·H)을 넣었다
- [ ] STEP 10: 막힘 태그를 최소 1개
_막힘로그.md에 옮겼다
완결성 게이트: STEP 7의 리스크 칸 회수, STEP 4의 원복 확인, STEP 6의 반쯤 반영이 비어 있으면 이 화는 미완성이다.
다음 화로 넘기는 상태
| 무엇 | 값 |
|---|---|
ora-hanorder | 변화 없음: T_ORD_STLM 100,000 · T_STLM_NUM LAST_NO 0 · PKG_ORD_STLM 원본 · T_ORD_BIG 인덱스 3 · 언두 UNDOTBS1/900 · _C13 0 |
P1 현재 리비전 | rev00 (전진하지 않았다. 측정으로 뒷받침되지 않았고, 1,000건 커밋은 지금은 안전하다) |
| 새 표준 | 커밋 주기 기준 · T4 2호 |
| 회수 | 12화 포스트모템 1호 재발방지 2번의 빈 「리스크」 칸 |
| 열어 둔 것 | "배치를 어떻게 빠르고 안전하게 돌리나" (집합 연산·APPEND·파티션·병렬) → 14화 · PL/SQL 루프 비용 → 15화 |
다음 → 14화 「야간 배치가 아침을 넘긴다」: 오늘은 왜 터지고 왜 느린지까지 봤다. 14화에서는 P1을 집합 연산으로 다시 쓰고, 04화 인덱스와 05화 HWM의 적재 청구서를 받는다.