[ORACLE] INDEX_STATS : 인덱스 내부 구조를 파헤치는 핵심 도구

[ORACLE] INDEX_STATS

 

[ORACLE] INDEX_STATS 뷰 : 인덱스 내부 구조를 파헤치는 핵심 도구

Oracle 데이터베이스 성능 최적화를 위해서는 인덱스의 상태를 심도 있게 분석하는 것이 중요합니다. 그중에서도 INDEX_STATS 뷰는 전문가 관점에서 독창적이고 특별한 가치를 지닌 도구입니다. 타인의 글에서는 흔히 다루지 않는 인덱스 블록 단위 구조, 압축 가능성, 반복 키 정보 등 심도 있는 내부 통찰을 제공하며, 특히 데이터베이스 튜닝 전문가에게 매우 유용합니다.

1. INDEX_STATS란 무엇인가?

`ANALYZE INDEX ... VALIDATE STRUCTURE OFFLINE` 명령을 실행하면, Oracle은 해당 인덱스의 내부 구조를 분석한 결과를 INDEX_STATS 뷰에 기록합니다. 이 뷰에는 B-트리 구조의 깊이, 리프 블록 수, 삭제된 키 수, 압축 정보 등 인덱스 세부 통계가 포함됩니다. 

2. 주요 컬럼과 해석 포인트

아래 표는 INDEX_STATS에서 주목해야 할 주요 컬럼들을 요약한 것입니다:

컬럼명	설명
HEIGHT	B-트리의 높이 (루트부터 리프까지 레벨 수)
LF_ROWS / LF_BLKS	리프 레벨의 키 수와 리프 블록 수
DEL_LF_ROWS	삭제된 리프 레코드 수 (인덱스 단편화를 나타냄)
PCT_USED	할당된 B-트리 공간 중 실제 사용 비율
OPT_CMPR_COUNT / OPT_CMPR_PCTSAVE	압축을 통한 최적 칼럼 길이 및 공간 절약률
BLKS_GETS_PER_ACCESS	인덱스 스캔 시 예상 일관성 모드 블록 읽기 횟수

3. INDEX_STATS 활용의 장점

• 인덱스 높이가 높거나 삭제된 리프 레코드 비율이 높을 경우, 성능 저하나 공간 낭비 요소를 즉시 파악할 수 있음.
• PCT_USED와 압축 정보(OPT_CMPR_*)를 통해 리빌드 또는 압축 여부를 판단하는 자료로 활용 가능.
• BLKS_GETS_PER_ACCESS는 쿼리 성능 예측 및 최적화에 직접 도움.
• ANALYZE INDEX VALIDATE STRUCTURE 실행 시 실시간 상태를 분석할 수 있어, 기존 오라클 통계보다 깊이 있는 내부 구조 분석이 가능.

4. INDEX_STATS vs ALL_IND_STATISTICS – 통계의 종류 비교

뷰 이름	제공 통계	활용 목적
INDEX_STATS	인덱스 내부 구조 (HEIGHT, LF_ROWS, DEL_LF_ROWS, PCT_USED 등)	인덱스 구조 튜닝, 리빌드/압축 결정, 단편화 상태 분석
ALL_IND_STATISTICS (DBMS_STATS)	BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, LAST_ANALYZED 등	옵티마이저용 통계 및 쿼리 계획 품질 향상

 

5. 실제 분석 시나리오

1. 인덱스 분석 명령 실행:

   ANALYZE INDEX idx_name VALIDATE STRUCTURE;

2. 분석 결과 확인:

   SELECT HEIGHT, DEL_LF_ROWS, PCT_USED, OPT_CMPR_PCTSAVE
   FROM INDEX_STATS;

3. 예시 해석
• HEIGHT가 5 이상이면 B-트리가 너무 깊어 인덱스 탐색 비용 증가 가능.
• DEL_LF_ROWS 비율이 LF_ROWS 대비 20% 이상이면 단편화가 심각하므로 리빌드 고려
• PCT_USED가 낮고 OPT_CMPR_PCTSAVE가 30% 이상이라면 압축 적용을 통해 공간 절약 가능.

6. 주의사항

• INDEX_STATS는 오직 최근에 실행한 `ANALYZE INDEX ... VALIDATE STRUCTURE`의 결과만 보관합니다. 즉, 각 세션에서 개별 인덱스 단위로 덮어씌워집니다. 
• 세션 종료 시 데이터가 사라지는 경우도 있어, 지속적인 저장이 필요하면 별도 테이블에 결과를 기록해야 합니다.
• ANALYZE 명령은 옵티마이저용 통계를 쌓는 DBMS_STATS 방식보다 비용이 크므로, 구조 검증 목적에 한해 사용해야 합니다.

7. 결론

INDEX_STATS 뷰는 Oracle 인덱스 내부의 구조적 효율성과 단편화 상태, 압축 가능성 등을 정밀하게 분석할 수 있는 전문가용 통계 도구입니다. 리빌드나 압축 전략 수립, 인덱스 유지보수를 체계적으로 수행하는 데 강력한 기반이 됩니다. 단, 임시적인 결과 저장 특성과 비용을 고려해 적절한 시점에 신중하게 활용해야 합니다.

참고 문헌

• Oracle 공식 문서: INDEX_STATS 컬럼 정의 및 ANALYZE INDEX VALIDATE STRUCTURE 사용법 
• Navicat 블로그: 인덱스 리빌드 판단 기준 (높이, 삭제된 리프 레코드 비율 등) 
• RelationalDBDesign 튜닝 가이드: INDEX_STATS를 통한 공간 사용 효율 모니터링 방법
• StackOverflow: INDEX_STATS가 세션별로 덮어씌워지는 특성 논의