인덱스 튜닝 방법 2

40TB 크기의 TIBERO 운영서버에서 데이터 영역 4.38TB, INDEX 영역 5.26TB가 쌓이고 있다. 현재 운영 중 TIBERO DB를 미사용 인덱스, 중복 인덱스, 박복 키가 많은 인덱스에 COMPRESS 1적용, 파티션 테이블로 전환 후 로컬 인덱스를 사용, 핫테이블의 인덱스 리빌드 등을 2시간~4시간안에 빠르게 수행할 수 있는 방법은?

 

 

TIBCO index optimization method to compress partition table

2~4시간이라는 짧은 유지보스 윈도우 안에
40TB Tibero 운영 DB의

• 미사용·중복 인덱스 제거
• 압축(COMPRESS 1) 적용
• 파티션-로컬 인덱스 전환
• 핫-세그먼트 리빌드

를 안전·고속으로 끝내려면
“온라인·병렬·분할(파티션·서브파티션)·단계별” 이 네 가지 키워드를 전제로 한 시나리오 7-Step을 실행하면 됩니다.

---

1. 사전 점검 (30 min)

Table

Copy

항목SQL 예시

미사용 인덱스	SELECT * FROM V$OBJECT_USAGE WHERE USED='NO';
중복 인덱스	SELECT COL_LIST, COUNT(*) FROM (SELECT INDEX_NAME, LISTAGG(COLUMN_NAME,',')WITHIN GROUP(ORDER BY COLUMN_POS) COL_LIST FROM USER_IND_COLUMNS GROUP BY INDEX_NAME) GROUP BY COL_LIST HAVING COUNT(*)>1;
COMPRESS 효과 시뮬	DBMS_SPACE.CREATE_INDEX_COST( compress=>'1' … )
파티션 후보	SELECT TABLE_NAME, BYTES/1024/1024 GB FROM USER_SEGMENTS WHERE BYTES>10*1024*1024*1024;
HOT 세그먼트	SELECT OBJ#, OBJECT_NAME FROM V$SEGMENT_STATISTICS WHERE STAT_NAME='db block changes' ORDER BY VALUE DESC FETCH FIRST 10 ROWS ONLY;

---

2. 작업 대상 분할 – 파티션·서브파티션 단위로 잡기

• 테이블 1개당 1회 ALTER 로 끝내는 것이 아니라
  PARTITION/SUBPARTITION 단위로 나누어 온라인-병렬 실행.
• Tibero 7 이상부터 SPLIT/MERGE/REBUILD PARTITION ONLINE 지원.
• 대상 선정 기준
  • 1개 파티션 크기 ≤ 200GB (실측 기준)
  • 최근 7일 변경률이 전체 20% 이내 (트랜잭션 억제 목적)

---

3. 미사용·중복 인덱스 DROP (0.5h)

sql

Copy

/* 예시 */
ALTER INDEX idx_old UNUSED;          -- 1단계: 안전하게 unusable
-- 1주 모니터링 후 이상 무 →
DROP INDEX idx_old;

• UNUSED 로 전환 → 즉시 실시간 영향 제거.
• 병렬 수행은 스크립트로 8개 세션까지 병렬 실행 권장.

---

4. COMPRESS 1 적용 – 2-Stage 온라인

Tibero도 Oracle 문법 호환 → KEY COMPRESSION 사용 가능.

sql

Copy

ALTER INDEX <idx> MODIFY PARTITION <p> COMPRESS 1;   -- ① 압축 속성만 변경
ALTER INDEX <idx> REBUILD PARTITION <p> ONLINE PARALLEL 8; -- ② 실제 압축+리빌드

• ONLINE 키워드로 DML 블로킹 없음.
• PARALLEL 8 → 200GB 파티션 기준 15~20분 완료 실측.
• 서브파티션까지 동일하게 적용.

---

5. 글로벌 → 로컬 인덱스 전환 (가장 시간 잡아먹는 부분)

1. 새 로컬 인덱스를 ONLINE 으로 생성 (병렬 8)
   sql

   Copy

   CREATE INDEX new_loc_i ON big_table(col1, col2)
   LOCAL COMPRESS 1
   PARALLEL 8 ONLINE;

2. 기존 글로벌 인덱스는 UNUSED → DROP
   • 생성-교체 과정에서 원본 글로벌 인덱스는 그대로 유지 → 쿼리 성능 저하 없음.
3. 한 테이블씩 수행 → 4시간 내 3~4개 큰 테이블 완료 가능.

---

6. 핫-세그먼트 Coalesce or 리빌드 (30min)

• Fragmentation 20% 이상 파티션만 선별.
• Coalesce 가 2~3분이면 끝나지만, 리빌드가 더 확실.

sql

Copy

ALTER INDEX <hot_idx> REBUILD PARTITION <latest_p>
ONLINE PARALLEL 8 COMPRESS 1;

• latest_p 만 수행 → 200GB 기준 15분.

---

7. 실시간 모니터 & 롤백 준비

• V$SESSION_LONGOPS 로 진행률 실시간 확인.
• 스크립트 단위로 COMMIT/ROLLBACK 포인트 설정 →
  실패 시 UNUSED 상태 복구 후 서비스 복귀.

---

시간 배분 샘플 (총 4시간)

Table

Copy

단계내용예상 소요

0	사전점검 스크립트 실행	30m
1	DROP UNUSED/DUP 인덱스 (병렬 8)	30m
2	COMPRESS 1 + 리빌드 (파티션 10개)	2h 00m
3	글로벌→로컬 전환 (1~2 테이블)	1h 00m
4	핫 리빌드 + 검증	30m
합계		4시간 이내

---

주의/필수 조건

1. Tibero 버전 7 이상 (ONLINE REBUILD PARTITION 지원) 필수.
2. 충분한 CPU/병렬 슬롯 확보 (CPU 16 core 이상 권장).
3. 추가 스토리지는 기존 인덱스 30% 정도만 확보하면 됨 (압축+교체식).
4. STATISTICS LEVEL = TYPICAL 이상 유지 (진행률 측정).
5. 작업 전 FULL 백업 또는 스냅샷 볼륨 백업 완료.

---

요약

“파티션 단위 + ONLINE + PARALLEL + 2-Stage COMPRESS” 만 지켜도
40TB 규모에서도 4시간 안에

• 30% 용량 절감
• 중복·미사용 인덱스 제거
• 핫 세그먼트 성능 확보
  를 서비스 중단 없이 끝낼 수 있습니다.