DevL Archive Tags

오라클 인덱스 분석

28 Feb 2019 Tags:
oracle, db, index, analyze


테이블을 통해 인덱스 조회

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = '테이블명'

BLEVEL 이 4가 넘으면 rebuild

ANALYZE INDEX 인덱스명 COMPUTE STATISTICS;

SELECT BLEVEL FROM USER_INDEXES
WHERE INDEX_NAME = '인덱스명'

PCT_DELETED 20이상이면 rebuild 대상

DISTINCTIVENESS : 인덱스가 만들어진 컬럼의 값이 얼마나 자주 반복되는지 보여주는 값.

ANALYZE INDEX 인덱스명 VALIDATE STRUCTURE;

SELECT DEL_LF_ROWS*100/DECODE(LF_ROWS,0,1,LF_ROWS) PCT_DELETED,
(LF_ROWS-DISTINCT_KEYS)*100/DECODE(LF_ROWS,0,1,LF_ROWS) DISTINCTIVENESS
 FROM INDEX_STATS
  WHERE NAME = '인덱스명';

BALANCE(인덱스 망가진 정도) 20이상이면 rebuild 대상

SELECT
	HEIGHT,
	DEL_LF_ROWS AS "삭제행_DEL_LF_ROWS",
	(DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100 AS BALANCE,
	BLOCKS,
	NAME,
	PARTITION_NAME,
	LF_ROWS,
	LF_BLKS,
	LF_ROWS_LEN,
	LF_BLK_LEN,
	BR_ROWS,
	BR_BLKS,
	BR_ROWS_LEN,
	BR_BLK_LEN,
	DEL_LF_ROWS_LEN,
	DISTINCT_KEYS,
	MOST_REPEATED_KEY,
	BTREE_SPACE,
	USED_SPACE,
	PCT_USED,
	ROWS_PER_KEY,
	BLKS_GETS_PER_ACCESS,
	PRE_ROWS,
	PRE_ROWS_LEN,
	OPT_CMPR_COUNT,
	OPT_CMPR_PCTSAVE
FROM
	INDEX_STATS;

Recent Posts