오라클 Index 모니터링 - 안쓰는 인덱스 찾기 (index monitoring), Foreign Key 주의

Oracle DB 가 오래 운영되다 보면, 인덱스의 수가 점점 많아지는 경향이 있습니다. 많은 경우는 한 테이블에 40개 이상의 인덱스가 걸려있는 경우도 종종 발견됩니다.

이렇게 인덱스가 많아지는 이유는 SQL 쿼리 속도를 빠르게 하려다 보니 Index 만드는 것이 가장 쉬운 방법이기 때문입니다. 게다가 인덱스가 너무 많아서 지우려해도 과거에 만들어진 인덱스가 아직도 사용되고 있는지 알 수 없어서 함부로 지우기도 겁나는 상황입니다.

 

이럴때, 사용할 수 있는 방법이 "인덱스 모니터링" 기능입니다.

 

alter index <인덱스명> monitoring usage;
alter index <인덱스명> nomonitoring usage;

 

※ Oracle 12.2 버전부터는 Index Monitoring 을 걸지 않아도 dba_index_usage 딕셔너리에서 사용유무를 확인할 수 있는 기능이 추가되었습니다. 단, 100% 는 아니고, 샘플링이라 누락될 수 있다고...

 

인덱스가 사용되고 있는지 아닌지 아리까리 할때, 위 alter index ~ monitoring usage; 커맨드로 모니터링을 걸어주면 됩니다.

모니터링을 걸어주면, 아래처럼 dba_object_usage 딕셔너리뷰에 정보가 생성됩니다.

 

monitoring 컬럼이 Yes 로 되어 있고, 언제부터 모니터링을 하고 있는지 start_monitoring 에 시작시간도 나옵니다.

 

해당 유저에서 볼때는 user_object_usage 딕셔너리뷰를 조회하면 됩니다.

11g 이하버전에서는 v$object_usage 딕셔너리뷰에서만 조회가 가능하고, 이건 해당 유저에서만 조회가 가능합니다.

 

아래와 같이 힌트를 주어 Index 를 강제로 사용하도록 실행해 보겠습니다.

 

다시 dba_object_usage 를 조회해보면, 이번에는 USED 컬럼에 값이 나타나는 것을 확인할 수 있습니다.

 

이렇게 USED 컬럼에 YES 로 나오는 것은 사용되는 인덱스이기 때문에 건덜면 클나겠죠~  Don't touch~~

 

이런식으로 모니터링을 걸어놓은지 한달이 넘었는데도 USED 컬럼에 계속 NO 라고 나온다면,

이 경우는 해당 인덱스를 안쓴다고 볼 수 있겠습니다.

 

안쓰는 인덱스라고 확신이 들면, drop index 하면 되겠지만... 좀 불안하죠???

그래서 바로 drop 하지말고, invisible 시켜놓는게 좋습니다.

 

alter index <인덱스명> invisible;
alter index <인덱스명> visible;

 

이게 Oracle 11gR2 버전부터 지원되는 기능인데, 인덱스를 보이지 않게 잠깐 숨겨놓는 것입니다.

분명 인덱스가 존재하기는 하지만, 사용되지는 않습니다.

이렇게 해놓으면 여차해서 다시 필요한 경우, visible 로 바꿔주면 끝입니다.

 

만약, drop index 해버렸다면 create index 커맨드로 인덱스 다시 만드는데 엄청 시간이 오래 걸릴 수도 있는데,

alter index ~ visible 커맨드는 순식간에 처리되기 때문에 장애상황을 방지할 수 있습니다.

 

 

하나더,,,,

주의해야 할 부분이 ... drop 하거나 Invisible 해서는 안되는 인덱스가 PK, FK Index 입니다.

PK (Primary Key) Index는 데이터를 유니크하게 구분해주는 PK Constraint 와 묶여있으니 건덜지 않는게 정신건강에 좋구요~

FK (Foreign Key) Index는 얼핏 없어도 될 것 같지만... 이거 있어야 합니다.

 

FK 컬럼에 인덱스가 없으면, 부모 테이블에 Update, Delete 할때 자식 테이블에 Table Lock 이 발생하기도 하고, 부모 테이블과 자식테이블 Join 할때 Full Table Scan 이 발생하는 등 문제가 많이 발생합니다.

그래서 FK 컬럼 인덱스는 지우지 말고, 오히려 없으면 만들어줘야 합니다.