테마
05화 · 블록이라는 그릇
4월 1일 월요일. 오늘은 조각을 고치는 날이 아니다. 오늘 만드는 것은 숫자가 아니라 빚이다. 여기서 생긴 고수위선과 공간 빚의 청구서는 14화 「야간 배치가 아침을 넘긴다」로 간다.
오전 9시 10분
정산 현업 조민우 씨가 먼저 전화를 걸어 왔다. 메신저를 안 쓰는 사람이다.
"저기, 지난주 금요일에 운영팀에서 로그 데이터 정리했다고 하던데요."
"네, 들었습니다. 주문상태이력 3년치 중에 2년 11개월치를 지웠다고요."
"그게 끝났다고 메일이 왔어요. 그런데 조회가 똑같아요." 수화기 너머로 마우스 클릭 소리가 났다. "지금도 돌려 보고 있는데, 체감이 지난주랑 같습니다. 데이터가 20분의 1로 줄었다면서요."
모니터를 봤다. T_ORD_LOG는 주문 하나가 접수→결제→배송→완료로 넘어갈 때마다 한 줄씩 쌓이는 테이블이다.
"확인해 보고 연락드리겠습니다." 전화를 끊을 때쯤 나는 거의 확신하고 있었다. 답이 뻔해 보였다.
"선배님, 통계 문제 같은데요. 데이터를 지웠는데 통계를 다시 안 모아서, 옵티마이저가 아직 50만 건인 줄 아는 거 아닐까요."
선배가 모니터에서 눈을 떼지 않고 말했다.
"그럴듯하네. 그럼 재 봐."
오전 9시 40분 — 통계는 멀쩡했다
먼저 통계부터 봤다.
sql
SELECT num_rows, blocks, empty_blocks, avg_row_len
FROM user_tables WHERE table_name = 'T_ORD_LOG'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_ROW_LEN
---------- ---------- ------------ -----------
24975 3665 0 47NUM_ROWS가 24,975였다. 50만 건에서 2만 5천 건으로 줄어 있었다. 통계는 제대로 갱신돼 있었다. 내 가설은 첫 줄에서 무너졌다.
"통계는 맞는데요. 2만 5천 건으로 알고 있어요."
"그럼 다음." 선배는 놀라지 않았다. "조민우 씨가 돌리는 그 조회, 그대로 재 봐."
지난주에 선배가 가르쳐 준 대로 삭제 전 상태를 다시 만들어 놓고 양쪽을 쟀다. 같은 SQL, 같은 세션이었다.
sql
SELECT /*+ GATHER_PLAN_STATISTICS */ COUNT(*), MAX(LOG_DT)
FROM T_ORD_LOG WHERE LOG_ST = 'C';삭제 전 (500,000행)
|* 2 | TABLE ACCESS FULL | T_ORD_LOG | 1 | 100K| 100K|00:00:00.01 | 3602 |
삭제 후 ( 24,975행)
|* 2 | TABLE ACCESS FULL | T_ORD_LOG | 1 | 4995 | 4995 |00:00:00.01 | 3602 |
Starts E-Rows A-Rows A-Time Buffers3,602 → 3,602.
숫자를 두 번 읽었다. 95%를 지웠는데 한 블록도 줄지 않았다.
A-Rows는 정직하게 100,000에서 4,995로 줄었다. 읽어서 통과한 행은 20분의 1이 됐다. 그런데 읽은 블록은 그대로였다.
"조민우 씨 말이 맞네요. 진짜 똑같아요."
오전 10시 20분 — 두 번째로 틀린다
"왜 그럴 것 같아?" 선배가 물었다.
생각해 봤다. 블록 하나에 행이 여러 개 들어간다고 배웠다. 01화의 캐리어다. 그럼 행이 줄었으면 캐리어도 덜 필요해야 한다.
"…블록 안이 비었으면 남은 행들을 모아서 더 적은 블록에 담으면 되잖아요. 2만 5천 건이면 캐리어가 몇 개 안 필요한데요."
"'되잖아요'하고 '된다'는 달라." 선배가 의자를 돌렸다. "누가 모아 줘?"
"…오라클이요?"
"언제? 네가 DELETE를 쳤을 때? 그러면 DELETE 한 줄에 테이블 전체를 다시 쓰는 일이 딸려 와. 2만 5천 건이 아니라 2천만 건짜리 테이블이면 그 DELETE는 언제 끝나?"
나는 입을 다물었다.
"일단 블록 안이 어떻게 돼 있는지부터 봐." 선배가 알려 준 건 DBMS_SPACE.SPACE_USAGE였다. 이 프로시저를 쓰면 세그먼트 안의 블록이 얼마나 차 있는지 구간별로 셀 수 있다.
FS3 50~75% 비어 있는 블록 : 473
FS4 75~100% 비어 있는 블록 : 3123
FULL 꽉 찬 블록 : 0 <- 합계 3,596 중꽉 찬 블록이 0개였다. 3,123개는 75% 이상 비어 있었다.
"비긴 비었네요. 거의 텅 비었어요."
"그래. 비었어. 그런데 거기 그대로 있어." 선배가 일어섰다. "따라와 봐. 3층."
오전 11시 00분 — 3층 문서보관실
선배가 향한 곳은 3층 끝 문서보관실이었다. 처음 들어가 보는 곳이었다. 형광등을 켜니 긴 철제 선반이 벽을 따라 쭉 이어져 있었다. 선반마다 칸이 나뉘어 있고, 칸마다 누런 서류 상자가 꽂혀 있었다.
"여기엔 규칙이 하나 있어. 상자를 넣을 땐 왼쪽 끝부터, 빈칸 없이 채워."
"왜요?"
"그래야 찾기 쉽잖아. 다음 사람이 어디에 넣을지 고민할 필요도 없고." 선배가 선반 중간쯤을 가리켰다. "자, 저기 봐."
선반 모서리에 분필로 그어 놓은 굵은 선이 있었다. 그 왼쪽에는 상자가 꽂혀 있고, 오른쪽은 빈칸이었다.
"저건 총무팀이 긋는 거야. '여기까지 써 봤다'는 표시지. 상자를 더 넣다가 선을 넘어가면 선을 오른쪽으로 다시 그어."
"…넘어갈 때만 긋는 거예요?"
"그래. 넘어갈 때만."
선배가 선반 쪽으로 걸어가며 말했다. "총무팀이 분기마다 실사를 해. 상자 안에 뭐가 들었는지 대장이랑 맞춰 보는 거야. 그 사람은 어디까지 확인할까?"
나는 분필 선을 봤다.
"…분필 선까지요."
"상자가 비었는데도?"
"열어 봐야 비었는지 알잖아요."
"그렇지." 선배가 선반 앞에 서서 왼쪽부터 상자를 열기 시작했다. 첫 번째, 비었다. 두 번째, 비었다. 세 번째, 비었다. 열 번째쯤에서 서류 한 뭉치가 나왔다. 그 뒤로 다시 빈 상자가 열댓 개 이어졌다.
"작년에 보존 기한 지난 서류를 싹 버렸어. 상자는 그대로 두고 안의 서류만." 선배가 손을 털었다. "그러고 나서 총무팀 실사 시간이 줄었을까?"
분필 선을 다시 봤다. 아무도 그 선을 지우지 않았다.
"……안 줄었겠네요. 똑같이 분필 선까지 다 열어 봐야 하니까."
"그게 오늘 네가 본 3,602야." 선배가 벽에 기댔다. "오라클은 테이블마다 분필 선을 하나씩 갖고 있어. 고수위선(High Water Mark). '이 세그먼트에서 여기까지는 써 봤다'는 선이야. 전체 스캔은 행이 어디까지 있는지가 아니라 분필 선까지 읽어. 블록을 열어 봐야 비었는지 아니까."
"그럼 DELETE는…"
"상자 안의 서류만 버리는 거야. 상자도 안 치우고 분필 선도 안 옮겨."
오전 11시 40분 — 그릇을 담는 그릇
자리로 돌아와서도 한 가지가 걸렸다.
"선배님, 그 '세그먼트'라는 게 뭐예요? 아까부터 나오는데요."
"그려 줄게." 선배가 종이를 꺼냈다.
테이블스페이스 ─ 데이터파일 몇 개를 묶은 것. 블록 크기를 여기서 정한다
└ 세그먼트 ─ 그 안에서 테이블 하나(또는 인덱스 하나)가 차지한 덩어리
└ 익스텐트 ─ 세그먼트가 공간이 모자랄 때 한 번에 받아 오는 단위
└ 블록 ─ 실제로 읽고 쓰는 최소 단위. 여기선 8KB"선반 전체가 테이블스페이스야. 그중 '우리 팀 구역'이 세그먼트, 한 번에 새로 받는 선반 한 줄이 익스텐트, 칸 하나가 블록이고."
"그럼 분필 선은 어디에 있는 거예요?"
"세그먼트에. 세그먼트 안에서 '여기까지'를 가리켜."
지난 화까지 만져 온 T_ORD_BIG으로 네 층을 직접 봤다. USER_SEGMENTS의 할당 블록이 6,016, USER_TABLES의 고수위선 아래 블록이 5,923, 전체 스캔을 돌렸을 때 Buffers가 5,826이었다. 숫자가 세 개 나왔는데 셋 다 달랐다.
"할당받은 건 6,016. 분필 선 아래는 5,923. 실제로 읽는 건 5,826." 선배가 짚었다. "6,016에서 5,923을 뺀 93개는 받아만 놓고 아직 안 쓴 칸이야. 익스텐트는 8칸이나 128칸을 통째로 받아 오거든. 그래서 끝에 자투리가 남아. 크기가 왜 다른지는 2_파고들기 §2에 적어 뒀어."
"숫자가 하나 더 있어." 선배가 말했다. "행이 진짜로 들어 있는 블록은 몇 개야?"
셀 방법을 몰랐다. 선배가 DBMS_ROWID라는 패키지를 알려 줬다. 행의 주소에서 블록 번호만 뽑아낼 수 있다고 했다.
sql
SELECT COUNT(*) AS "행수",
COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) AS "행이 든 블록"
FROM T_ORD_BIG; 행수 행이 든 블록
---------- -------------
1000000 58135,813. 분필 선 아래 5,923개 중에 110개는 행이 하나도 없었다.
그리고 나눗셈을 해 봤다. 1,000,000 ÷ 5,813 = 172. 01화에서 선배가 "한 블록에 169건"이라고 했던 바로 그 값을 다시 계산한 것이다. 그때는 blocks 5,923으로 나눴다.
"169가 틀린 거예요?"
"틀린 게 아니라 다른 걸 센 거야." 선배가 웃었다. "169는 '분필 선 아래 블록 하나당 평균 몇 건'이고, 172는 '행이 실제로 들어 있는 블록 하나당 몇 건'이야. 차이는 블록 110개고, 그 110개가 오늘 얘기의 씨앗이야. 빈 상자인데도 매번 열리거든."
⛳ 오늘은 여기까지(중단 지점 1) 여기까지가 오늘의 절반이다. 지웠는데 왜 안 줄어드는지, 그리고 4계층과 고수위선을 봤다. 이어서 읽으면 고친다. 그리고 두 번 더 틀린다.
오후 1시 30분 — 세 번째로 틀린다
점심을 먹고 오자 선배가 물었다.
"그래서, 분필 선을 어떻게 내릴 건데?"
아침에 본 SPACE_USAGE를 떠올렸다. 블록 3,123개가 75% 이상 비어 있었다. 남은 2만 5천 행을 앞으로 당겨 모으면 되지 않을까. 찾아보니 명령이 있었다.
sql
ALTER TABLE T_ORD_LOG ENABLE ROW MOVEMENT;
ALTER TABLE T_ORD_LOG SHRINK SPACE COMPACT;"이거네요. COMPACT. 압축해서 모은다는 뜻이잖아요."
"돌려 봐."
0.4초 만에 끝났다. 블록 상태를 다시 세니 FS3=1 FS4=3416 FULL=179였다. 꽉 찬 블록이 0개에서 179개가 됐다. 2만 5천 행이 앞쪽 블록 179개에 빽빽하게 모인 것이다.
"됐네요. 모였어요." 나는 자신 있게 조회를 돌렸다.
|* 2 | TABLE ACCESS FULL | T_ORD_LOG | 1 | 4995 | 4995 |00:00:00.01 | 3602 |3,602. 또 그대로였다.
"……."
"COMPACT가 뭘 했는지 봤지? 상자 안의 서류를 왼쪽 상자들로 옮겨 담았어." 선배가 말했다. "그런데 분필 선은?" user_tables.blocks는 3,665 그대로였다.
"SHRINK SPACE COMPACT는 이름 그대로 모으기만 해. 선은 안 건드려." 선배가 말했다. "이게 따로 있는 데는 이유가 있어. 모으는 건 오래 걸리지만 다른 사람 작업을 거의 안 막아. 선을 내리는 순간에는 짧게나마 테이블 전체를 잠그거든. 그래서 업무 시간에 모아 두고, 선은 밤에 내려."
"그럼 선을 내리는 건 뭐예요?"
"COMPACT를 빼."
sql
ALTER TABLE T_ORD_LOG SHRINK SPACE;세그먼트블록 익스텐트수 HWM아래블록
------------ ---------- -----------
200 17 180|* 2 | TABLE ACCESS FULL | T_ORD_LOG | 1 | 4995 | 4995 |00:00:00.01 | 183 |3,602 → 183.
분필 선 아래 블록이 3,665에서 180이 됐다. 세그먼트는 3,712블록에서 200블록으로, 익스텐트는 44개에서 17개로 줄었다. 받아 놨던 공간까지 반납한 것이다.
"19.7배네요."
"배수부터 말하지 마." 선배가 짚었다. "Buffers 3,602 → 183. 그게 T0 규약이야."
오후 2시 10분 — 길이 네 개 있다
선배가 네 가지를 차례로 재 보게 했다. 같은 상태(50만 적재 → 95% 삭제)를 매번 다시 만들어서 쟀다.
| 조치 | 고수위선 아래 블록 | 세그먼트 블록 | 익스텐트 | Buffers |
|---|---|---|---|---|
| (아무것도 안 함) | 3,665 | 3,712 | 44 | 3,602 |
SHRINK SPACE COMPACT | 3,665 | 3,712 | 44 | 3,602 |
SHRINK SPACE | 180 | 200 | 17 | 183 |
ALTER TABLE MOVE | 192 | 256 | 17 | 183 |
TRUNCATE | 0 | 8 | 1 | 1 |
"MOVE는 뭐가 달라요? 결과가 같은데."
"가는 길이 달라. SHRINK는 제자리에서 행을 앞으로 당겨. MOVE는 세그먼트를 새로 하나 만들어서 통째로 복사하고 옛날 걸 버려. 그래서 하는 동안 공간이 두 배로 필요하고, 끝나면 인덱스가 전부 UNUSABLE이 돼. 그 이유는 조금 이따 얘기할게." 선배가 다음으로 넘어갔다. "그리고 TRUNCATE는 분필 선을 맨 왼쪽으로 되돌려. 1블록. 근데…"
"되돌릴 수 없죠."
"그래. 그리고 WHERE 절도 못 써. 보관할 게 있으면 TRUNCATE는 못 쓴다." 선배가 메모지에 적었다. "오늘 네가 리포트에 쓸 문장이 여기 있어. 고수위선은 DELETE로는 안 내려간다. SHRINK나 MOVE나 TRUNCATE 중 하나를 명시적으로 해야 내려가."
선배가 마지막으로 하나를 덧붙였다. "셋 다 운영 시간에 하지 마. 그리고 하기 전에 지금 Buffers를 재 놔. 안 재 놓으면 나중에 '좋아졌다'는 말을 증명할 수가 없어." 나머지 안전 수칙은 2_파고들기 §4에 정리해 뒀다.
오후 2시 40분 — 주소에 적혀 있지 않은 것
"선배님, 아까 MOVE를 하면 인덱스가 죽는다고 하셨잖아요. 왜요?"
"인덱스에 뭐가 들어 있지?"
"키값이랑… ROWID요."
"그 ROWID가 뭔데?"
"행의 주소요"라고 대답했다가, 그게 정확히 무슨 뜻인지 모른다는 걸 깨달았다. 선배가 DBMS_ROWID로 분해해 보라고 했다.
ROWID 데이터오브젝트 상대파일 블록 행번호
-------------------- -------------- ---------- ---------- ----------
AAAR2eAAYAAABgLAAA 73118 24 6155 0
AAAR2eAAYAAABgLAAB 73118 24 6155 1
AAAR2eAAYAAABgLAAC 73118 24 6155 2열여덟 자리짜리 그 문자열은 네 조각으로 나뉘었다. 앞의 세 조각은 같고 마지막만 0, 1, 2로 늘어났다. 같은 블록 안의 행 세 개였다.
"블록 6155번 안의 0번 행. 그게 주소야." 선배가 말했다. "자, 그럼 질문. 이 주소 어디에 테이블스페이스가 있어?"
네 숫자를 다시 봤다. 오브젝트, 파일, 블록, 행 번호. 테이블스페이스는 없었다.
"…없는데요. 근데 파일 번호가 있으니까 파일을 보면 되잖아요."
"그 파일 번호는 상대 파일 번호야." 선배가 쿼리를 하나 더 줬다.
TABLESPACE_NAME BIG 절대 상대
---------------- --- ---------- ----------
USERS NO 24 24
SYSAUX YES 22 1024
SYSTEM YES 21 1024
UNDOTBS1 YES 23 10241024번이 세 개였다.
"어, 겹치는데요."
"겹쳐도 돼. 상대 파일 번호는 '테이블스페이스 안에서' 고유해. 테이블스페이스가 다르면 같은 번호가 또 나와." 선배가 말했다. "그러니까 ROWID 안의 '파일 1024번'만 보고는 어느 테이블스페이스인지 알 수가 없어."
"그럼 어떻게 알아요?"
"오브젝트 번호로 알아. 73118이 어떤 테이블인지 사전에서 찾고, 그 테이블이 어느 테이블스페이스에 있는지 또 찾아. '어디'를 알려면 '무엇'을 먼저 알아야 돼." 그러고는 선배가 결정적인 걸 보여 줬다.
sql
SELECT DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID, USER, 'T_ORD_BIG') FROM T_ORD_BIG WHERE ROWNUM=1;"이게 ROWID에서 절대 파일 번호를 구하는 함수야. 인자가 셋이지. ROWID, 소유자, 테이블명. ROWID만으로 구할 수 있었으면 인자는 하나면 됐겠지." 선배가 말했다. "오라클이 만든 함수가 '소유자랑 테이블명을 내놔'라고 요구하는 것 자체가 답이야. 그 정보가 ROWID 안에 없다는 뜻이거든."
"그래서 MOVE를 하면…"
"블록 번호가 전부 바뀌어. 세그먼트를 새로 만들어서 복사하니까. 인덱스에 적힌 주소는 전부 옛날 자리를 가리키게 돼. 그래서 UNUSABLE이 되는 거야. 오라클이 친절해서 죽이는 게 아니라, 살려 두면 엉뚱한 데를 찾아가거든."
오후 3시 40분 — 그 테이블은 왜 그렇게 생겼나
"그런데 선배님, 애초에 이 테이블은 왜 이렇게 커요? 50만 건에 3,665블록이면…"
"테이블 정의는 봤어?"
sql
CREATE TABLE T_ORD_LOG (
LOG_ID NUMBER NOT NULL, ORD_SEQ NUMBER NOT NULL, LOG_DT DATE NOT NULL,
LOG_ST VARCHAR2(4000), LOG_MSG VARCHAR2(4000), LOG_DTL VARCHAR2(4000));VARCHAR2(4000)이 세 개였다. 상태 코드 한 글자가 들어가는 LOG_ST도 4000이었다. 커밋 로그를 찾아봤다.
2019-07-22 hd.kang
주문상태이력 테이블 신설. 운영에서 "뭐가 언제 바뀌었는지 모르겠다"는 요청이 반년째.
어떤 값이 들어올지 아직 모른다. 길이 초과로 적재가 실패해서 야간 배치가 죽는 것만은 피한다.
-> 문자 컬럼은 전부 VARCHAR2(4000)."이거 때문이죠? 4000자씩 잡아 놔서 테이블이 큰 거."
"재 봐."
같은 데이터를 담은 테이블 두 개를 만들었다. 하나는 VARCHAR2(4000) 세 개, 다른 하나는 VARCHAR2(2), VARCHAR2(30), VARCHAR2(100)이다. 둘 다 20만 건씩 똑같은 값을 넣었다.
테이블 행수 블록 평균행길이 전체스캔 Buffers
---------- ---------- ---------- ---------- ---------------
T_W4000 200000 874 23 821
T_W30 200000 874 23 821874 대 874. 평균 행 길이도, 전체 스캔 Buffers도 같았다.
"……똑같은데요."
"VARCHAR2는 가변 길이야. 'CUST-000012'를 넣으면 11바이트를 쓰고 끝이야. 선언을 4000으로 하든 30으로 하든 디스크에는 똑같이 11바이트가 들어가." 선배가 말했다. "선언 길이는 상한선이지 할당량이 아니야." 실제로 USER_TAB_COLUMNS를 보니 DATA_LENGTH는 4000과 30으로 달랐지만 AVG_COL_LEN은 양쪽 다 12였다.
"그럼 VARCHAR2(4000)이 나쁜 게 아니네요?"
"나쁘긴 한데, 네가 생각한 이유 때문은 아니야."
통계가 없으면 선언 길이가 비용이 된다
선배가 조건을 하나 바꿨다. 통계를 지웠다.
"로그 테이블에 통계를 꼬박꼬박 모으는 현장이 몇이나 될 것 같아? 야간 배치 시간을 아끼려고 통계 수집 대상에서 빼는 일이 흔해." 통계 없이 같은 정렬 쿼리를 돌리자 실행계획이 갈렸다.
T_W4000 : |* 3 | SORT ORDER BY STOPKEY | 71389 | 272M | 278M | 59755 (1)|
T_W30 : |* 3 | SORT ORDER BY STOPKEY | 71389 | 4810K |5608K | 1401 (1)|
E-Rows E-Bytes E-Temp Cost같은 데이터인데 Cost가 59,755 대 1,401이었다. 42배다. 한쪽은 "이 정렬에 272MB가 필요하고 디스크를 278MB 쓸 것"이라고 믿었고, 다른 쪽은 "4.8MB면 된다"고 믿었다. 실제로 읽은 Buffers는 822 대 822로 똑같았다.
"읽은 건 같아. 다른 건 오라클이 비싸다고 믿는 정도야." 선배가 말했다. "옵티마이저는 그 믿음으로 조인 순서를 정하고, 해시 조인을 할지 소트 머지를 할지 정해. 눈이 42배 흐려진 채로."
그리고 진짜로 터지는 자리
"하나 더. 저 테이블에 (ORD_NM, ORD_MEMO) 복합 인덱스를 만들고, 그 두 컬럼에 4000자를 꽉 채운 행을 넣어 봐."
INSERT INTO T_W4000 VALUES (999999, RPAD('z',4000,'z'), RPAD('z',4000,'z'), RPAD('z',4000,'z'))
*
ERROR at line 1:
ORA-01450: maximum key length (6397) exceededORA-01450. 인덱스를 만들기 전에는 똑같은 INSERT가 멀쩡히 들어갔었다. 8KB 블록에서 인덱스 키는 6,397바이트를 넘을 수 없는데, 4000 더하기 4000은 8000이다.
"느려지는 정도가 아니라 야간 배치가 죽는 거야." 선배가 말했다. "그것도 '누가 진짜로 4000자를 채우는 날' 처음 죽어. 3년 뒤일 수도 있고."
선언 길이는 '최대 이만큼'이 아니라 '여기까지는 들어올 수 있다는 약속'이다. 그 약속이 넓으면 옵티마이저도 인덱스도 그 넓이를 계산에 넣는다.
오후 4시 30분 — 행이 이사를 간다
"그리고 이 테이블엔 하나가 더 있어." 선배가 커밋 로그를 더 내려 봤다.
2020-11-05 hd.kang
상태 코드만으로는 운영이 못 알아봄. LOG_ST 에 코드 + 설명 문자열을 함께 적는다.
컬럼을 새로 추가하면 배포 승인이 2주 걸림. 기존 컬럼이 VARCHAR2(4000)이니 그냥 넣는다."적재할 땐 '01' 두 글자를 넣고, 나중에 배치가 200자짜리 설명으로 UPDATE를 한다는 거야. 2020년부터."
"그게 왜요? 길어져 봐야 200자인데요."
"재 봐."
테이블 다섯 개를 만들었다. PCTFREE만 다르게 했다. 전부 10만 행을 상태 '01'과 패딩 60자로 넣고, 그중 네 개는 나중에 200자로 UPDATE했다. 마지막 하나(T_MIG_OK)에는 처음부터 200자짜리를 넣었다. 최종 데이터는 모두 같다.
"PCTFREE가 뭐예요?"
"블록을 채울 때 남겨 두는 빈자리. 10이면 '90%만 채우고 10%는 비워 둬'라는 뜻이야." 선배가 말했다. "왜 비워 두겠어?"
아직은 몰랐다. UPDATE를 돌리고 ANALYZE TABLE ... COMPUTE STATISTICS로 다시 쟀다.
테이블 PCTFREE 블록 이전행 이전율%
---------- ---------- ---------- ---------- ----------
T_MIG00 0 3898 76169 76.2
T_MIG10 10 4150 73494 73.5
T_MIG40 40 5158 56984 57
T_MIG75 75 3772 0 0
T_MIG_OK 0 3520 0 0이전행 칸의 값은 CHAIN_CNT 컬럼에서 나왔다. PCTFREE 0짜리는 10만 행 중 76,169행이 여기에 걸렸다. DBMS_STATS는 이 컬럼을 채우지 않고, ANALYZE로만 값이 나온다.
"이게 뭐예요?"
"이사 간 행." 선배가 말했다. "행이 72바이트에서 270바이트로 커졌어. 3.75배지. 그런데 블록은 이미 꽉 차 있었어. 안 들어가."
"그럼 오라클이 어떻게 해요?"
"다른 블록으로 몸을 옮겨. 그런데 ROWID는 못 바꿔. 인덱스 여기저기에 그 주소가 적혀 있으니까. 그래서…"
선배가 아침의 문서보관실 이야기를 다시 꺼냈다.
"상자 안의 서류철이 두꺼워져서 그 칸에 안 들어가. 그럼 다른 칸으로 옮기고, 원래 칸에는 쪽지만 남겨. 'B-47 칸으로 옮겼음.' 대장에 적힌 칸 번호는 안 고쳐. 고치려면 대장 전체를 뒤져야 하니까."
"찾는 사람은 두 번 걷겠네요."
"그게 행 이전이야. 그리고 그 쪽지는 진짜로 블록 안에 있어. 보여 줄게."
블록을 열어 본다
선배가 DBA 권한으로 블록 하나를 덤프했다. 이전된 행이 가장 많이 몰린 블록이었다.
seg/obj: 0x11e00 itc: 2 flg: E- typ: 1 - DATA <- 블록 헤더
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.015.0000026b 0x000066ce.0136.07 C--- 0 scn ...
0x02 0x0005.013.00000268 0x00013ae5.0159.3c --U- 100 fsc ...
ntab=1 nrow=100 avsp=0x2 tosp=0xec <- 행 디렉터리
0x12:pri[0] offs=0x1e8c
0x14:pri[1] offs=0x1d80
tab 0, row 3, @...
tl: 268 fb: --H-FL-- lb: 0x2 cc: 3 <- 멀쩡한 행 (268바이트)
tab 0, row 4, @...
tl: 9 fb: --H----- lb: 0x2 cc: 0
nrid: 0x06005a11.3 <- ★ 9바이트짜리 쪽지"위에서부터 보자." 선배가 짚었다. "itc: 2 부분이 ITL이야. 이 블록을 동시에 고칠 수 있는 트랜잭션 수지. 0x02번 칸이 --U-인 건 아직 정리되지 않은 트랜잭션이 행 100개를 잠갔다는 기록이고. 그 칸이 모자라면 어떻게 되는지는 나중에 따로 다룰 거야."
"nrow=100과 pri[n]이 행 디렉터리야. '이 블록의 몇 번째 행은 블록 안 몇 번지에 있다'는 목록이지. ROWID 마지막 조각의 숫자가 바로 이 배열의 첨자야. avsp=0x2는 남은 빈 공간이 2바이트라는 뜻이고. PCTFREE 0으로 만들었으니까."
선배가 마지막 두 줄을 가리켰다. "그리고 여기. 멀쩡한 행은 268바이트에 --H-FL--이야. 머리, 첫 조각, 끝 조각(H, F, L)이 다 있어. 이사 간 행은 9바이트에 --H-----뿐이고, 대신 nrid, 그러니까 다음 주소를 들고 있어."
9바이트짜리 쪽지였다.
그런데 전체 스캔에선 안 보인다
"자, 그럼 이게 얼마나 나쁜지 재 보자. 먼저 전체 스캔." T_MIG00(이전 76,169행)이 3,904, T_MIG_OK(이전 0행)가 3,469였다. 겨우 1.13배였다. 7만 6천 행이 이사를 갔는데도.
"별거 아닌데요?"
"전체 스캔은 어차피 전부 읽거든. 쪽지를 보고 B-47로 걸어갈 필요가 없어. 어차피 B-47도 읽을 거니까." 선배가 말했다. "그럼 인덱스로 찾아가면?"
xplan
-- SELECT /*+ INDEX(m IX_MIG00) */ COUNT(m.PAD) FROM T_MIG00 m WHERE m.ORD_SEQ BETWEEN 1 AND 20000
T_MIG00 : | 2 | TABLE ACCESS BY INDEX ROWID BATCHED | 20000 | 30549 |
|* 3 | INDEX RANGE SCAN | 20000 | 45 |
T_MIG_OK : | 2 | TABLE ACCESS BY INDEX ROWID BATCHED | 20000 | 735 |
|* 3 | INDEX RANGE SCAN | 20000 | 45 |735 대 30,549.
두 숫자를 번갈아 봤다. 같은 SQL, 같은 2만 건, 같은 최종 데이터였다. 인덱스를 뒤지는 부분은 양쪽 다 45블록으로 똑같았다. 테이블로 가는 데서 갈렸다. 41.6배였다.
"하나만 더. 계기판에 이름이 붙어 있어." V$MYSTAT에서 table fetch continued row의 증가분을 쟀다. 쪽지를 보고 한 번 더 걸어간 횟수다.
T_MIG00 조회 후 증가분 : 15252
T_MIG_OK 조회 후 증가분 : 02만 건을 찾는 데 15,252번을 더 걸었다.
"이름이 정직하네요."
"오라클 계기판은 대체로 정직해. 읽을 줄 몰라서 그렇지."
그럼 PCTFREE를 올리면 되는 거 아닌가
다섯 개를 한 줄로 세워 봤다.
| 만든 방식 | UPDATE 전 블록 | 이전행 | 인덱스 경유 Buffers |
|---|---|---|---|
PCTFREE 0 + 나중에 UPDATE | 1,000 | 76,169 | 30,549 |
PCTFREE 10 + 나중에 UPDATE | 1,126 | 73,494 | 29,482 |
PCTFREE 40 + 나중에 UPDATE | 1,630 | 56,984 | 23,083 |
PCTFREE 75 + 나중에 UPDATE | 3,772 | 0 | 786 |
| 처음부터 최종 길이로 적재 | 3,520 | 0 | 735 |
"40을 줘도 절반 넘게 이사 가는데요. 75를 줘야 0이 돼요."
"PCTFREE는 눈금이 아니라 문턱이야." 선배가 말했다. "행이 72에서 270으로 커지면 원래 크기의 2.75배만큼 자리가 더 필요해. 40%로는 턱도 없어. 75%는 줘야 겨우 맞아."
"그럼 75를 주면 되잖아요."
"UPDATE 전 블록 수를 봐." PCTFREE 75짜리는 UPDATE 전에 이미 3,772블록이었고, PCTFREE 0은 1,000블록이었다. 쓰지도 않을 공간을 3.8배나 들고 있었던 것이다.
"적재하고 배치가 돌기 전까지 저 테이블은 네 배 뚱뚱한 채로 조회돼. 그 사이에 누가 전체 스캔을 하면 네 배를 읽어." 선배가 말했다. "그리고 UPDATE 패턴이 바뀌면 그 75도 틀린 숫자가 돼."
"그럼 뭐가 답이에요?"
"대조군을 봐. 처음부터 긴 값을 넣은 쪽. PCTFREE가 0인데 이전도 0이고 735야. 답은 PCTFREE가 아니라 설계야. 상태 코드는 코드 컬럼에 두고, 설명이 필요하면 설명 컬럼을 따로 두는 거지."
"배포 승인이 2주 걸려서 못 했다잖아요."
"그래. 그땐 그게 합리적인 선택이었어. 2주를 기다리느니 있는 컬럼에 넣는 게 낫다고 판단한 거야." 선배가 말했다. "지금 우리가 보는 건 그 판단의 이자고. 그래서 오늘 네가 할 일은 강도현 씨를 욕하는 게 아니라, 이자가 얼마인지 숫자로 적어서 올리는 거야. 그래야 다음에 비슷한 판단을 할 때 2주를 기다릴 근거가 생겨."
⛳ 오늘은 여기까지(중단 지점 2)
Buffers 3,602 → 183과735 → 30,549라는 두 숫자를 손에 넣었다. 오늘 몫은 여기까지만 해도 다 한 것이다. 이어지는 저녁 대화에서 선배가 오늘 고친 것이 왜 다 고친 게 아닌지를 짚고, 14화로 보낼 청구서를 적게 한다.
오후 5시 20분 — 고쳤는데 왜 735가 아닌가
"이전된 행은 어떻게 고쳐요?"
"MOVE. 세그먼트를 새로 쓰면 다 펴져."
sql
ALTER TABLE T_MIG00 MOVE;
ALTER INDEX IX_MIG00 REBUILD;테이블 블록 이전행
---------- ---------- ----------
T_MIG00 3513 0이전행이 76,169에서 0이 됐다. 블록도 3,898에서 3,513으로 줄어, 대조군(3,520)보다도 작았다.
조회를 돌렸다. 735가 나올 줄 알았다.
xplan
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | T_MIG00 | 20000 | 10405 |약 1만. 735가 아니었다.
"……왜요? 이전은 0인데요."
"MOVE가 뭘 하는지 다시 생각해 봐." 선배가 말했다. "지금 놓여 있는 걸 순서대로 읽어서 새 세그먼트에 쌓아. 그럼 묻자. 지금 놓여 있는 순서가 뭐지?"
생각해 봤다. 7만 6천 행이 이사를 갔다. 이사 간 순서대로, 빈자리가 나는 대로.
"…뒤죽박죽이요."
"그 뒤죽박죽을 그대로 굳힌 거야." 선배가 숫자 하나를 짚어 줬다. 클러스터링 팩터였다. 02화에서 T_ORD_BIG이 100만이었던 그 숫자다.
INDEX_NAME CLUSTERING_FACTOR
------------ -----------------
IX_MIG00 52206
IX_MIG_OK 3449"대조군은 3,449야. 테이블 블록 수(3,520)랑 거의 같지. 최선이야. MOVE한 쪽은 5만이 넘어. 15배야."
"이전은 고쳤는데 순서는 못 고쳤네요."
"MOVE는 모양을 고치지 순서를 고치지는 않아. 순서까지 고치려면 ORDER BY를 줘서 새로 쌓아야 돼." 선배가 말했다. "근데 그건 04화에서 한 얘기고, 거기서 네가 이미 봤을 거야. 오늘은 여기까지만."
수첩에 적었다. 행 이전을 MOVE로 고쳐도, 그 과정에서 망가진 클러스터링 팩터는 돌아오지 않는다.
그리고 MOVE로도 못 고치는 게 하나 있다
"마지막으로 하나만." 선배가 말했다. "VARCHAR2(4000) 세 개를 진짜로 꽉 채우면 어떻게 될 것 같아?"
4000 곱하기 3이면 12,000바이트다. 블록은 8,192바이트다.
"…안 들어가는데요."
"그럼 어떻게 되는지 봐."
5,000행을 그렇게 넣고 쟀다.
테이블 행수 블록 평균행길이 연결된행 행당블록수
---------- ---------- ---------- ---------- ---------- ----------
T_CHAIN 5000 15197 12022 5000 3.045,000행 전부가 CHAIN_CNT에 걸렸다. 한 행이 블록을 3.04개씩 썼다.
"이건 행 이전이 아니라 행 연결(chaining)이야. 이름은 비슷한데 완전히 달라." 선배가 말했다. "이전은 '빈자리가 없어서 옮긴 것'이니까 PCTFREE로 막거나 MOVE로 펼 수 있어. 연결은 행 자체가 블록보다 커서 쪼갠 거야. 어디로 옮겨도 안 들어가."
그리고 하나를 더 보여 줬다. 같은 테이블에 전체 스캔 두 개를 돌렸다.
COUNT(C1) -- 첫 조각만 읽는다 : Buffers 15,207 . table fetch continued row 0
COUNT(C3) -- 끝 조각까지 읽는다 : Buffers 25,207 . table fetch continued row 9,999첫 컬럼만 읽으면 공짜고, 마지막 컬럼을 읽으면 1만 블록이 더 붙었다.
"그래서 규칙이 하나 나와. 자주 읽는 컬럼을 앞에 둬라." 선배가 말했다. "그리고 그보다 먼저, 한 행이 블록을 넘지 않게 설계해라. 이건 고칠 수단이 없어."
오후 6시 10분 — 청구서는 아직 안 왔다
퇴근 무렵 선배가 내 자리에 왔다. 나는 T1 리포트 3호를 쓰고 있었다. Buffers 3,602 → 183까지 쓴 참이었다.
"좋아. 근데 두 개만 묻자." 선배가 의자를 끌어왔다. "다음 달에 또 지우면 어떻게 돼?"
말문이 막혔다. 이 테이블은 매달 DELETE 배치가 돈다. 그러면 분필 선 아래가 또 텅 비고, 조회는 또 안 빨라지고, 또 SHRINK를 해야 한다.
"…매달 똑같은 일을 하네요."
"그래. 오늘 네가 한 건 치료가 아니라 청소야." 선배가 말했다. "그리고 청소에는 대가가 있어. SHRINK든 MOVE든 행을 전부 다시 써. 그러면 redo가 생기고, 그동안 테이블을 건드려. 월말 배치 창이 좁은 밤에 그걸 끼워 넣어야 해."
커밋 로그가 하나 더 있었다.
2021-03-30 hd.kang
이력 테이블 비대. 월 1회 DELETE 로 6개월 이전 삭제하는 배치 추가.
TRUNCATE 는 WHERE 를 못 쓰므로 불가. 파티션은 유상 옵션이라 승인 못 받음."그때도 옳았어." 선배가 말했다. "TRUNCATE는 쓸 수가 없고, 파티션은 돈이 들어. DELETE 말고 뭐가 있었겠어."
"그럼 지금은 뭐가 달라요?"
"지금은 네가 숫자를 갖고 있어." 선배가 내 리포트를 가리켰다. "'보관을 DELETE로 하면 조회가 안 빨라집니다'는 의견이야. 'Buffers 3,602가 95% 삭제 후에도 3,602였고, SHRINK 후 183이 됐으며, 이 작업은 매달 반복됩니다'는 근거야. 그걸로 파티션 예산을 요청하는 거야."
"파티션이 왜 답이에요?"
"파티션은 조각마다 세그먼트를 따로 가져. 그러니까 6개월 지난 조각은 DELETE가 아니라 조각째 통째로 버릴 수 있어. 분필 선 문제가 애초에 안 생겨." 선배가 일어섰다. "그건 14화에서 할 거야. 그때 오늘 네가 적은 숫자가 근거로 올라가."
두 번째 질문
"그리고 두 번째." 선배가 문 앞에서 돌아섰다. "오늘 T_ORD_LOG를 쟀잖아. T_ORD_BIG은?"
아침에 본 숫자를 떠올렸다. 분필 선 아래 5,923, 행이 든 블록 5,813. 빈 블록이 110개였다.
"110개 있어요."
"그 테이블엔 인덱스가 몇 개야?" 두 개. 02화에서 내가 만든 것들이었다.
"그 인덱스 세그먼트가 얼마나 큰지 봤어?"
SEGMENT_NAME BLOCKS MB
-------------------- --------- ----------
T_ORD_BIG 6016 47
IX_ORD_BIG_DT 2816 22
IX_ORD_BIG_DT_AMT 3328 26테이블이 47MB, 인덱스 두 개가 22 + 26 = 48MB였다. 인덱스가 테이블보다 컸다.
"02화에서 64배를 벌었지. 그 영수증이 저거야." 선배가 말했다. "적재할 때마다 저 48MB를 같이 갱신해. 그게 14화에서 청구돼. 오늘 리포트의 리스크 칸에 저 숫자를 적어 둬."
리포트의 리스크 칸을 봤다. 쓸 게 많았다.
오늘 남은 것
| 무엇 | 값 |
|---|---|
| 고수위선 사건 | 95% DELETE 후 Buffers 3,602 → 3,602 (변화 없음) |
| 회수 | SHRINK SPACE 후 3,602 → 183 |
| 행 이전 | 같은 데이터, 인덱스 경유 735 → 30,549 (41.6배) |
| 행 연결 | 12KB 행 · COUNT(C1) 15,207 vs COUNT(C3) 25,207 |
VARCHAR2(4000) | 저장은 874 = 874 (차이 없음) · 통계 없을 때 Cost 59,755 vs 1,401 |
| T1 리포트 3호 | 공간·보관 정책 리포트 |
오늘 배운 한 문장
DELETE는 행을 지울 뿐 고수위선을 내리지 않는다. 전체 스캔이 읽는 범위는 "행이 어디까지 있나"가 아니라 "어디까지 써 봤나"로 정해진다.
오늘 세 번 틀린 것
- "통계를 안 모아서다": 통계는 멀쩡했다(
NUM_ROWS24,975) - "블록 안이 비었으면 적게 읽는다": 비어도 읽는다(
FS43,123,FULL0) - "
SHRINK SPACE COMPACT면 되겠지": 모으기만 하고 선은 내리지 않는다(3,602 그대로)
다음으로 넘기는 것
- 매달 반복되는
DELETE+SHRINK작업과 파티션으로 가야 하는 이유 → 14화 - 인덱스 2개 48MB의 적재 비용 (02화에서 미뤄 둔 것) → 14화
MOVE로도 돌아오지 않는 클러스터링 팩터: 04화에서 본 그 CF다. 순서까지 고치려면ORDER BY로 다시 쌓아야 한다- 블록 안의 ITL이 모자라면 어떻게 되는가 (오늘은 슬롯이 2칸 있다는 것만 봤다) → 12화 · 13화
다음 →
3_실습과_기록.md에서 오늘 과정을 직접 재현하고, 블록 안을 덤프로 들여다본 뒤 T1 리포트 3호를 쓴다. 개념이 막히면2_파고들기.md를 펼친다.