주문 인덱스 셋을 압축했더니 하나는 반으로 줄고, 하나는 오히려 커지고, 하나는 에러가 났다

중급DDL버거136 / 700

상황

버거원 DBA가 저장 공간을 줄이려고 운영 주문 테이블 ORD_RANDOM의 인덱스 셋을 압축하는 스크립트를 일요일 09:00에 돌렸다. "두 컬럼짜리는 COMPRESS 2, 한 컬럼짜리는 COMPRESS 1." 결과는 셋이 다 달랐고, 그동안 문을 연 매장의 POS 결제가 몇 초씩 멈췄다는 문의도 들어왔다. 박팀장은 "결과를 설명하고, 고친 스크립트와 다음부터 운영을 막지 않고 미리 재는 방법을 적어 와라"고 했다.

테이블과 인덱스

ORD_RANDOM(운영 주문): 1,000,000행 · 22,414블록 · 순서 없이 쌓였다 · ORD_DT VARCHAR2(8) 730일, 하루 약 1,370건 · ORD_HMS VARCHAR2(6)이 09:00:00 ~ 22:59:59에 고르게 분포 · SHOP_NO 200곳에 고르게 · POS가 주문을 계속 INSERT한다
ORD_RANDOM_PK (ORD_NO): 유일 인덱스 · 리프 약 2,080블록
ORD_RANDOM_X01 (ORD_DT, ORD_HMS): 리프 3,774블록 · 「일별 주문 조회」가 쓴다(첫 화면 1,378블록 가운데 대부분이 테이블 블록, ACC-027)
ORD_RANDOM_X02 (ORD_DT, SHOP_NO): 리프 3,774블록 · 월마감 「가맹점별 주문 건수」가 쓴다(한 달 160블록)
보관본 ORD: 같은 데이터에 같은 정의의 인덱스(ORD_X01, ORD_X02) · 운영 SQL은 쓰지 않는다

원본 SQL

sql

-- 일요일 09:00 DBA 스크립트
ALTER INDEX ORD_RANDOM_PK  REBUILD COMPRESS 1;
ALTER INDEX ORD_RANDOM_X01 REBUILD COMPRESS 2;
ALTER INDEX ORD_RANDOM_X02 REBUILD COMPRESS 2;

실행계획 학습용 예시 · 압축 전 리프 수는 교재 04화 실측 · 압축 뒤 리프 수는 어림값

xplan

[스크립트 결과]
ALTER INDEX ORD_RANDOM_PK  REBUILD COMPRESS 1
  ORA-25194: invalid COMPRESS prefix length value
ALTER INDEX ORD_RANDOM_X01 REBUILD COMPRESS 2      (09:00:02 ~ 09:00:09)
  LEAF_BLOCKS 3,774 → 4,570
ALTER INDEX ORD_RANDOM_X02 REBUILD COMPRESS 2      (09:00:09 ~ 09:00:15)
  LEAF_BLOCKS 3,774 → 1,990

[그 뒤 SQL]
월마감 · 2013년 12월         ORD_RANDOM_X02 INDEX RANGE SCAN          Buffers 160 → 84
일별 주문 조회 · 첫 화면      ORD_RANDOM_X01 + TABLE ACCESS BY ROWID   Buffers 1,378 → 1,379

[09:00:02 ~ 09:00:15 POS 결제 세션]
enq: TM - contention 대기 · 결제 응답 평균 0.4초 → 최대 7초

문제

세 인덱스의 결과가 갈린 이유를 각 인덱스의 컬럼 구성과 값의 되풀이로 설명하시오. 월마감은 반으로 줄었는데 일별 주문 조회는 그대로인 이유도 설명하시오.
고친 스크립트를 작성하시오. POS 결제가 멈춘 이유와, 다음부터 압축할 컬럼 수를 운영을 막지 않고 미리 재는 방법도 설명하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- PK (ORD_NO) : 유일 한 컬럼 인덱스는 압축할 앞부분이 없다 → 손대지 않는다
-- X01 (ORD_DT, ORD_HMS) : 날짜만 되풀이된다 → COMPRESS 1 · 운영 중이므로 ONLINE
ALTER INDEX ORD_RANDOM_X01 REBUILD COMPRESS 1 ONLINE;
-- X02 (ORD_DT, SHOP_NO) : (날짜, 매장) 묶음이 약 6.9번씩 되풀이된다 → COMPRESS 2 (이미 된 것을 그대로 둔다)

-- 다음부터 : 같은 데이터의 보관본 ORD 에서 미리 잰다 (VALIDATE STRUCTURE 는 그 테이블의 DML 을 막는다)
ANALYZE INDEX ORD_X01 VALIDATE STRUCTURE;
SELECT NAME, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE FROM INDEX_STATS;
ANALYZE INDEX ORD_X02 VALIDATE STRUCTURE;
SELECT NAME, LF_BLKS, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE FROM INDEX_STATS;

개선 후 실행계획 학습용 예시 · 압축 전 리프 수는 교재 04화 실측 · 압축 뒤 리프 수는 어림값

xplan

[고친 뒤]
ORD_RANDOM_PK    압축 없음     LEAF_BLOCKS 약 2,080
ORD_RANDOM_X01   COMPRESS 1   LEAF_BLOCKS 4,570 → 약 2,520
ORD_RANDOM_X02   COMPRESS 2   LEAF_BLOCKS 약 1,990 (그대로)

[보관본 ORD · INDEX_STATS]
NAME      LF_BLKS   OPT_CMPR_COUNT   OPT_CMPR_PCTSAVE
ORD_X01      3774                1                 33
ORD_X02      3774                2                 47

모범 답안 서술

키 압축은 정렬된 리프 블록에서 앞쪽 n개 컬럼 값이 되풀이될 때 그 값을 블록마다 한 번만 적고(접두 항목), 행마다 나머지 컬럼과 ROWID만 남긴다. 그래서 압축이 주는 양은 앞부분이 몇 번 되풀이되느냐에 달려 있다. X02 (ORD_DT, SHOP_NO)는 하루 1,370건이 매장 200곳에 나뉘어 (날짜, 매장) 묶음 하나가 약 6.9번 되풀이된다. 접두 항목 하나로 일곱 행 가까이의 날짜와 매장을 덜어 내므로 리프가 3,774 → 1,990블록으로 반쯤 줄었다. 월마감은 X02의 한 달 범위만 읽으므로 160 → 84블록이 됐다. X01 (ORD_DT, ORD_HMS)는 하루 1,370건이 50,400초에 고르게 흩어져 (날짜, 시각) 묶음이 거의 한 번씩만 나온다. COMPRESS 2로는 행마다 접두 항목이 따로 생기고 접두 항목의 머리 몫까지 더해져 오히려 3,774 → 4,570블록으로 커졌다. 되풀이되는 것은 날짜(하루 1,370번)뿐이므로 COMPRESS 1이 맞다(약 2,520블록). ORD_RANDOM_PK는 유일 인덱스다. 유일 인덱스는 압축할 수 있는 컬럼 수가 '컬럼 수 − 1'까지라서, 한 컬럼짜리 PK는 압축할 앞부분이 없고 ORA-25194가 난다. 손대지 않는다. 일별 주문 조회가 그대로인 이유는 1,378블록의 대부분이 테이블 블록이기 때문이다. ORD_RANDOM은 순서 없이 쌓여 행마다 다른 블록을 찾아간다(CF 999,958). 압축은 리프만 줄이고 테이블 랜덤 액세스는 줄이지 못한다. POS 결제가 멈춘 것은 REBUILD를 ONLINE 없이 돌려 다시 만드는 동안 테이블의 DML을 막았기 때문이다(enq: TM - contention). 고친 스크립트는 ONLINE으로 다시 만든다. 다음부터는 압축할 컬럼 수를 미리 잰다. ANALYZE INDEX … VALIDATE STRUCTURE를 돌린 뒤 INDEX_STATS의 OPT_CMPR_COUNT(권하는 컬럼 수)와 OPT_CMPR_PCTSAVE(줄어드는 비율)를 본다. 이 명령은 도는 동안 그 테이블의 DML을 막으므로 운영 ORD_RANDOM이 아니라 같은 데이터의 보관본 ORD에서 잰다. 보관본에서 X01은 1과 33%, X02는 2와 47%가 나온다.

해설

함정: COMPRESS 숫자를 '컬럼 수만큼'으로 정하면 안 된다. 압축은 앞부분이 되풀이될 때만 이득이고, 거의 유일한 묶음을 압축하면 접두 항목 몫만큼 커진다.
같은 (ORD_DT, …) 인덱스라도 뒤 컬럼의 종류 수에 따라 답이 다르다. 매장(200곳)은 날짜와 묶어도 되풀이되지만, 시각(50,400가지)은 그렇지 않다.
압축으로 리프가 줄면 인덱스 범위를 넓게 읽는 SQL(월마감, INDEX FAST FULL SCAN)이 빨라진다. 한두 건을 찾는 SQL이나 테이블을 많이 찾아가는 SQL에는 효과가 거의 없다.
컬럼 수를 오라클이 고르는 고급 압축(COMPRESS ADVANCED LOW)도 있지만 Advanced Compression 옵션이 필요하다. 접두 압축(COMPRESS n)은 옵션 없이 쓸 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 압축 전 리프 수는 교재 04화 실측 · 압축 뒤 리프 수는 어림값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 압축 전 리프 수는 교재 04화 실측 · 압축 뒤 리프 수는 어림값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황