15화 · SQL은 다 고쳤는데 왜 느린가

🗓️ 6월 10일 (월) · 14화에서 일주일 뒤 · 무대 ora-hanorder(한빛유통) 오늘 배울 것: 배치 시간을 잡아먹는 것은 SQL 한 번의 비용이 아니라 SQL로 건너가는 횟수다. 그리고 한 문장으로 바꿀 수 있어도, 바꾸면 무언가를 잃는 경우가 있다. 오늘 다룰 조각은 P1 완결(rev02에서 끝나고 rev03은 없다)과 정산 배치의 나머지 반쪽인 주간 보정 배치(ADJ_rev00 → rev01)다. 16화와 17화에서 쓸 부하 발생기(PKG_LOAD_DRIVER)도 만든다.

파일 세 개

	파일	무엇을 하나	시간
읽는다	1_사건.md	"SQL은 다 빠르다"는 보정 배치의 루프 안을 열어 보고, 지하 수발실에 내려가 보고, 트레이스 한 줄을 읽는다. 한 문장으로 바꿨다가 루프가 숨는 걸 보고, 에러 로그가 깨끗했던 170건을 찾고, PL/SQL로 제대로 쓴 뒤, 다음 주에 쓸 부하 발생기까지 만드는 하루	40분
한다	3_실습과_기록.md	루프의 내부 · 10046/TKPROF · raw 한 줄 · 보정 rev00 · 한 문장의 함정 · rev01 · HPROF · NOCOPY/패키지 상태 · 부하 발생기 시운전 · T3 2호	90~150분
찾는다	2_파고들기.md	왕복 한 번의 비용 · `PLSQL_OPTIMIZE_LEVEL` · raw 10046 필드 · TKPROF 판독 · BULK/FORALL/`INDICES OF`/`SAVE EXCEPTIONS` · 패키지 상태와 UGA · NOCOPY · 한 문장의 함정 · `WHEN OTHERS THEN NULL` · HPROF	필요할 때

1을 읽고 3을 따라 한다. 2는 막힐 때 펼친다. 시간이 없는 주에는 1_사건을 중단 지점 1까지 읽고 3_실습은 STEP 0, 1, 2, 4, 6, 9, 10만 한다(약 60분). STEP 9(부하 발생기)는 빼면 안 된다. 16화가 그 위에서 시작한다.

지난 화에서 들고 오는 것


14화가 넘긴 질문	"커서 FOR 루프가 왜 느린가": P1 rev00 0.89초와 rev01 0.08초 사이의 속사정 · 한 문장으로 못 바꾸는 행 단위 로직
14화 T3 1호	9번 칸 「PL/SQL 호출 통계」가 비어 있다("15화 2호 예정") · 재발방지 1번(단계별 시각)
13화 저녁	"읽기와 쓰기를 떼어 내는 쪽으로 같이 고쳐요". 하지만 주간 보정 배치는 14화 체인 사고에 밀려 오과장이 혼자 고쳤다
12화 · 13화	보정 배치의 1,000건 커밋(12화 처방) · fetch across commit(13화) · 반쯤 반영 · 「커밋 주기 기준」
08화	「정합성 회귀 체크리스트」, 특히 A(기준은 원장)와 E(경계: 1 쪽에 없는 키)
07화 · 06화	스칼라 서브쿼리 캐시의 '칸' · 4층 거래처 카드함(행마다 찾아가는 조회)

그리고 월요일 아침, 메시지가 세 통 와 있다.

"일요일 체인, 처음으로 단계마다 시각 찍었어요. 01시 47분에 끝났어요." (오과장, 08:58) "지난주 보정은 10시 52분에 끝나서 11시 확정을 8분 남기고 했어요. 그리고 보정 건수가 정산 건수보다 적었어요. 에러 로그는 깨끗하대요." (조민우, 09:06) "6조각 SQL은 다 고쳤다고 보고받았습니다. 그런데 왜 아직도 느린 겁니까?" (박팀장, 09:15)

오늘의 전개 — 두 번 틀리고, 한 번 더 데인다

	내가 믿은 것	측정(연습 DB 복사본)
09:30	"커서 FOR 루프가 한 건씩 가져와서 느리다"	한 문장은 3블록 · 1블록이다. 그런데 P1 rev00의 구동 커서 fetch는 1,001로, 레벨 2에서는 이미 100행씩 가져온다. 가져오기는 4cs이고 나머지는 루프 안 INSERT 10만 번이다(recursive calls 101,350 vs 183)
10:20	(비유) 지하 1층 수발실: 전표 한 장에 행낭 하나	PL/SQL → SQL 왕복 한 번 약 2.5µs · SQL → PL/SQL 약 0.53µs
11:00	트레이스 한 줄	보정 rev00 RECURSIVE Execute 299,865 · 등급 조회 `Fetch ≈ Execute` = 100,020 · `cr=3 r=1 dep=1`
11:40	"MERGE 한 문장에 함수를 넣으면 된다"	먼저 ORA-01400(함수의 `NO_DATA_FOUND`가 SQL 안에서는 NULL이 된다) → `LOG ERRORS`로 끝까지 돌리니 함수 호출은 100,020 그대로 · 스칼라 서브쿼리 캐시 57,679
13:00	(정합성) 에러 로그가 깨끗했던 이유	원장 100,020건 중 170건이 보정도 기록도 없이 빠졌다(탈퇴 고객 150 · 금액 미확정 20) · `WHEN OTHERS THEN NULL` · 화면에는 '보정 0원'
13:40	시간은 어느 부름 아래에서 썼나(HPROF)	INSERT 99,870 부름 1.50초 · 함수 100,020 부름 0.41초 · `COUNT(*)` 0.37초 · 페치 1,001 부름 0.02초
14:20	"FORALL로 묶으면 된다"	원소마다 `MERGE` → 130cs · 논리 읽기 426,669(일은 그대로) → FORALL INSERT/UPDATE · 등급표 · 반송함 = rev01 27~28cs · 22,363 · `ERROR_INDEX` 6 vs 8
15:30	한 문장이면 안 되나	CASE로 옮기면 23~32cs(규칙이 두 곳에 생긴다) · `MATERIALIZE` 25~34cs(힌트를 빼면 함수 99,850번) · `LOG ERRORS` 하나로 23 → 105~117cs
16:30	"등급표를 패키지 변수에"	UGA +262~327KB · 재컴파일 → ORA-04068(그 세션의 상태 전부, `DBMS_OUTPUT`까지) · 상수 하나로도 상태가 생긴다
17:10	다음 주에 쓸 물건	`PKG_LOAD_DRIVER`: STEADY · SPIKE 2세션 × 3분 · 에러 0 · 원본 지문 불변 · 남은 세션 0

왜 이렇게 짰을까? 14화가 "한 문장으로 바꿀 수 있나"를 물었다면, 15화는 "바꾸면 무엇을 잃나"를 묻는다. 부하 발생기는 이 화의 교훈(바인드 변수, 묶어서 보내기, 에러를 삼키지 않기, 상태 없는 패키지)을 모두 지킨 잘 쓴 PL/SQL의 실물이다. 16화는 그 위에서 시작한다.

오늘 배우는 한 문장

SQL이 빠른 것과 배치가 빠른 것은 다르다. 배치 시간을 잡아먹는 것은 SQL 한 번의 비용이 아니라 SQL로 건너가는 횟수다. 한 문장으로 바꿀 수 있으면 바꾼다. 바꿔서 잃는 것이 있다면 PL/SQL로 제대로 쓴다. 규칙은 PL/SQL에 두고, SQL로 가는 왕복만 묶고, 넘긴 것은 반드시 적어 둔다.

같은 보정(정산 100,020건)	경과	recursive calls	함수 호출	논리 읽기	보정 / 반송 / 사라짐
rev00 (루프 · 행마다 함수 · `COUNT(*)` · `WHEN OTHERS THEN NULL`)	214~224cs	301,185	100,020	816,984	99,850 / 0 / 170
`MERGE` 한 문장 + 함수 + `LOG ERRORS`	155~162cs	100,399	100,020	616,714	99,850 / 170(오류 기록) / 0
rev01 (BULK `LIMIT` · FORALL · 등급표 · 반송함)	27~28cs	2,457	2,000	22,363	99,850 / 170 / 0

🔑 오늘의 비유 — 지하 1층 문서 수발실 (행낭)

수발실	오라클
7층 정산팀 ↔ 지하 1층 수발실	PL/SQL 엔진 ↔ SQL 엔진
창구 직원이 전표 한 장을 철하는 3초	SQL 한 문장이 하는 일(3블록): "SQL은 다 빠르다"
전표 한 장마다 행낭을 하나씩 들고 엘리베이터로 오간다	루프 안의 SQL 한 번 = 엔진 왕복 한 번(recursive call)
올라오는 서류는 수발실이 100장씩 한 행낭에 담아 준다	레벨 2의 커서 FOR 루프: 자동 배열 페치(fetch 1,001)
내려보내는 전표는 정산팀이 직접 묶어야 한다	루프 안의 DML은 자동으로 묶이지 않는다 → FORALL
천 장을 한 행낭에 넣었는데 창구가 한 장마다 대장을 처음부터 뒤진다	FORALL + 원소마다 `MERGE … USING dual`: 왕복은 묶였는데 일은 그대로
전표마다 4층 인사팀에 전화해 등급을 묻는다(← 06화 카드함)	행마다 부르는 함수 · 그 안의 SELECT
명단을 한 번 받아 책상에 붙여 둔다	등급표 캐시(고객마다 한 번 · 2,000)
누가 밤사이 책상을 바꾸면서 메모를 전부 치워 버린다	재컴파일 → ORA-04068: 그 세션의 패키지 상태가 전부 사라진다
반송된 전표를 휴지통에 버리고 "다 보냈다"고 한다	`WHEN OTHERS THEN NULL`
반송함에 사유 쪽지와 함께 넣는다	반송함 테이블 · `SAVE EXCEPTIONS`
반송 쪽지의 "행낭 안 여섯 번째"	`ERROR_INDEX`: 반복 번호(빼고 넣었다면 첨자가 아니다)

앞에서 나온 비유도 다시 쓴다. 06화 4층 거래처 카드함(행마다 여는 조회), 07화 탕비실 픽업 선반의 칸(스칼라 서브쿼리 캐시가 2,000명을 다 담지 못한다), 14화 하역장의 팔레트(묶어서 나르기), 13화 정정 전 사본함(NOCOPY는 사본을 만들지 않으니 되돌릴 것도 없다)이다.

☠️ 오늘의 함정 — 한 문장이라고 집합 처리가 아니다 · 정상 종료라고 맞은 게 아니다

MERGE 한 문장에 함수를 넣었다      → 함수 호출 100,020 (루프가 문장 안으로 숨었다)
SQL 안에서 함수가 NO_DATA_FOUND    → 에러가 아니라 NULL
LOG ERRORS 를 붙였다 (오류 0 건)   → 23 → 105~117 cs · redo 11 → 56 MB
WITH 절 MATERIALIZE 를 뺐다        → 함수 1,997 → 99,850 · 에러도 경고도 없다
FORALL 로 묶었다 (원소마다 MERGE)  → 왕복 2,260 인데 논리 읽기 426,669
WHEN OTHERS THEN NULL             → 정상 종료 · 오류 기록 0 · 170건 사라짐
직전 판(rev00)과 대조했다          → 99,850 = 99,850 '통과' — 둘 다 170건을 빠뜨렸으니까

실습 준비

bash

docker start ora-hanorder

14화를 마친 상태에서 시작한다. 전제는 실습/00_준비와_점검.sql이 확인한다(T_ORD_BIG 100만 · 인덱스 3 · ORD_ST 히스토그램 · T_ORD_STLM 10만 · T_STLM_NUM 0 · PKG_ORD_STLM VALID).

PowerShell에서는 먼저 $OutputEncoding = New-Object System.Text.UTF8Encoding $false 한 줄을 실행한다.

스크립트	무엇	계정	소요
`실습/00_준비와_점검.sql`	전제 확인 · 복사본(C15) · 판들 · 수첩	sqlp	10초
`실습/01_P1_루프의_내부.sql`	★ 루프의 내부 · 레벨 1·2·3 · 왕복 한 번	sqlp	15초
`실습/02_10046_TKPROF.sql`	★ 트레이스 다섯 판(+ 컨테이너 안 TKPROF)	sqlp	20초
`실습/03_보정배치_rev00.sql`	★ rev00 · 정합성 170건	sqlp	10초
`실습/04_한문장의_함정.sql`	한 문장 판 일곱	sqlp	15초
`실습/05_보정배치_rev01.sql`	★ rev01 · LIMIT · `ERROR_INDEX` · 정합성	sqlp	15초
`실습/91_HPROF_권한_SYSDBA.sql` → `06_HPROF_계층프로파일.sql`	계층 프로파일	SYS → sqlp	10초
`실습/07_NOCOPY와_패키지상태.sql` · `07a` + `07b`	NOCOPY · UGA · 레벨 3 · ORA-04068(두 세션)	sqlp	35초
★★ `L1/05_PKG_LOAD_DRIVER.sql` · `05_부하_돌리기.sh` · `.ps1`	부하 발생기 설치 · 시운전(환경 폴더)	sqlp	3분 × 2
`실습/99_정리.sql`	C15 · HPROF 테이블 삭제 · 14화 종료 상태 확인(드라이버는 남긴다)	sqlp	수 초

모두 두 번씩 돌려도 판정은 같다. 호출 수와 fetch 수, 행 수는 그대로이고 경과 시간만 흔들린다. 원본 테이블에는 한 줄도 쓰지 않는다(C15 복사본을 쓰고, 드라이버는 *_LD 작업 테이블에 쓴다). ⚠️ 02가 남기는 트레이스 하나(보정 rev00)는 약 64MB다. 다 읽은 뒤에는 지운다(명령은 02 머리말과 99 머리말에 있다). ℹ️ 실습은 모두 Docker로 띄운 Oracle Database Free 한 대에서 끝난다. DBMS_HPROF는 Free에도 있어서 실행 권한 한 줄(91)만 주면 된다. 다만 수십 세션 규모의 동시 부하는 CPU 2개로 만들 수 없다. 그래서 드라이버는 2~4세션을 권하고 8세션까지 받는다. 대규모 EE 환경은 별권에서 다룬다.

이 화의 산출물

무엇	어디
P1 완결 판정: rev02에서 끝(rev03은 없음, 없는 이유까지)	`리비전/P1_rev02.sql`
`ADJ_rev00` · `ADJ_rev01`: 주간 정산 보정 배치(오과장 운영본 → 15화)	`리비전/`
T3 배치 SLA 리포트 2호 ★: 9번 칸(PL/SQL 호출 통계)을 처음 채운다 · 08화 체크리스트 · 13화 커밋 주기 경로	`3_실습` STEP 10 · `T3 양식`
★ `PKG_LOAD_DRIVER`: 부하 발생기(설치 · Git Bash 발사기 · PowerShell 발사기) → 환경 폴더로 승격	`00_자료준비/고객사/L1_ora-hanorder/`

이 화가 다음으로 넘기는 것

무엇	어디로
화면이 쓸 등급표(패키지 변수에 두려다 데인 것) · 결과 캐시 · `DETERMINISTIC` · 스칼라 서브쿼리 캐시의 칸	15b화
`PKG_LOAD_DRIVER` STEADY 위에서 AWR을 처음 읽는다. `T_LD_LOG`의 조각별 횟수와 `T_LD_RUN`의 시작·끝 시각을 스냅샷 구간에 맞춘다	16화
`SPIKE`: 평균에 묻히는 1분(NL의 CPU 폭발 · LOCK의 블로킹 체인) · `MODULE` / `ACTION` / `CLIENT_IDENTIFIER`로 ASH를 가른다	17화
보정 배치 rev01의 운영 소요 시간(연습 DB에서 1/8이라고 운영에서도 1/8인 것은 아니다) · 반송함 화면	16화(운영 첫 실행 뒤)
「SQL 코딩 가이드」 후보: `WHEN OTHERS THEN NULL` 금지 · 행마다 부르는 함수는 호출 수를 적는다 · FORALL에는 묶이는 문장을 · `ERROR_INDEX`는 첨자가 아니다 · T3 2호	18화

← 14화 「야간 배치가 아침을 넘긴다」 · 교재 전체 안내 · 다음 → 15b화 「같은 답을 두 번 계산하지 마라」

15화 · SQL은 다 고쳤는데 왜 느린가 ​

파일 세 개 ​

지난 화에서 들고 오는 것 ​

오늘의 전개 — 두 번 틀리고, 한 번 더 데인다 ​

오늘 배우는 한 문장 ​

🔑 오늘의 비유 — 지하 1층 문서 수발실 (행낭) ​

☠️ 오늘의 함정 — 한 문장이라고 집합 처리가 아니다 · 정상 종료라고 맞은 게 아니다 ​

실습 준비 ​

이 화의 산출물 ​

이 화가 다음으로 넘기는 것 ​