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

6월 10일 월요일. 일요일 밤 체인은 처음으로 단계마다 시각을 남겼고, 마감 두 시간 전에 끝났다. 그런데 오늘 아침의 문제는 체인이 아니었다. 오늘은 두 번 틀린다. 그리고 처음으로 다음 주에 쓸 물건을 만든다.

오전 9시 05분

월요일 아침, 매주 처음 하는 일부터 했다. 리비전 폴더를 열고 P1_rev02를 띄웠다. 맨 아래에 지난주에 붙인 메모가 있었다.

-- 전진. 측정이 정당화했다. 단, rev02 는 마감 전에 끝난다는 전제 위에서만 안전하다(배타 락).

그 위에도 한 줄이 더 있었다. 지난주 오후 2시에 선배가 "다음 주에 재"라며 남긴 숙제였다.

-- "커서 FOR 루프가 왜 느린가" — rev00 0.89초 vs rev01 0.08초의 내부는 15화.

T3 양식의 9번 칸 「PL/SQL 호출 통계」도 비어 있었다. 15화 2호 예정.

메신저에 세 사람이 차례로 떴다.

오과장  08:58
일요일 체인, 처음으로 단계마다 시각 찍었어요 (재발방지 1번).
01시 47분에 끝났어요. 04시 마감에 두 시간 넘게 남았어요. 고맙습니다.

조민우  09:06
오늘 10시 보정 배치가 걱정돼요. 지난주엔 10시 52분에 끝나서 11시 정산 확정을 8분 남기고 했어요.
다음 달부터 신규 거래처가 붙어서 정산이 두 배래요.
그리고 하나 더요 — 지난주 보정 건수가 정산 건수보다 적었어요. 오과장님 말로는 에러 로그는 깨끗하대요.

박팀장  09:15
6조각 SQL 은 다 고쳤다고 보고받았습니다. 보정 배치 SQL 도 다 빠르다고 들었고요.
그런데 왜 아직 느린 겁니까? 10시 것이 끝나기 전에 원인을 알고 싶습니다.

마지막 메시지를 두 번 읽었다. SQL은 다 고쳤는데 왜 느린가. 박팀장이 내 리포트를 꼼꼼히 읽고 있다는 뜻이었다. 그리고 나에게는 거기에 답할 말이 없다는 뜻이기도 했다.

문제의 배치는 주간 정산 보정 배치였다. 12화에서 정산 등록 화면을 10분 동안 세웠고, 13화에서 ORA-01555로 죽었던 바로 그 배치다. 13화 저녁에 오과장에게 "읽기와 쓰기를 떼어 내는 쪽으로 같이 고쳐요"라고 보냈는데, 14화에 체인 사고로 하루를 통째로 쓰는 바람에 같이 보지 못했다. 오과장은 혼자 고쳐서 지난주 월요일부터 돌리고 있었다.

오과장이 소스를 보내왔다. "13화 때 말씀대로 했어요. 정산을 직접 안 고치고 보정 이력에 써요. 두 번 돌려도 같은 값이에요. 이미 한 건은 확인하고 넘어가요."

sql

-- 주간 정산 보정 배치 (오과장 운영본)
FOR r IN (SELECT STLM_SEQ, CUS_ID, STLM_AMT FROM T_ORD_STLM) LOOP
  BEGIN
    v_rate := FN_ADJ_RATE(r.CUS_ID);                                         -- 보정률 (강도현 2019)
    SELECT COUNT(*) INTO v_cnt FROM T_STLM_ADJ WHERE STLM_SEQ = r.STLM_SEQ;  -- 이미 보정했나 (오과장)
    IF v_cnt = 0 THEN
      INSERT INTO T_STLM_ADJ (...) VALUES (r.STLM_SEQ, v_rate, ROUND(r.STLM_AMT * v_rate), SYSDATE);
    ELSE
      UPDATE T_STLM_ADJ SET ... WHERE STLM_SEQ = r.STLM_SEQ;
    END IF;
  EXCEPTION
    WHEN OTHERS THEN NULL;                                                   -- (강도현 2019)
  END;
  n := n + 1;
  IF MOD(n, 1000) = 0 THEN COMMIT; END IF;                                   -- (12화 처방)
END LOOP;

코드에는 여러 사람의 손이 겹쳐 있었다. 루프와 함수와 WHEN OTHERS는 강도현이 2019년에 쓴 본문이고, 1,000건 커밋은 내가 12화 포스트모템에 적은 처방이고, 보정 이력과 COUNT(*)는 13화 이야기를 듣고 오과장이 넣은 것이었다. 13화의 병(읽고 있는 테이블을 고치면서 커밋하는 것)은 정말로 사라져 있었다. 이제 정산은 읽기만 하고, 쓰는 곳은 보정 이력이었다.

"SQL은 전부 인덱스 타요. 트레이스 떠 보면 한 줄이 1블록이에요." 오과장 메시지의 마지막 줄이었다.

오전 9시 30분 — 한 문장은 세 블록

운영은 건드리지 않는다. 연습 DB에 정산 복사본을 뜨고, 보정 배치도 대상만 복사본으로 바꿔 심었다. 그리고 오과장 말이 맞는지부터 확인했다. 루프 안에서 정산 한 건마다 부르는 문장들을 따로 꺼내 한 번씩 돌려 봤다.

고객 등급 조회 (FN_ADJ_RATE 안)   INDEX UNIQUE SCAN PK_M_CUS_C15 → TABLE ACCESS BY INDEX ROWID   Buffers 3   Plan hash 1639701707
이미 보정했나 (COUNT(*))          INDEX UNIQUE SCAN PK_STLM_ADJ_C15                             Buffers 1

3블록. 1블록. INSERT도 나중에 트레이스로 보니 한 번에 세 블록 남짓이었다. 오과장 말 그대로였다. 문장 하나하나는 흠잡을 데가 없었다.

"SQL은 정말 다 빠른데요." 커피를 들고 온 선배에게 화면을 돌려 보였다. "02화 이후로 이렇게 깨끗한 실행계획은 처음 봐요."

"그 셋이 몇 번 돌아?"

"정산 한 건마다 한 번씩이니까… 연습 DB 정산이 10만 건이면 세 문장이 10만 번씩이요."

"그럼 30만 번이네."

답은 이미 안다고 생각했다. 책에서 읽은 문장이 있었다.

"커서 FOR 루프라서 그래요. 커서 FOR 루프는 한 건씩 가져오잖아요. 정산 10만 건을 10만 번 왕복해서 가져오는 거예요. 09화에서 ARRAYSIZE 15로 받으면 왕복이 1,827번이었던 것처럼, 루프가 한 건씩 받으면…"

"재 봐." 선배가 의자를 끌어왔다. "그 전에 지난주 숙제부터 해. P1 rev00하고 rev01. 보정 배치랑 모양은 같은데 훨씬 단순하잖아. 거기서 루프에 드는 비용을 먼저 재고, 그걸 들고 보정 배치로 가."

오전 9시 50분 — 가져오는 쪽은 이미 묶여 있었다

이번에는 경과 시간만 보지 않기로 했다. 세션 통계 V$MYSTAT에서 세 가지를 판 앞뒤로 찍었다. 다른 SQL을 부른 횟수인 recursive calls, 실행 횟수인 execute count, 그리고 CPU다. 루프의 구동 커서가 몇 번 페치했는지는 공유 풀에서 확인했다.

판 (10만 건)              경과     CPU    recursive calls   execute count   구동 커서 fetch   논리 읽기
P1 rev00 커서 FOR 루프    84 cs   44 cs      101,350          100,028            1,001         110,383
P1 rev01 INSERT…SELECT     7 cs    5 cs          183               36               —            7,403

재귀 호출은 101,350 대 183. 여기까지는 예상대로였다. 루프 안의 INSERT 한 줄이 SQL 엔진으로 10만 번 건너갔으니까.

눈길이 멈춘 건 그 옆 칸이었다. 구동 커서 fetch 1,001.

"선배님, 1,001이에요. 10만이 아니라."

"한 번에 몇 행씩 가져온 거야?"

"10만 ÷ 1,001… 100행씩이요."

"그게 PLSQL_OPTIMIZE_LEVEL이야. 기본값이 2인데, 2부터는 컴파일러가 커서 FOR 루프를 알아서 100행씩 묶어 가져오도록 바꿔." 선배가 말했다. "책에 '커서 FOR 루프는 한 건씩'이라고 써 있었지? 그 책이 나올 때의 버전에서는 맞는 말이었어. 레벨 1로 재 봐."

같은 소스를 레벨 1로 다시 컴파일했다. 루프에서 INSERT를 빼고 가져오기만 하는 판과, 명시적 커서로 FETCH … INTO를 한 건씩 하는 판도 나란히 놓았다.

판	레벨	구동 커서 fetch	recursive calls	논리 읽기	경과
커서 FOR 루프 · 가져오기만	2	1,001	1,008	1,827	4cs
커서 FOR 루프 · 가져오기만	1	100,001	100,008	100,009	16cs
명시적 커서 `FETCH INTO` · 가져오기만	2	100,001	100,009	100,009	16cs
P1 rev00 (가져오기 + INSERT)	2	1,001	101,350	110,383	84cs
P1 rev00	1	100,001	200,387	208,622	94cs
P1 rev00	3	1,001	101,386	110,430	98cs

표를 두 번 읽었다.

레벨 2의 커서 FOR 루프는 이미 100행씩 가져오고 있었다. 가져오는 데 쓴 시간은 4cs뿐이었다. 레벨 1로 내리면 한 건씩 가져오게 되고 16cs가 된다. 명시적 커서는 레벨 2에서도 한 건씩이었다. 그리고 P1 rev00 전체 84cs 가운데 가져오기는 4cs였다.

"그러니까… 루프에서 '가져오는 쪽'은 이미 묶여 있었네요. 느린 건 가져오기가 아니라…"

"보내기야. 루프 안의 INSERT가 한 건마다 SQL로 건너가. 그건 컴파일러가 못 묶어. INSERT를 100개씩 모아 보내려면 코드를 그렇게 짜야 해." 선배가 레벨 3 줄을 짚었다. "레벨 3도 똑같지? 3은 다른 걸 해 주는데, 그건 오후에 보자."

P1은 이걸로 이야기가 끝났다. rev01과 rev02에는 그 10만 번이 아예 없다. 루프도 없고, 행마다 판단할 규칙도 없다. 읽어서 번호를 붙여 넣을 뿐이다.

P1_rev02 메모 아래에 적었다. 15화: 루프 비용을 쟀다. 재귀 호출 101,350 → 183. P1에는 PL/SQL로 더 잘 쓸 것이 남지 않았다. rev02에서 완결. 리비전 번호는 올리지 않았다.

"그럼 보정 배치는요? 그것도 한 문장으로 바꾸면…"

"그 전에 왕복 한 번에 얼마가 드는지 몸으로 알아 둬. 따라와."

오전 10시 20분 — 지하 1층 수발실

선배가 엘리베이터 B1 버튼을 눌렀다. 문이 열리자 문서 수발실이 나왔다. 층마다 내려온 행낭(사내 우편 주머니)이 선반에 줄지어 있었고, 창구 뒤에서 직원 둘이 전표를 받아 대장에 철하고 있었다.

마침 7층 정산팀 막내가 행낭 하나를 들고 내려왔다. 안에 든 전표는 한 장이었다. 창구 직원이 받아서 대장에 꽂는 데 3초가 걸렸다. 막내는 빈 행낭을 들고 다시 엘리베이터를 기다렸다.

"저 친구 오늘 몇 번째예요?" 선배가 창구 직원에게 물었다.

"보정 전표요? 월요일마다 그래요. 한 장씩 들고 왔다 갔다." 직원이 웃었다. "철하는 건 금방이에요. 엘리베이터가 오래 걸리지."

선배가 나를 봤다. "SQL이 다 빠르다는 말은 맞아. 창구에서 철하는 건 3초야. 3블록이지. 문제는…"

"엘리베이터요. 한 장마다 한 번씩 왕복하니까요."

"PL/SQL 엔진하고 SQL 엔진은 다른 층에 있어. 루프 안에서 SQL 한 문장을 부를 때마다 행낭 하나 들고 내려가는 거야. 창구 일은 금방인데 왕복 비용은 매번 치러야 해." 선배가 선반의 행낭 하나를 들었다. 묵직했다. "아까 그 100행씩 가져오기가 이거야. 위로 올려 보내는 건 수발실이 알아서 100장씩 담아 보내 줘. 그런데 내려보내는 건 정산팀이 한 장마다 행낭을 하나씩 써. 그건 수발실이 대신 묶어 줄 수가 없어."

보정 배치 소스를 떠올렸다. 정산 한 건에 행낭이 세 번 오갔다. 등급 조회, COUNT(*), INSERT.

"그럼 등급 조회는요?"

"그건 더 나빠. 전표마다 4층 인사팀에 전화해서 이 고객 등급이 뭐냐고 묻는 거야. 06화 4층 거래처 카드함 기억나지? 그 카드함을 전표마다 여는 거야. 같은 고객이 50번 오면 50번 열고." 선배가 말했다. "2,000명짜리 명단을 한 번 받아서 책상에 붙여 두면 끝날 일인데."

자리로 돌아와 왕복 한 번에 드는 비용을 쟀다. 아무 일도 하지 않는 문장(SELECT … FROM dual)을 루프에서 10만 번 부르는 판과, 같은 일을 PL/SQL 대입으로 하는 판을 비교했다. 반대 방향도 쟀다. SQL이 행마다 PL/SQL 함수를 부르는 판과, 같은 식을 SQL에 바로 쓴 판이다. 모두 세 번씩 돌렸다.

PL/SQL → SQL  한 번 왕복   약 2.5 마이크로초      (10만 번이면 그것만 0.25초)
SQL → PL/SQL  한 번 왕복   약 0.53 마이크로초     (PRAGMA UDF 를 붙인 함수는 0.1~0.2)

"2.5마이크로초면 아무것도 아닌데요."

"한 번이면 그렇지. 보정 배치는 정산 한 건에 세 번이고, 그게 10만 건이야." 선배가 말했다. "게다가 그 행낭마다 창구 일이 따라붙어. 3블록이든 1블록이든 30만 번이면 얼마겠어."

10시 정각에 조민우 씨에게서 메시지가 왔다. "보정 배치 시작했어요." 운영의 10시 판은 지난주와 같은 코드로 돌기 시작했다. 오늘 우리가 할 수 있는 건 다음 주를 바꾸는 일이었다.

컨텍스트 스위칭 비용과 레벨별 페치, 측정값 전부는 2_파고들기.md §1 · §2에 정리했다.

오전 11시 00분 — 트레이스의 한 줄

연습 DB의 보정 배치는 정산 100,020건을 처리했다. 원본 10만 건에 경계 데이터 20건을 더한 복사본이었다. 왜 더했는지는 오후에 나온다. 일단 쟀다.

보정 배치 (오과장 운영본 · 이력이 빈 첫 판)
  경과 224 cs · CPU 141 cs
  recursive calls 301,185 · execute count 299,768 · 보정률 함수 호출 100,020
  논리 읽기 816,984 · redo 55.75 MB · 커밋 100
  → 정산 한 건당 SQL 로 건너간 횟수 3.01 · 논리 읽기 8.17

재귀 호출 30만. 행낭이 30만 번 오갔다. 한 건에 8블록 남짓이니 문장 하나하나는 정말 싸다. 그게 30만 번이었다.

"이제 트레이스로 봐. 운영에서 쓸 수 있는 도구는 그쪽이야. V$MYSTAT은 내 세션만 보이지만, 10046은 운영 배치 세션에도 걸 수 있으니까." 선배가 말했다. "11화에서는 Parse 줄만 봤지. 오늘은 call 표 전부하고 raw 한 줄까지 볼 거야."

10046을 켜고 같은 배치를 돌린 뒤 컨테이너 안에서 TKPROF를 돌렸다. 맨 끝에 있는 합계 두 개부터 봤다.

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS     ← SQL*Plus 가 보낸 호출
call     count       cpu    elapsed       disk      query    current        rows
Execute      2      3.60       3.67          0          0          0           1

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS         ← PL/SQL 안에서 나간 호출
Execute 299865      0.91       1.78          4       2390     312812       99852
Fetch   200892      0.19       0.18          1     501138          0      299761

"위가 User Call이야. 클라이언트가 보낸 거지. BEGIN ADJ…; END; 한 번, 트레이스를 끄는 ALTER SESSION 한 번. 아래가 Recursive Call이야. 그 한 번 안에서 PL/SQL이 SQL로 내려보낸 행낭 전부." 선배가 아래 줄을 짚었다. "Execute 30만, Fetch 20만. 문장별로 내려가 봐."

SQL                                   Execute     Fetch      rows    query
SELECT STLM_SEQ, CUS_ID, STLM_AMT …         1     1,001   100,020    1,548     ← 구동 커서 : 100행씩
SELECT CUS_GD FROM M_CUS … = :B1      100,020   100,020    99,870  299,910     ← 보정률 함수 안
SELECT COUNT(*) FROM T_STLM_ADJ …      99,870    99,870    99,870  199,676
INSERT INTO T_STLM_ADJ …               99,870         —    99,850        —     (current 312,710)

"구동 커서랑 등급 조회를 견줘 봐. 둘 다 SELECT야."

"구동 커서는 rows ÷ Fetch가 100이에요. 한 번에 100행씩이요. 등급 조회는… Execute 100,020, Fetch 100,020. 한 번 실행할 때마다 한 번 페치해서 한 행이요."

"그게 행마다 호출된 한 건 조회의 지문이야. Fetch ≈ Execute ≈ rows. 운영 TKPROF에서 이 모양이 보이면 'SQL이 느린가'가 아니라 '누가 이걸 행마다 부르나'를 찾아." 선배가 말했다. "배열 크기도 같은 식으로 읽어. rows ÷ Fetch. 아까 그 1,001이 TKPROF에서도 그대로 나오지."

그리고 raw 파일을 열었다. 64MB에 800,876줄이었다. 선배가 그중 한 줄을 골랐다.

FETCH #135811338131992:c=0,e=9,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=1,plh=1639701707,tim=2866995418

"하나씩 읽어."

"FETCH는 페치 호출이고… c는 CPU, e는 경과 시간이에요. 단위는 마이크로초고요. 9마이크로초 걸렸어요. p=0은 디스크에서 읽은 블록 0, cr=3은 일관 읽기 3블록, 아까 그 3블록이에요. cu=0은 현재 모드 읽기 0. mis=0은 공유 풀에서 못 찾은 횟수 0이니까 하드 파싱이 아니고요. r=1은 한 행."

"dep는?"

"…깊이요? 1."

"재귀 깊이. 0이면 클라이언트가 보낸 거, 1이면 PL/SQL이 부른 거. 이 파일엔 2도 있어." 선배가 위쪽을 가리켰다. 첫 INSERT를 파싱하던 순간에 obj$를 읽는 dep=2 줄이 있었다. 한참 뒤에는 update seg$도 dep=2로 찍혀 있었다. "하드 파싱하느라 딕셔너리를 읽은 것, 테이블이 커져서 공간을 잡은 것. 그것도 재귀 호출이야. 네가 부르지 않았는데 오라클이 부른 SQL이지."

"og=1은 옵티마이저 목표로 ALL_ROWS고, plh는 실행계획 해시예요. 아까 DISPLAY_CURSOR의 1639701707이랑 같네요. tim은 시각이고요."

"그 한 줄이 30만 번 들어 있는 파일이야. TKPROF는 그걸 문장별로 더한 거고." 선배가 말했다. "TKPROF가 이상하면 raw를 열어. 오늘 안에 한 번은 그럴 일이 있을 거야."

raw 10046의 필드 전부와 User Call, Recursive Call이 생기는 자리, TKPROF 읽는 법은 2_파고들기.md §3 · §4에 정리했다.

오전 11시 40분 — 한 문장으로 쓰면

"그럼 답은 나왔네요." 자신 있었다. "14화에서 P1을 한 문장으로 바꿨잖아요. 보정도 MERGE 한 문장으로 쓰면 돼요. 함수는 그대로 문장 안에 넣고요. 그러면 행낭이 하나가 되잖아요."

"써 봐."

sql

MERGE INTO T_STLM_ADJ a
USING (SELECT STLM_SEQ, STLM_AMT, FN_ADJ_RATE(CUS_ID) AS rate FROM T_ORD_STLM) x
   ON (a.STLM_SEQ = x.STLM_SEQ)
 WHEN MATCHED THEN UPDATE SET ...
 WHEN NOT MATCHED THEN INSERT ... ;

돌렸다. 0.04초 만에 에러가 났다.

ORA-01400: cannot insert NULL into ("SQLP"."T_STLM_ADJ_C15"."ADJ_RATE")

보정 이력은 0건이었다. 한 문장이라 한 행이 실패하자 10만 건이 다 되돌아갔다. 보정률이 NULL인 행이 있었다는 건 함수가 NULL을 돌려줬다는 뜻이었다.

"함수는 고객이 없으면 NO_DATA_FOUND를 던지는데요. NULL을 줄 리가…"

같은 고객 번호로 PL/SQL에서 한 번, SQL에서 한 번, 나눠서 불러 봤다.

PL/SQL 에서 부르면 : raised ORA-01403: no data found
SQL 에서 부르면    : (NULL - 에러 없음)

"SQL 안에서 부른 함수가 NO_DATA_FOUND를 밖으로 던지면, SQL은 그걸 '그 행엔 값이 없다'로 읽어." 선배가 말했다. "에러가 아니라 NULL이 되는 거야. 이번엔 보정률 칸이 NOT NULL이라 터져 줬지. NULL을 받는 칸이었으면 보정률이 NULL인 이력이 조용히 쌓였을 거야."

"…고객이 없는 정산이 있다는 거네요."

"그건 오후 몫이야. 일단 끝까지 돌게 해 봐."

실패한 행만 따로 빼 두도록 LOG ERRORS를 붙였다. 이번에는 끝까지 돌았다.

판                                   경과     함수 호출   recursive calls   논리 읽기    redo
rev00 (루프)                        224 cs     100,020        301,185        816,984   55.75 MB
MERGE 한 문장 + LOG ERRORS          162 cs     100,020        100,399        616,714   55.90 MB

함수 호출 100,020. 그대로였다.

"행낭이 하나가 됐는데요… 함수는 10만 번이에요."

"이번엔 SQL이 위층으로 행낭을 보내. 행마다 PL/SQL 함수를 부르러 가는 거지." 선배가 말했다. "그리고 그 함수 안에 SQL이 또 있잖아. 내려갔다 올라갔다 다시 내려가는 거야. 루프를 지운 게 아니라 루프가 문장 안으로 숨은 거야. 한 문장이라고 다 집합 처리는 아니야."

07화의 스칼라 서브쿼리 캐시가 떠올랐다. 06화 Q2에서 지역명 스칼라 서브쿼리가 Starts 8로 끝났던 장면이다.

"함수를 스칼라 서브쿼리로 감싸면 되잖아요. (SELECT FN_ADJ_RATE(CUS_ID) FROM dual). 그러면 같은 고객은 캐시에서…"

스칼라 서브쿼리로 감싼 MERGE + LOG ERRORS   147 cs · 함수 호출 57,679

57,679. 줄긴 줄었다. 그런데 고객은 2,000명이었다.

"07화에서 캐시에 '칸'이 있다고 했지. 그 Q2의 지역은 8개였어. 오늘은 2,000명인 데다, 정산이 고객 번호 순서대로 오지 않고 돌아가면서 와." 선배가 말했다. "칸이 모자라면 칸에 못 들어간 고객은 매번 다시 불러. 2,000명을 다 담을 칸이 없었던 거지."

⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 문장 하나하나는 3블록이었고, 배치는 그 문장들을 30만 번 불렀다. 커서 FOR 루프는 이미 100행씩 가져오고 있었고(레벨 2), 시간은 루프 안에서 SQL로 보내는 쪽에서 들었다. 그걸 한 문장으로 바꿨더니 함수 호출이 100,020 그대로였다. 루프가 문장 안으로 숨은 것이다. 이어서 읽으면 에러 로그가 깨끗했던 이유를 찾고, 행 단위 규칙을 PL/SQL로 제대로 쓴 뒤, 그 코드가 정말 한 문장보다 나은지 잰다.

오후 1시 00분 — 170건

점심을 먹고 오니 조민우 씨에게서 메시지가 와 있었다. "보정 끝났어요. 10시 49분. 오늘도 건수가 안 맞아요."

오전에 미뤄 둔 질문으로 돌아갔다. 고객이 없는 정산은 왜 있을까. 조민우 씨에게 물었더니 바로 답이 왔다.

"아, 5월 말에 개인정보 파기 정책 때문에 탈퇴 고객을 고객 마스터에서 지웠어요. 정산은 남아 있고요. 그리고 정산 등록 화면에서 금액 없이 먼저 저장하는 건이 있어요. 금액은 다음 날 확정해요."

연습 DB 복사본에는 그 두 가지를 그대로 만들어 두었다. 08화 체크리스트의 E 항목대로, 경계 데이터는 복사본에 만든다. 탈퇴 고객 3명(그 고객들의 정산 150건)과 금액 미확정 정산 20건이다. 그래서 정산이 100,020건이었다.

선배가 08화 체크리스트를 열었다. "A부터. 기준이 뭐야?"

"지난주 보정 결과요?"

"그건 직전 판이지. 직전 판이 틀렸으면? 08화에서 Q3 rev00은 처음부터 50배였어." 선배가 말했다. "기준은 원장, 그러니까 이번 주 정산 전체야. 보정은 정산 한 건마다 '보정했다'든 '못 했다, 이유는 이것'이든 뭔가가 남아야 해."

원장 (이번 주 정산)              100,020
보정 이력                         99,850
반송 기록                              0
오류 기록                              0
─────────────────────────────────────────
원장에만 있는 정산 (어디에도 없음)    170
   고객이 마스터에 없음   150 (3명)
   금액 미확정             20

170건이 사라졌다. 보정도 안 됐고, 어디에도 적히지 않았다. 그런데도 배치는 '정상 종료'했다.

"WHEN OTHERS THEN NULL." 소스의 그 줄을 짚었다. "고객이 없으면 함수가 NO_DATA_FOUND를, 금액이 없으면 INSERT가 ORA-01400을 내요. 둘 다 여기서 삼켰어요. 그래서 에러 로그가 깨끗했던 거고요."

규정대로라면 탈퇴 고객 150건에 적혔어야 할 보정액은 114,864원이었다. 게다가 정산 확정 화면은 정산과 보정 이력을 바깥 조인으로 붙여 보여 주기 때문에, 그 170건은 화면에 '보정 0원'으로 떠 있었다. 정산팀은 보정이 0원인 정산이라고 믿고 확정 버튼을 눌렀을 것이다. 14화에서 선배가 한 말 그대로였다. 반쯤 찬 화면은 사람이 맞는 숫자라고 믿고 써 버린다.

오과장에게 그 줄이 언제 왜 들어갔는지 물었다. 답으로 커밋 로그가 왔다.

2019-06-24  hd.kang
  보정 한 건의 오류로 월요일 정산 전체가 멈춤 (6/24 10:20). 건별 예외 처리로 변경
  — 한 건 때문에 전체를 세우지 않는다.

"그때는 옳았어." 선배가 말했다. "한 건 때문에 월요일 정산을 통째로 세우면 안 돼. 그건 지금도 맞아. 2019년에는 넘길 건이 거의 없었을 거야. 고객 파기도, 금액 없는 등록도 없었으니까." 선배가 NULL을 손가락으로 가렸다. "빠진 건 하나야. 넘긴 걸 어디에 적느냐. 수발실로 치면, 창구에서 반송된 전표를 휴지통에 버리고 '다 보냈습니다'라고 보고한 거야."

그 위의 줄도 다시 봤다. 2019-06-17, 같은 사람이었다.

2019-06-17  hd.kang
  보정률은 정산팀 규정. 분기마다 바뀜 — 함수 하나에 모은다. 화면(예상 보정액)도 이 함수를 쓴다.

함수에도 그때 나름의 근거가 있었다. 규칙은 한 곳에 둔다는 것이다. 그 근거는 오후에 한 번 더 나온다.

오후 1시 40분 — 어느 부름 아래에서

"고치기 전에 시간이 어디서 드는지부터 나눠 봐. TKPROF는 문장별로 셌지. 이번엔 부름별로 세." 선배가 DBMS_HPROF를 꺼냈다. "계층 프로파일러야. 누가 누구를 불렀는지까지 세 줘."

연습 DB의 sqlp에는 처음에 그 패키지를 부를 권한이 없었다. 동의어는 보이는데 본체가 보이지 않았다. SYS로 실행 권한을 하나 주고 나서야 돌았다. Free에도 프로파일러는 있었다. 결과는 파일이 아니라 테이블에 쌓였다.

보정 배치 (프로파일러를 켠 채 · 판 전체 2.39 초)
부름                                       횟수       그 아래 전부
INSERT INTO T_STLM_ADJ (11번 줄)          99,870       1.50 초
FN_ADJ_RATE                              100,020       0.41 초
  └ SELECT CUS_GD … (함수 4번 줄)         100,020       0.36 초
SELECT COUNT(*) … (9번 줄)                99,870       0.37 초
구동 커서 페치 (6번 줄)                     1,001       0.02 초
COMMIT (22번 줄)                              100       0.004 초

"네 가지 모양이 시간표에 그대로 나오네요." 나는 표 옆에 적었다. "INSERT는 한 건씩 보내서, 함수는 행마다 불러서, COUNT(*)는 행마다 물어서 시간이 들어요. 루프의 가져오기는 0.02초뿐이고요."

"그럼 하나씩 바꿔."

① 커서 FOR 루프          → BULK COLLECT … LIMIT 1000           (천 건씩 받는다)
② 행마다 함수            → 고객마다 한 번 — 책상 위 등급표       (함수는 그대로 둔다)
③ COUNT(*) 로 묻는 분기  → 구동 커서에서 보정 이력을 바깥 조인   (행마다 묻지 않는다)
④ WHEN OTHERS THEN NULL  → 반송함 — 넘긴 것을 사유와 함께 적는다

오후 2시 20분 — FORALL인데 왜

첫 판은 금방 썼다. 천 건씩 받아서, 등급은 책상 위 등급표에서 찾고, 천 건을 FORALL로 한 번에 보낸다. 오전에 쓴 MERGE를 그대로 FORALL 안에 넣었으니, 원소마다 MERGE … USING dual이 도는 셈이었다.

FORALL MERGE 판    130 cs · recursive calls 2,260 · 함수 호출 2,000 · 논리 읽기 426,669 · redo 53.89 MB

130cs. 같은 조건에서 루프 판이 219였으니 219에서 130이 됐다. 재귀 호출은 30만에서 2,260으로 줄었는데, 시간은 반 가까이밖에 줄지 않았다.

"행낭이 2,000개 남짓으로 줄었는데 왜 이것밖에…"

"행낭은 묶었지. 창구 일은?" 선배가 논리 읽기와 redo 칸을 가리켰다. 42만 블록에 54MB. 루프 판(82만 · 56MB)과 별로 다르지 않았다. "원소마다 MERGE면 행마다 MERGE야. 천 장을 한 행낭에 넣어 보냈는데, 창구 직원이 한 장 받을 때마다 대장을 처음부터 뒤져서 '이거 있나?'를 확인하고 철하는 거지. FORALL은 왕복을 묶을 뿐 일을 묶지는 않아. 일을 묶으려면 문장 자체가 묶일 수 있는 모양이어야 해."

오전의 ③을 다시 봤다. '이미 보정했나'를 행마다 물을 필요가 없었다. 구동 커서가 정산을 읽을 때 보정 이력을 바깥 조인으로 붙이면, 읽는 순간 INSERT할 정산과 UPDATE할 정산이 갈린다. 그러면 MERGE 대신 INSERT는 INSERT대로, UPDATE는 UPDATE대로 천 건씩 보내면 된다.

문제는 배열에 구멍이 생긴다는 것이었다. 천 건 중에서 반송할 것(고객 없음, 금액 없음)을 빼고 INSERT할 것과 UPDATE할 것을 나누면, 두 배열이 모두 듬성듬성해진다. 1번, 2번, 4번, 6번…

"그래서 INDICES OF가 있는 거야. 구멍 난 배열을 구멍째로 넘기면 있는 첨자만 보내." 선배가 말했다. "그리고 오늘 아침처럼 그 천 건 중에 한 건이 실패했다고 천 건이 다 되돌아가면 안 되지. SAVE EXCEPTIONS를 붙이면 실패한 것만 모아 두고 나머지는 넣어."

sql

FORALL i IN INDICES OF ins_rate SAVE EXCEPTIONS
  INSERT INTO T_STLM_ADJ (...) VALUES (v_seq(i), ins_rate(i), ROUND(v_amt(i) * ins_rate(i)), SYSDATE);

돌렸다.

판 (이력이 빈 첫 판)    경과     CPU    recursive calls   함수 호출   논리 읽기    redo       undo
rev00 (오과장 운영본)  219 cs   134 cs     301,187         100,020    816,425   55.77 MB   16.71 MB
FORALL MERGE 판        130 cs    56 cs       2,260           2,000    426,669   53.89 MB   17.07 MB
rev01                   27 cs    15 cs       2,457           2,000     22,363   11.24 MB    3.69 MB

논리 읽기 816,425 → 22,363. 재귀 호출 301,187 → 2,457. 경과 시간 219cs → 27cs. 처음부터 한 번 더 돌려도 214 → 28이었다.

"함수 2,000번은 고객 1,997명하고 탈퇴 고객 3명이에요. 등급표를 만들 때 한 번씩이요."

반송함을 열었다. 그런데 금액 미확정 20건의 사유가 이상했다.

ORA-01400: cannot insert NULL into ()

컬럼 이름이 없었다. SAVE EXCEPTIONS가 돌려주는 건 오류 번호뿐이었다. 그 번호로 메시지를 다시 만들면 괄호 안이 빈다.

"그물에 걸린 건 다 이 모양이 돼. 무엇이 실패했는지는 알아도 왜 실패했는지는 몰라." 선배가 말했다. "아는 실패는 이름으로 먼저 걸러. 금액이 없는 건 네가 이미 알잖아. 보내기 전에 '금액 미확정'이라고 반송함에 적어. 그물은 아직 모르는 실패에 대비해 남겨 두고."

그렇게 고쳤다. 그리고 그물을 작은 배열로 따로 시험하다가, 하마터면 놓칠 뻔한 것을 하나 발견했다. 첨자 1~10 중에서 3과 5를 지우고 8번에 실패를 심었다.

ERROR_INDEX = 6   → seq(6) 을 반송하면 엉뚱한 정산
실제로 실패한 첨자 = 8

"ERROR_INDEX는 '몇 번째로 보낸 것'이야. 구멍 난 배열에서는 첨자가 아니야." 선배가 말했다. "반송 쪽지에 '행낭 안 여섯 번째'라고만 적혀 있는 거지. 너는 3번과 5번을 빼고 넣었으니 원래 번호로는 8번이고. 그 번호를 그대로 쓰면 멀쩡한 정산을 반송함에 적고, 실패한 정산은 또 사라져."

다음은 정합성이었다. 이번에는 A 칸부터 원장을 기준으로 삼았다.

원장 100,020 = 보정 99,850 + 반송 170       겹침 0 · 빠진 것 0 · 번호 중복 0
반송 사유   NO_CUS (고객 없음) 150 · NO_AMT (금액 미확정) 20 · 그물에 걸린 것 0
보정액      73,638,572 = 규정대로 직접 계산한 값 73,638,572 · MINUS 0

선배가 하나를 더 시켰다. "rev01 결과를 rev00 결과하고 대조해 봐."

rev01 99,850 = rev00 99,850 · MINUS 0 / 0

"…통과예요."

"통과지. 둘 다 같은 170건을 빼먹었으니까." 선배가 말했다. "rev00이 기준이었으면 오늘 찾은 건 하나도 안 보였을 거야. 08화 A 칸이 왜 '직전 판 그리고 원장'인지 이제 알겠지."

BULK COLLECT LIMIT, FORALL, INDICES OF, SAVE EXCEPTIONS, 그리고 ERROR_INDEX의 함정은 2_파고들기.md §5에 정리했다.

오후 3시 30분 — 한 문장이면 안 되나

리포트를 쓰려는데 선배가 의자를 돌렸다.

"질문 하나. 13화 「커밋 주기 기준」 1번 줄이 뭐였지?"

"집합 연산 한 문장으로 쓸 수 있나? 쓸 수 있으면 그렇게 쓴다."

"이건 한 문장으로 못 써?" 선배가 말했다. "보정률 규칙이래야 등급 셋에 CASE 하나잖아. 고객 마스터를 조인해서 CASE로 쓰면?"

써 봤다. 반송할 것을 먼저 따로 적고, 나머지는 MERGE 한 문장으로 처리했다.

규칙을 SQL 로 옮긴 판 (반송 INSERT + MERGE)   23 cs · 논리 읽기 16,609 · redo 11.15 MB     (두 번째 판 32 cs)

23cs. rev01의 27보다 빨랐다. 두 번째 판은 32cs로 흔들렸지만, 논리 읽기 16,609는 rev01의 22,363보다 적었다. 역시 한 문장은 한 문장이었다.

"…그럼 PL/SQL 판은 왜 썼죠."

선배는 답하지 않고 조민우 씨에게 전화를 걸어 스피커로 돌렸다. "보정률 규칙, 그거 누가 정해요?"

"저희 팀이요. 분기마다 바뀌어요. 다음 분기에 신규 거래처 등급이 하나 더 생겨요. 그리고 정산 등록 화면에서 예상 보정액을 보여 줄 때도 그 함수를 써요."

전화를 끊고 선배가 말했다. "강도현 씨가 2019년에 함수로 모은 이유가 그거야. 규칙을 이 문장에 CASE로 박으면 규칙이 두 군데가 돼. 다음 분기에 함수만 고치고 이 문장을 잊으면 보정 배치와 화면이 서로 다른 보정액을 내. 08화의 정합성 사고가 이번엔 규칙 쪽에서 나는 거지."

"그럼 함수를 그대로 쓰면서 한 문장으로는요? WITH 절에서 고객별로 먼저 계산하고 조인하면 함수를 고객마다 한 번만 부르잖아요."

WITH 절 + /*+ MATERIALIZE */   34 cs · 함수 호출 1,997 · 논리 읽기 22,711     (두 번째 판 25 cs)
같은 문장에서 힌트만 뺌         74 cs · 함수 호출 99,850 · 논리 읽기 315,561  (두 번째 판 67 cs)

힌트가 있으면 rev01과 같은 값이 나왔다. 힌트를 빼자 함수 호출이 99,850번으로 돌아왔다. 실행계획을 보니 힌트가 있는 쪽에는 TEMP TABLE TRANSFORMATION이 있었고, 없는 쪽에는 HASH JOIN 하나뿐이었다. 옵티마이저가 WITH 절을 풀어 본문에 합치면서, 함수 호출을 조인이 끝난 뒤 행마다 부르는 자리로 옮긴 것이다. 에러도 경고도 없었다.

"06화 힌트 대장 기억하지? 힌트 하나에 성능을 다 걸면, 버전을 올리다 그 힌트가 무시되는 날 아무도 몰라." 선배가 말했다. "그리고 한 문장은 원자적이야. 아침처럼 모르는 실패가 한 건만 나도 10만 건이 다 되돌아가. 그걸 막으려면?"

"LOG ERRORS요."

"그건 재 봤어?"

규칙을 SQL로 옮긴 23cs짜리 문장에 LOG ERRORS만 붙였다. 오류가 한 건도 나지 않는 문장이었다.

같은 MERGE + LOG ERRORS (오류 0 건)   117 cs · 논리 읽기 316,251 · redo 55.84 MB    (두 번째 판 105 cs)

23 → 117. 오류 기록 절을 붙인 것만으로 redo가 11MB에서 56MB로 늘었다. 행 단위로 처리하던 루프 판의 redo와 거의 같았다. 오류를 행마다 기록할 수 있게 하려고, 문장이 묶어서 하던 일을 한 행씩 처리하게 된 것이다.

네 판을 한 표에 모았다.

판	경과	함수 호출	규칙의 자리	모르는 실패가 나면
규칙을 SQL로(CASE)	23~32cs	0	두 곳(함수 · 이 문장)	10만 건 되돌림
〃 + `LOG ERRORS`	105~117cs	0	두 곳	실패 행만 기록
함수 · 고객마다 (`MATERIALIZE`)	25~34cs	1,997	한 곳	10만 건 되돌림 · 힌트가 빠지면 99,850번
rev01 (PL/SQL)	27~28cs	2,000	한 곳	실패 행만 반송함 · 사유와 함께

"한 문장으로 못 바꾸는 게 아니었네요. 바꾸면 잃는 게 있었던 거예요."

"그게 '한 문장으로 못 바꾸는 행 단위 규칙'의 정체야. 대부분은 바꿀 수 있어. 그런데 바꿨을 때 규칙이 두 곳이 되거나, 힌트 하나에 기대게 되거나, 실패를 행마다 다룰 수 없게 되면 그때 PL/SQL로 써. 대신 제대로 써. 규칙은 PL/SQL에 두고, SQL로 가는 행낭만 묶어." 선배가 표의 맨 아랫줄을 짚었다. "그리고 그게 한 문장이랑 같은 값이 나오는지 재 봐. 오늘은 같았어."

LIMIT도 바꿔 가며 쟀다. 10이면 재귀 호출이 32,249로 다시 늘었다. 100이면 5,220, 1,000이면 2,455, 10,000이면 2,111이었다. 1,000을 넘기면 왕복은 거의 줄지 않고, 10만으로 잡으면 세션 메모리만 34MB를 차지했다. 천 건으로 정했다.

⛳ 오늘은 여기까지(중단 지점 2) 보정 배치 rev01이 나왔다. 재귀 호출 301,187 → 2,457, 논리 읽기 816,425 → 22,363, 함수 호출 100,020 → 2,000이고, 원장 100,020 = 보정 99,850 + 반송 170이다(빠진 것 0). 한 문장 판들과 견주어 왜 PL/SQL로 썼는지도 숫자로 남겼다. 여기까지만 해도 오늘 할 일은 다 했다. 이어지는 늦은 오후에는 등급표를 '패키지'에 두려다 데이고, 오늘 얻은 교훈으로 다음 주에 쓸 부하 발생기를 만든다.

오후 4시 30분 — 책상 위의 등급표

"등급표 말인데요." 나는 rev01의 지역 변수 한 줄을 가리켰다. "이걸 패키지 변수로 빼면 정산 등록 화면도 같이 쓸 수 있잖아요. 세션이 살아 있는 동안 계속 남으니까, 화면에서 고객 하나 볼 때마다 등급표를 다시 만들 필요도 없고요."

"재 봐. 얼마나 남고, 언제 사라지는지."

패키지 변수에 고객 1,997명의 등급표를 채웠다.

첫 번째로 채울 때   함수 호출 1,997 · session uga memory +261,920 ~ +327,400 바이트 (64 KB 덩어리 4 ~ 5개)
두 번째 돌 때       함수 호출 0
다음 호출(새 EXEC)  등급표 1,997명 그대로

"세션이 끝날 때까지 남아. UGA, 그러니까 그 세션 몫의 메모리야. 정산 등록 화면 세션이 200개면 등급표도 200벌이고." 선배가 말했다. "그건 감당할 수 있어. 다른 걸 봐. 세션 하나가 등급표를 쥐고 있는 동안 옆에서 누가 그 패키지를 다시 컴파일하면 어떻게 될까."

두 세션을 띄웠다. A가 등급표를 채우고 6초 쉰다. 그 사이 B가 소스는 그대로 두고 패키지 본문만 다시 컴파일한다. 그리고 A가 다시 부른다.

A2  ORA-04068: existing state of packages has been discarded
    ORA-04061: existing state of package body "SQLP.PKG_RATE_C15" has been invalidated

그런데 그다음 줄들이 화면에 나오지 않았다. SERVEROUTPUT을 다시 켜고 나서야 보였다.

"ORA-04068은 그 패키지 하나가 아니라 그 세션의 패키지 상태 전부를 버려. DBMS_OUTPUT 설정까지." 선배가 말했다. "수발실로 치면, 누가 밤사이 책상 배치를 바꾸면서 책상 위 메모를 다 치운 거야. 네 메모만이 아니라 전부."

같은 실험에 상태가 없는 패키지도 하나 끼워 두었다. 그 패키지는 다시 컴파일된 뒤에도 문제없이 호출됐다. 그런데 그 패키지도 처음에는 ORA-04068을 맞았다. 스펙에 기본값 문자열 상수 하나를 두었기 때문이다. 상수 하나만으로도 '상태 있는 패키지'가 된 것이다. 그 상수를 함수로 바꾸고 나서야 상태가 없어졌다.

"그래서 rev01의 등급표는 지역 변수로 둬. 배치가 끝나면 사라지게." 선배가 말했다. "화면에 줄 거라면 결과 캐시라는 게 따로 있어. 그건 다음 주 15b에서 보자."

하나를 더 쟀다. 원소 10만 개짜리 배열을 프로시저에 IN OUT으로 1,000번 넘기고, 마지막에는 프로시저 안에서 일부러 예외를 던진 뒤 부른 쪽에서 배열을 들여다봤다.

IN OUT          1,000번 196 ~ 202 cs   예외 뒤 x(1) = ORIG       ← 들어갈 때 복사, 나올 때 복사 · 고친 게 되돌아갔다
IN OUT NOCOPY   1,000번   0 cs         예외 뒤 x(1) = CHANGED    ← 복사가 없다 · 반쯤 고친 배열이 남았다

"13화의 '반쯤 반영'이네요. 이번엔 배열 안에서요."

"복사를 안 하니까 되돌릴 원본도 없지. NOCOPY는 속도를 얻는 대신 그 안전을 내줘." 선배가 말했다. 오전에 미뤄 둔 레벨 3도 이때 쟀다. 지역 함수를 300만 번 부르면 레벨 1과 2는 0.16초, 3은 0.10초였다. 레벨 3은 같은 프로그램 안의 작은 함수를 부르는 자리에 함수 본문을 풀어 넣어, 부르는 일 자체를 없앤다. "기본값을 3으로 올리자는 얘긴 아니야. 이 차이가 의미 있는 코드는 드물어. 재 본 결과를 알고 있으면 돼."

패키지 상태와 UGA, ORA-04068, NOCOPY의 대가, 레벨별 최적화는 2_파고들기.md §6 · §7에 정리했다.

오후 5시 10분 — 다음 주에 쓸 물건

리포트를 쓰려는데 선배가 다른 이야기를 꺼냈다.

"다음 주부터는 SQL 하나가 아니라 시스템 전체를 봐. AWR로." 선배가 연습 DB의 세션 목록을 띄웠다. 우리 둘뿐이었다. "그런데 이 DB는 조용해. 부하가 없는 AWR은 빈 종이야. 운영처럼 여러 사람이 여러 조각을 동시에 돌리는 상태를 만들어야 해."

"부하 발생기요."

"오늘 배운 걸로 만들어. 그래서 오늘 만드는 거야." 선배가 손가락을 꼽았다. "6조각의 현재판을 섞어 돌린다. 바인드 변수로(11화). 결과는 배열로 받는다(오늘). 에러는 삼키지 않고 센다(오늘). 패키지에 상태를 두지 않는다(방금). 원본 테이블에는 한 줄도 안 쓴다(13화부터). 그리고 다음 주 AWR 숫자와 맞춰 볼 수 있게 조각마다 몇 번 돌았는지 남긴다."

먼저 막힌 건 세션을 띄우는 방법이었다. 스케줄러 잡으로 세션 여러 개를 동시에 돌리려고 했는데, 이 DB는 job_queue_processes가 1이었다. 잡을 돌릴 일꾼이 하나뿐이라는 뜻이다. 그래서 12화처럼 셸에서 sqlplus 세션을 여러 개 띄우고, 세션마다 같은 프로시저를 번호만 달리해서 부르게 했다.

설계는 이렇게 됐다.

PKG_LOAD_DRIVER
  START_RUN   런을 연다 (프로필 · 분 · 세션 수 · 가중치 · 씨앗 · SPIKE 구간)  — 작업 테이블을 비운다
  RUN_SESSION 세션 하나 — 조각을 가중치대로 고르고, 돌리기 직전에 MODULE='LOAD_DRIVER' · ACTION='Q2 rev01'
  STOP_RUN    제어 테이블에 STOP — 모든 세션이 1초 안에 스스로 끝난다
  END_RUN     끝 시각을 적고 작업 테이블을 비운다
  기록        T_LD_RUN (시작 · 끝 시각) · T_LD_SESS (세션별) · T_LD_LOG (세션 × 분 × 조각 × 판)

조각을 고르는 순서는 세션 번호를 씨앗으로 고정한 난수로 정했다. 인자가 같으면 같은 순서로 같은 바인드 값을 고른다. 쓰기를 하는 조각(P1과 N1)은 원본이 아니라 드라이버 전용 작업 테이블에 쓰게 했다. P1은 14화의 APPEND 판이라 테이블을 통째로 잠그므로, 세션끼리 노란 줄에 걸리지 않도록 세션마다 다른 작업 테이블을 주었다.

"그리고 다음다음 주에 쓸 스위치 하나." 선배가 말했다. "평균에 묻히는 몇 분짜리 폭발을 만들 수 있어야 해. 정해진 분부터 정해진 길이만큼 옛날의 나쁜 판을 섞는 거야."

그래서 SPIKE 프로필을 만들었다. 그 구간에서만 Q2가 06화의 USE_NL 힌트판으로, Q4가 11화 개발팀의 리터럴판으로, N1이 12화의 채번 테이블 FOR UPDATE 판으로 바뀐다. 그리고 세션 1이 12화의 보정 배치처럼 번호 행을 쥔 채 10초씩 버틴다.

2세션 × 3분씩 두 번 돌렸다.

STEADY  세션 2 · 3분 · 생각 시간 200ms      실행 873 + 878 · 에러 0
  Q1 rev03 521 · Q2 rev01 82 · Q3 rev01 188 · Q4 rev03 710 · N1 rev01 227 · P1 rev02 23

SPIKE   세션 2 · 3분 · 1분째부터 1분 (NL · LIT · LOCK)   실행 587 + 656 · 에러 0
  분       Q1    Q2   Q2 rev00   Q3    Q4   Q4 lit   N1   N1 hotfix   N1 hold   P1
  0       168    19       —      69   243      —     79        —         —      8
  1        28     —       7       3     —     21      —        7         6      3     ← SPIKE
  2       181    26       —      61   233      —     74        —         —      7

SPIKE의 1분째에 실행된 조각 수가 확 줄었다. 세션 1이 번호 행을 쥐고 10초씩 버티는 동안, 세션 2는 N1을 고를 때마다 그 줄에 섰다. 핫픽스판 N1 일곱 번이 평균 6.2초씩 기다렸다. 같은 구간의 ASH에는 ACTION = 'N1 hotfix'로 enq: TX - row lock contention이 43샘플 찍혀 있었다. 12화의 그 장면이 연습 DB에서, 이번에는 정해진 분에 정해진 길이만큼 재연됐다.

그리고 SPIKE 런의 첫 1분(168 · 19 · 69 · 243 · 79 · 8)은 STEADY 런의 첫 1분과 한 개도 다르지 않았다. 같은 씨앗에 같은 순서였다.

원본 지문은 돌리기 전과 뒤가 같았다. T_ORD_BIG 1,000,000건 · 255,398,623,700, T_ORD_STLM 100,000건, T_STLM_NUM 0. 발사기가 끝난 뒤 남은 드라이버 세션은 0개였다.

"이건 다음 주에 쓰려고만 만든 게 아니야. 오늘 네가 배운 걸 지킨 PL/SQL이 어떻게 생겼는지 보여 주는 물건이야." 선배가 말했다. "환경 폴더에 올려. 16화와 17화가 거기서 가져다 쓰게."

오후 6시 20분 — T3 2호

T3 양식을 다시 열었다. 1호는 일요일 밤 체인이었고, 2호는 주간 보정 배치다. 지난주에 비워 둔 9번 칸 「PL/SQL 호출 통계」를 처음으로 채웠다.

9. PL/SQL 호출 통계 (연습 DB · 10046 + TKPROF · 정산 100,020건)
                                     rev00                          rev01
   User Call (NON-RECURSIVE)        Execute 1                      Execute 1
   Recursive Call  Execute          299,865                        290 (+ 함수 안 2,000 · TKPROF 표 밖)
                   Fetch            200,892                        103
   행마다 불린 한 건 조회             등급 조회 Exec = Fetch = 100,020  없음 (고객마다 2,000)
   구동 커서 배열 크기 (rows/Fetch)   100 (PLSQL_OPTIMIZE_LEVEL 2)   990 (LIMIT 1,000)

1번 칸에는 운영 숫자를 적었다. 오과장이 남기기 시작한 로그 덕분에 처음으로 쓸 수 있었다. 트리거 월 10:00 · 마감 11:00(정산 확정) · 6/3 끝 10:52 · 6/10 끝 10:49 · 여유 8~11분. 8번 「추세」 칸도 처음으로 채웠다. 다음 달 정산이 두 배가 되면 지금 판으로는 여유가 음수가 된다. 연습 DB에서 1/8로 줄어든 시간이 운영에서 몇 분이 될지는 쓰지 않았다. 그건 다음 주 월요일, rev01이 운영에서 처음 돈 뒤의 로그를 봐야 알 수 있다.

10번 정합성 칸에는 오늘 찾은 170건을 그대로 적었다. A 기준: 원장(직전 판 rev00과 대조하면 통과해 버린다). B: 100,020 = 99,850 + 170. E: 탈퇴 고객 150 · 금액 미확정 20. H: 정산 확정 화면이 반송함을 함께 보여 줘야 한다. 11번 커밋 주기 칸에는 13화 결정표를 따라간 경로를 적었다. #1 한 문장으로 쓸 수는 있지만, 규칙이 두 곳이 되거나 힌트에 기대게 되므로 쓰지 않는다. #2 fetch across commit 아님. #3 언두 3.7MB. → #4 끝에 한 번.

퇴근 전에 세 사람에게 같은 메시지를 보냈다.

나  18:55
보정 배치의 SQL 은 정말 다 빠릅니다 (한 문장 1~3 블록). 느린 건 그걸 정산 한 건마다 세 번씩 부르는 구조였습니다.
연습 DB 기준 정산 10만 건에 SQL 호출 30만 번 → 고친 판은 2,500번 남짓, 읽는 양은 37분의 1입니다.
그리고 보정 건수가 안 맞은 이유를 찾았습니다. 탈퇴 고객(5월 말 파기)과 금액 미확정 정산이 에러 없이 빠지고 있었습니다.
연습 DB 에 같은 경우를 만들어 보니 170건이 그렇게 빠졌고, 고친 판은 그 170건을 '반송함' 에 사유와 함께 남깁니다.
정산 확정 화면에 반송함을 같이 보여 주는 건 정산팀과 상의가 필요합니다.
운영 적용은 다음 주 월요일 전에 연습 서버에서 오과장님과 같이 돌려 보겠습니다.

조민우  19:02   보정 0원으로 보이던 게 그거였군요… 반송함 화면 같이 봐요. 탈퇴 고객 건은 저희가 수기로 처리할게요.
오과장  19:08   COUNT(*) 로 확인하는 게 안전하다고만 생각했어요. 수요일 저녁에 같이 돌려요.
박팀장  19:20   'SQL 은 빠른데 부르는 횟수가 문제' 라는 건 처음 알았습니다. 다음 주부터 시스템 전체를 본다고요?

리비전 폴더에 ADJ_rev00과 ADJ_rev01 두 파일이 생겼다. 그리고 P1_rev02 아래에는 오늘 적은 한 줄이 남았다. rev02에서 완결.

오늘 남은 것

무엇	값
P1 루프의 내부(10만 건)	rev00 재귀 호출 101,350 · 구동 커서 fetch 1,001(레벨 2 = 100행씩) → rev01 재귀 호출 183. 레벨 1이면 fetch 100,001 · 재귀 호출 200,387
왕복 한 번의 비용	PL/SQL → SQL 약 2.5µs · SQL → PL/SQL 약 0.53µs(UDF 0.1~0.2)
P1 판정	rev02에서 완결. PL/SQL로 더 잘 쓸 것이 남지 않았다(rev03 없음)
보정 배치 rev00	재귀 호출 301,185 · 함수 호출 100,020 · 논리 읽기 816,984 · 한 건당 SQL 3.01번
TKPROF	RECURSIVE Execute 299,865 · 등급 조회 `Execute = Fetch = 100,020`(행마다 호출된 한 건 조회의 지문)
한 문장의 함정	`MERGE` + 함수 → 함수 호출 100,020 그대로 · 스칼라 서브쿼리 캐시 57,679 · SQL 안의 `NO_DATA_FOUND` = 에러 없는 NULL
WHEN OTHERS THEN NULL	원장 100,020건 중 170건이 보정도 기록도 없이 빠졌다(탈퇴 고객 150 · 금액 미확정 20). 화면에는 '보정 0원'
보정 배치 rev01	재귀 호출 2,457 · 논리 읽기 22,363 · 함수 2,000 · 원장 = 보정 99,850 + 반송 170
FORALL의 함정 둘	원소마다 `MERGE`는 행마다 `MERGE`다(130cs · 논리 읽기 426,669) · `ERROR_INDEX`는 첨자가 아니다(6 vs 8)
한 문장과의 비교	CASE로 옮기면 23~32cs(규칙이 두 곳) · `MATERIALIZE` 25~34cs(빼면 함수 99,850번) · `LOG ERRORS` 하나로 23 → 105~117cs
패키지 상태	등급표 UGA +262~327KB · 재컴파일 → ORA-04068(그 세션의 상태 전부) · 스펙의 상수 하나로도 상태가 생긴다
NOCOPY · 레벨 3	10만 원소 IN OUT 1,000번 약 2초 → 0(예외 뒤 반쯤 고친 배열) · 지역 함수 300만 번 0.16 → 0.10초
PKG_LOAD_DRIVER	6조각 현재판 + SPIKE(NL · LIT · LOCK · LOOP) · 원본 지문 불변 · 남은 세션 0 → 환경 폴더 L1
T3 2호	주간 보정 배치 · 9번 칸 처음으로 채움

오늘 배운 한 문장

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

다음으로 넘기는 것

시스템 전체를 보는 눈: 오늘 만든 부하 발생기를 돌려 놓고 AWR을 처음 읽는다 → 16화
평균에 묻힌 몇 분: SPIKE 프로필로 만든 폭발과 블로킹 체인을 ASH로 본다 → 17화
같은 답을 두 번 계산하지 않기: 화면이 쓸 등급표는 결과 캐시로 둔다(오늘 패키지 변수로 하려다 데인 것) → 15b화
「SQL 코딩 가이드」 후보: WHEN OTHERS THEN NULL 금지 · 행마다 부르는 함수는 횟수를 적는다 · FORALL에는 묶이는 문장을 → 18화

다음 → 3_실습과_기록.md에서 루프 안을 직접 재 보고, 트레이스 한 줄을 읽고, 보정 배치를 고친다. 부하 발생기를 시운전한 뒤 T3 2호를 쓴다. 개념이 막히면 2_파고들기.md를 펼친다.

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

오전 9시 05분 ​

오전 9시 30분 — 한 문장은 세 블록 ​

오전 9시 50분 — 가져오는 쪽은 이미 묶여 있었다 ​

오전 10시 20분 — 지하 1층 수발실 ​

오전 11시 00분 — 트레이스의 한 줄 ​

오전 11시 40분 — 한 문장으로 쓰면 ​

오후 1시 00분 — 170건 ​

오후 1시 40분 — 어느 부름 아래에서 ​

오후 2시 20분 — FORALL인데 왜 ​

오후 3시 30분 — 한 문장이면 안 되나 ​

오후 4시 30분 — 책상 위의 등급표 ​

오후 5시 10분 — 다음 주에 쓸 물건 ​

오후 6시 20분 — T3 2호 ​

오늘 남은 것 ​

오늘 배운 한 문장 ​

다음으로 넘기는 것 ​