테마
09화 · 100페이지째부터 화면이 멈춘다
🗓️ 4월 29일 (월) · 08화에서 일주일 뒤 · 무대
ora-hanorder(저녁에는ora-dist) 오늘 여는 것: Q4. 01화에서 재 놓고 8주 동안 한 번도 열지 않은 조각이다. 운영팀은 "PGA부터 올리자"고 한다. 근거로 든 문장은 06화 리포트에 내가 쓴 문장이다.
파일 세 개
| 파일 | 무엇을 하나 | 시간 | |
|---|---|---|---|
| 읽는다 | 1_사건.md | 페이지 곡선을 그리고, 책상을 좁혀 멈춤을 재현하고, PGA를 올려 보고, 정렬을 없앤 끝에 3층으로 질문하러 가는 하루 | 40분 |
| 한다 | 3_실습과_기록.md | 페이지 곡선 · 책상 × 페이지 · rev01 · 포스트잇 · ARRAYSIZE · ora-dist · T1 7호 + 재협상서 1호 | 70~100분 |
| 찾는다 | 2_파고들기.md | Top-N · 외부 병합 정렬 · "PGA부터 올리자" 판정법 · NOSORT 조건 · OFFSET의 본질 · 키 기반 페이징 | 필요할 때 |
1을 읽고 3을 따라 한다. 2는 막힐 때 펼친다. 시간이 없는 주에는 1_사건을 중단 지점 1까지 읽고 3_실습은 STEP 0, 1, 2, 4, 5, 8만 한다(약 50분).
지난 화에서 들고 오는 것
Q4 + Q4c = 11,652블록 | 01화 원장 그대로다. 화면 한 장(10건)을 띄울 때마다 테이블을 두 번 읽는다 |
T_ORD_BIG 인덱스 | 2개(IX_ORD_BIG_DT, IX_ORD_BIG_DT_AMT). 02화에서 만들었고, 선두 컬럼이 Q4의 정렬 키와 같은 ORD_DT 컬럼이다 |
| 06화 T1 4호 「환경 의존성」 | "PGA가 줄면 같은 계획이 TEMP를 쓰기 시작하고 수십 배 느려진다" ← 오늘 처방전이 되어 돌아온다 |
| 03화가 넘긴 것 | ora-dist의 SLE_HMS 0 채움 누락 33,241건. 데이터 교정 승인을 기다리는 중이다 |
| 08화가 만든 규칙 | 모든 리포트에 「정합성 회귀 체크리스트」(템플릿)를 붙인다. T1 7호가 처음으로 붙이는 리포트다 |
그리고 박팀장에게서 메시지가 하나 와 있다.
"정산 목록 화면이 100페이지쯤 넘어가면 멈춘답니다. 1페이지는 괜찮답니다. 지난번 리포트에 'PGA가 줄면 수십 배 느려진다'고 쓰셨던데, 그럼 PGA부터 올리면 되는 것 아닙니까?"
오늘 여는 코드
sql
-- Q4 : 정산 목록 100페이지째 (991~1000번째)
SELECT * FROM (SELECT ROWNUM rn, a.*
FROM (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
FROM T_ORD_BIG ORDER BY ORD_DT DESC, ORD_SEQ DESC) a
WHERE ROWNUM <= 1000)
WHERE rn > 990;
-- Q4c : 화면 하단 "총 N건"
SELECT COUNT(*) FROM T_ORD_BIG;【그때의 근거】
2019-05-20 hd.kang"정산 목록 페이징: ROWNUM을 안쪽 인라인뷰로 옮김. 바깥에 두면 전체를 정렬함. 총건수 표시는 정산팀 화면 요구사항."강도현은 Top-N 정렬을 알고 있었다.
ROWNUM을 바깥에만 두면 1페이지에서도 정렬에 48M을 쓴다(실측). 강도현은 이것을 4K로 줄였다. 총건수도 강도현이 원해서 넣은 게 아니었다. 그 요구가 아직 필요한지 다시 물어본 사람이 없었을 뿐이다.
오늘의 전개 — 세 번 틀리고, 3층에 간다
| 내가 믿은 것 | 측정 | |
|---|---|---|
| 1차 | "1페이지는 괜찮다" (현업의 증언) | 1페이지도 100페이지도 5,826. 다른 건 정렬 책상(Used-Mem 4K → 74,638K)뿐이다 |
| 2차 | "PGA를 올리면 된다" | 64KB 책상에서는 64페이지부터 184 PASSES(멈춤 재현). 1MB로 올리면 100페이지는 살아나지만 10,000페이지에서 다시 2 PASSES가 난다. 멈추는 페이지가 뒤로 밀릴 뿐이다. Buffers는 5,826 아래로 내려가지 않는다 |
| 3차 | "02화 인덱스가 정렬된 목록이니까 이미 쓰고 있을 것이다" | 안 쓴다. 힌트도 듣지 않는다. ORD_DT가 NULL을 허용하고, 정렬 키 ORD_SEQ가 인덱스에 없다 |
| rev01 | 인덱스 (ORD_DT, ORD_SEQ), SQL은 그대로 | 100페이지 5,826 → 1,007. 책상이 사라졌고 Q4c도 저절로 3,365로 줄었다 |
| — | 그런데 페이지를 뒤로 넘기면 | 14 → 1,007 → 10,037 → 1,003,347. 이제야 "뒤로 갈수록 느려진다"는 말이 Buffers로 보인다 |
| rev02 | 포스트잇(키 기반 페이징) + 화면 요구 재협상 | 어느 페이지든 15~18. 한 번 클릭에 11,652 → 약 15 |
왜 이렇게 짰을까? 06화에서 "PGA가 모자라면 느려진다"를 배웠고, 오늘 그 문장이 처방이 되어 돌아온다. 메모리는 정렬을 빨리 하게 할 뿐 안 하게 하지는 못한다. 그리고 정렬을 없앤 뒤에도 세는 비용은 남는다. 오늘은 이 두 가지를 차례로 확인한다. 마지막 한 걸음(4,372 → 15)은 SQL 안에 없다. 화면을 쓰는 사람에게 직접 물으러 가야 한다.
오늘 배우는 한 문장
정렬은 다 읽어야 첫 줄이 나온다. 그리고 OFFSET은 앞에서부터 센다. 메모리는 정렬을 빨리 하게 할 뿐 안 하게 하지는 못하고, 인덱스는 정렬을 없애 주지만 세는 일은 남긴다. 세는 일까지 없애려면 SQL이 아니라 화면이 묻는 질문을 바꿔야 한다. "몇 번째부터"가 아니라 "어디부터"를 묻게 하는 것이다.
| 곡선 | SQL | 페이지 p의 비용 |
|---|---|---|
| 평평하고 높다 | rev00: FULL + Top-N 정렬 | 상수(5,826) |
| 기울었다 | rev01: 인덱스가 정렬을 대신한다 | ≈ 10p(버리는 행마다 테이블 한 블록) |
| 평평하고 낮다 | rev02: 포스트잇 | 상수(≈ 15) |
🔑 오늘의 비유 — 출입대장에 붙인 포스트잇
선배가 02화의 그 방문객 출입대장을 다시 꺼내 들고 말한다. "최근 방문자부터 거꾸로, 열 명씩 불러 줘." 1페이지는 마지막 장의 열 줄만 부르면 된다. 100페이지는 손가락으로 990줄을 센 다음에야 열 줄을 부를 수 있다. 101페이지를 부르려고 또 처음부터 세려는 순간 선배가 묻는다. "왜 처음부터 세?" 그러고는 다음에 읽을 줄에 포스트잇을 한 장 붙인다.
| 사무실 | 오라클 |
|---|---|
| 출입대장(방문 일시 순) | 정렬 키와 같은 인덱스 (ORD_DT, ORD_SEQ) |
| 손가락으로 990줄 세기 | OFFSET(ROWNUM <= 1000 … rn > 990): 페이지가 깊을수록 오래 걸린다 |
| 포스트잇 | 키 기반 페이징: 지난번에 멈춘 (ORD_DT, ORD_SEQ)부터 읽는다 |
| 포스트잇을 붙이는 자리 = 다음에 읽을 줄 | 10건을 보여 주려고 가져온 11번째 행(= "다음 페이지 있음") |
06화의 책상도 다시 나온다. 정렬은 책상(work area) 위에서 한다. 책상이 좁으면 묶음(run)을 바닥(TEMP)에 내려놓았다가 나중에 합친다. 책상을 넓혀도 100만 건을 처음부터 끝까지 넘겨 봐야 하는 건 마찬가지다.
(01화 커피 캐리어 = 블록 · 02화 방문객 출입대장 = 인덱스 · 06화 4층 카드함과 책상 = 조인과 work area)
☠️ 오늘의 함정 — PGA를 올리면 해결된 것처럼 보인다
64KB 책상에서 100페이지가 멈춘다. 1MB로 올리면 0.04초로 돌아온다. 박팀장에게 "해결됐습니다"라고 쓰고 싶어진다.
| 책상 | 100페이지 | 10,000페이지 | Buffers |
|---|---|---|---|
| 64KB | 184 PASSES · 6~24초 | 290 PASSES | 7,162 / 8,013 |
| 1MB | optimal · 0.04초 | 2 PASSES · 1.5~6.2초 | 5,826 / 5,901 |
| 16MB | optimal | optimal | 5,826 |
멈추는 페이지가 뒤로 밀렸을 뿐이다. 게다가 마지막 페이지까지 optimal로 끝내려면 사용자 한 명이 한 번 누를 때마다 74,638K가 필요하다. 읽는 양(Buffers 5,826 × 2)은 한 블록도 줄지 않았다.
리포트에 쓴 문장을 다른 사람은 처방으로 읽는다. 그래서 「환경 의존성」 칸에는 "그러니 무엇을 하라"가 아니라 "무엇을 재 보라"까지 써야 한다.
실습 준비
bash
docker start ora-hanorder
docker start ora-dist # STEP 7 만08화를 마친 상태(T_ORD_BIG에 인덱스 IX_ORD_BIG_DT, IX_ORD_BIG_DT_AMT)에서 시작한다. ora-dist는 03화를 마친 상태여야 한다.
| 스크립트 | 컨테이너 | 무엇 | 걸리는 시간 |
|---|---|---|---|
실습/00_페이지_곡선.sql | ora-hanorder | rev00의 페이지 곡선 · 02화 인덱스를 못 쓰는 이유 · STOPKEY | 약 3초 |
실습/01_책상_좁히기_정렬.sql | ora-hanorder | 책상 × 페이지 · 63 → 64 절벽 · sorts (disk) · direct path write temp | 20초~1분(일부러 느리게 만든다) |
실습/02_인덱스로_정렬_대신.sql | ora-hanorder | rev01 · Q4c가 저절로 바뀐다 · INVISIBLE의 함정 · 갈아타는 페이지 · 늦은 테이블 방문 | 약 5초 |
실습/03_포스트잇.sql | ora-hanorder | rev02 · 정합성 체크리스트(10만 페이지 전체 순회) · 날짜로 가기 · 총건수 대안 | 약 5초 |
실습/04_ARRAYSIZE_와_Fetch.sql | ora-hanorder | 엑셀 내려받기 · roundtrips 1,827 → 29 | 약 1초 |
실습/05_본사_매출목록.sql | ora-dist | 정렬 키가 틀렸다 · SLE_NO로 4,511 → 7 · 복사본에서 교정 확인 | 약 3초 |
실습/90_정렬_트레이스_10032.sql | ora-hanorder | (선택) 외부 병합 정렬의 묶음 · 합치기 폭 · 합치기 횟수 | 약 6~30초 |
모든 스크립트는 맨 앞에서 초기화하고 시작한다. 그래서 몇 번을 돌려도 결과가 같다. ⚠️ 00과 01은 맨 앞에서 09화 인덱스(IX_ORD_BIG_DT_SEQ)를 지운다. 따라서 마지막에는 02(또는 03)를 돌린 상태로 끝내야 한다.ora-dist의 CF10 원본에는 쓰지 않는다. 교정 실험은 복사본에서 하고, 끝나면 복사본을 지운다.
리비전은 리비전/에 Q4_rev00 → Q4_rev01 → Q4_rev02 순서로 남는다.
ℹ️ 이 교재의 실습은 Docker로 띄운 Oracle Database Free 한 대로 모두 끝낼 수 있다. 동시 사용자 수십 명이 정렬 책상을 나눠 쓰는 상황은 만들 수 없으므로, 세션 파라미터(
WORKAREA_SIZE_POLICY=MANUAL+SORT_AREA_SIZE)로 한 세션의 책상 크기를 직접 정해 재현한다. 서버 메모리를 바꿔 가며 대규모 EE 환경과 대조하는 일은 이 교재의 범위 밖이다(별권).
이 화의 산출물
| 무엇 | 어디 |
|---|---|
Q4_rev01: 인덱스가 정렬을 대신한다. 100페이지 5,826 → 1,007 | 리비전/Q4_rev01.sql |
Q4_rev02: 포스트잇 + 총건수 제거. 한 번 클릭에 11,652 → 약 15 | 리비전/Q4_rev02.sql |
| T1 리포트 7호: 「기각한 대안」에 PGA 증설이 들어가고, 08화의 정합성 회귀 체크리스트를 처음으로 붙인 리포트 | 3_실습과_기록.md STEP 9 |
| 「화면 요구 재협상서」 ★ 새 표준 문서 | 40_보고서와_표준/템플릿/화면요구_재협상서.md |
이 화가 다음으로 넘기는 것
| 무엇 | 어디로 |
|---|---|
포스트잇 실행계획의 E-Rows 999K vs A-Rows 11, Cost에 정렬 CPU가 들어가서 935페이지에서 갈아타는 이유 | 10화: 옵티마이저의 셈 |
| 포스트잇 값을 문자열로 박으면 페이지마다 SQL이 달라진다 | 11화: 바인드 변수 |
T_ORD_BIG 인덱스 3개의 적재 비용 | 14화: 청구서에 한 줄이 더 붙는다 |
ARRAYSIZE를 PL/SQL에서 정하는 BULK COLLECT LIMIT | 15화 |
| 재협상서 1호의 다음 재협상 조건(정렬 기준 추가 · 검색 조건 추가) | 조건에 걸리면 다시 잰다 · 18화 원장 재측정 |
← 08화 「금액이 50배로 찍힌다」 · 교재 전체 안내 · 다음 → 10화 「어제는 0.1초 오늘은 30초」