인덱스 사용 여부 확인

 

차세대 프로젝트 또는 운영 시스템에서 사용하지 않은 인덱스가 간혹 있을 것이다.

그래서 그 미사용되고 있는 인덱스를 체크, 관리 하기 위한 방법을 정리해본다.

 

 

 

1. 인덱스 설계

: 프로젝트 중 인덱스를 보통 아래 상황에서 생성 / 변경 하게 된다.

 

1) 튜닝 요청 : SQL 튜닝 중

2) 모니터링 : SQL 튜닝 중

3) SQL 소스 분석 : 차세대 프로젝트에서는 풀 소스를 보고 100% 있어야 하는 곳은 미리 생성해두기도 한다.

 

 

 

 

 

2. 인덱스 사용여부 주기적 확인

: 생성된 인덱스가 사용되고 있는지 어느 시점에는 체크 해 볼 필요가 있다.

설계 당시에는 당연히 사용되었으나, SQL 변경으로 미사용 될 가능성이 있기 때문이다.

아래는 INDEX 사용여부를 체크하는 3가지 방법이다.

 

 

1) GV$SQL_PLAN

: SQL 실행 플랜에서 INDEX를 이용한 내역 찾기이다. 자주 사용되지 않으면 AGE OUT 되는 경향이 있다.

SELECT * FROM V$SQL_PLAN WHERE OBJECT_NAME = 'PK_TB_EPL_LEAGUE_RANK';
SELECT * FROM V$SQL_PLAN WHERE OBJECT_NAME = 'IX_TB_EPL_LEAGUE_RANK_01';

 

 

2) DBA_HIST_SQL_PLAN

: AGE OUT된 상태에서 Snapshot 캡쳐가 되면 없는 경우가 발생된다.

SELECT * FROM DBA_HIST_SQL_PLAN WHERE OBJECT_NAME = 'PK_TB_EPL_LEAGUE_RANK';
SELECT * FROM DBA_HIST_SQL_PLAN WHERE OBJECT_NAME = 'IX_TB_EPL_LEAGUE_RANK_01';

 

 

3) DBA_OBJECT_USAGE

: 가장 명확한 방법이다. 인덱스에 모니터링을 걸고 사용 여부를 체크 하는 방법이다.

1) 모니터링 적용
ALTER INDEX DBADM.PK_TB_EPL_LEAGUE_RANK MONITORING USAGE;
ALTER INDEX DBADM.IX_TB_EPL_LEAGUE_RANK_01 MONITORING USAGE;

2) 점검을 통해 사용 여부 체크 
SELECT * FROM DBA_OBJECT_USAGE WHERE OWNER = 'DBADM';

3) 사용되는 인덱스는 모니터링 끄기
ALTER INDEX DBADM.PK_TB_EPL_LEAGUE_RANK NOMONITORING USAGE;
ALTER INDEX DBADM.IX_TB_EPL_LEAGUE_RANK_01 NOMONITORING USAGE;

 

 

 

 

 

 

3. 인덱스 사용여부 결정

 

위 2번의 3가지 방법을 복합적으로 활용하여, 미사용되는 인덱스를 어떻게 할지 결정해야한다.

보통 삭제시 문제 발생 가능성이 있을 수 있으므로, 아래와 같이 invisible 처리 할 것을 권장 한다.

ALTER INDEX INDEX명 INVISIBLE;

 

insivible 인덱스는 이용하지 못할뿐이지, 테이블에 데이터가 들어오면 invisible된 인덱스에도 데이터가 저장된다.

그래서 혹여 invisible된 인덱스로 성능 이슈가 발생하면 visible 처리하면 대응이 쉽게 된다.

ALTER INDEX INDEX명 VISIBLE;

 

 

 

 

 

 

4. 개인적인 사용방법

1) GV$SQL_PLAN과 DBA_HIST_SQL_PLAN으로 사용하는 인덱스 체크

2) 위 1)에서 체크되지 못한 인덱스 MONITOR 진행

- DBA_OBJECT_USAGE 일일 점검하여 사용하는 인덱스 MOINTOR 중지

3) 장시간 모니터 결과 동일하게 미사용 인덱스는 INVISIBLE 처리

4) 충분한 기간 동안 두기-> DBA_OBJECT_USAGE 확인 -> 미사용 인덱스 -> DROP

 

※ 중요사항

: 주관적인 판단만으로 미사용 인덱스로 결정하여 INDEX를 바로 INVISIBLE 처리하는 방법은 위험하다.

SQL에 /*+ USE_INVISIBLE_INDEXES(A) */ 힌트를 넣고 INVISIBLE된 인덱스를 이용하는 방법이 있기 때문이다.

 

차세대 프로젝트는 표준 준수가 엄격하므로 99.9% 없으나, 운영 시스템에서는 위 힌트가 존재할 가능성이 높다.

그러므로 개인적인 판단이 아닌 DPV, AWR, MONITOR로 체크를 반드시 해야만 한다.

( 위 힌트로 인덱스를 사용하면 반드시 3가지 방법에서 확인 가능함. Ageout 된 상태면 1) 2)번 확인 불가)

 

물론 INVISIBLE 로 인한 문제 발생시 VISIBLE 처리하면 문제가 해결 될 것이다.

그러나 그 뒤 불필요한 잡음을 듣는 스트레스가 발생 할 수 있으므로 단계적으로 접근하는 방법이 옳다고 생각한다.

 

 

 

 

 

[ 예제 - 스크립트 ]

 

CREATE TABLE TB_EPL_LEAGUE_RANK

(

RANK NUMBER

, TEAM_NAME VARCHAR2(100)

, MATCHES NUMBER

, POINTS NUMBER

, WINS NUMBER

, DRAWS NUMBER

, LOSSES NUMBER

, GOALS_FOR NUMBER

, GOALS_AGAINST NUMBER

, GOALS_DIFFERENCE NUMBER

, CREATED DATE

);

 

insert into TB_EPL_LEAGUE_RANK VALUES ( '1','리버풀 FC', '7', '18', '6', '0', '1', '13', '2', '11',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '2','맨체스터 시티 FC', '7', '17', '5', '2', '0', '17', '8', '9',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '3','아스널 FC', '7', '17', '5', '2', '0', '15', '6', '9',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '4','첼시 FC', '7', '14', '4', '2', '1', '16', '8', '8',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '5','애스턴빌라 FC', '7', '14', '4', '2', '1', '12', '9', '3',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '6','토트넘 홋스퍼 FC', '8', '13', '4', '1', '3', '14', '8', '6',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '7','브라이턴 앤 호브 알비온 FC', '7', '12', '3', '3', '1', '13', '10', '3',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '8','뉴캐슬 유나이티드 FC', '7', '12', '3', '3', '1', '8', '7', '1',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '9','풀럼 FC', '7', '11', '3', '2', '2', '10', '8', '2',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '10','노팅엄 포레스트 FC', '7', '10', '2', '4', '1', '7', '6', '1',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '11','브렌트퍼드 FC', '7', '10', '3', '1', '3', '13', '13', '0',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '12','AFC 본머스', '7', '8', '2', '2', '3', '8', '10', '-2',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '13','맨체스터 유나이티드 FC', '7', '8', '2', '2', '3', '5', '8', '-3',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '14','웨스트햄 유나이티드 FC', '8', '8', '2', '2', '4', '10', '11', '-1',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '15','레스터 시티 FC', '7', '6', '1', '3', '3', '9', '12', '-3',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '16','에버턴 FC', '7', '5', '1', '2', '4', '7', '15', '-8',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '17','입스위치 타운 FC', '7', '4', '0', '4', '3', '6', '14', '-8',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '18','크리스탈 팰리스 FC', '7', '3', '0', '3', '4', '5', '10', '-5',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '19','사우샘프턴 FC', '7', '1', '0', '1', '6', '4', '15', '-11',SYSDATE);

insert into TB_EPL_LEAGUE_RANK VALUES ( '20','울버햄튼 원더러스 FC', '7', '1', '0', '1', '6', '9', '21', '-12',SYSDATE);

 

CREATE UNIQUE INDEX PK_TB_EPL_LEAGUE_RANK ON TB_EPL_LEAGUE_RANK ( RANK );

ALTER TABLE TB_EPL_LEAGUE_RANK ADD CONSTRAINT PK_TB_EPL_LEAGUE_RANK PRIMARY KEY ( RANK );