[Oracle] 인덱스 사용여부를 모니터링하는 방법

저희 회사는 Oracle 11gR2 버전을 사용하고 있기 때문에 V$OBJECT_USAGE뷰를 사용하여 생성된 인덱스의 사용여부를 모니터링하는 방법을 알아보겠습니다.

1. 모니터링 대상 인덱스 선정
2. 인덱스 모니터링 시작
3. 인덱스 사용여부 확인
4. 인덱스 모니터링 종료

1. 모니터링 대상 인덱스 선정

• DBA_INDEXES 뷰를 통해 인덱스 모니터링을 위한 DDL을 생성합니다. INVALID 인덱스는 모니터링할 수 없어서 VALID 상태의 인덱스만을 대상으로 모니터링합니다.

  SELECT INDEX_NAME, INDEX_TYPE, TABLE_OWNER, TABLE_NAME,
         'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' MONITORING USAGE;' AS MONITORING_DDL, 
         'ALTER INDEX ' || OWNER || '.' || INDEX_NAME || ' NOMONITORING USAGE;' AS NOMONITORING_DDL,
         'SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME = ''' || INDEX_NAME || ''';' AS USED_YN_DML
  FROM   DBA_INDEXES
  WHERE  OWNER = '스키마명'
  AND    STATUS = 'VALID';
  

2. 인덱스 모니터링 시작

• MONITORING_DDL을 실행하여 인덱스의 사용여부를 모니터링하기 시작합니다. V$OBJECT_USAGE뷰에 동일한 인덱스가 등록되어 있으면 기존 모니터링 정보는 삭제되고 다시 등록됩니다.

  ALTER INDEX 스키마.인덱스 MONITORING USAGE;
  

3. 인덱스 사용여부 확인

• 일정기간 인덱스의 사용여부를 모니터링한 후 USED_YN_DML를 실행하여 V$OBJECT_USAGE뷰의 USED 컬럼을 확인합니다. 만일 관리자 계정으로 확인하려면 아래 쿼리를 사용합니다.

  SELECT u.name AS owner,
         io.name AS index_name,
         t.name AS table_name,
         DECODE(BITAND(i.flags, 65536), 0, 'NO', 'YES') AS monitoring,
         DECODE(BITAND(ou.flags, 1), 0, 'NO', 'YES') AS used,
         ou.start_monitoring,
         ou.end_monitoring
  FROM   sys.obj$ io, 
         sys.obj$ t, 
         sys.ind$ i, 
         sys.object_usage ou, 
         sys.user$ u
  WHERE  io.obj# = ou.obj#
    AND  t.obj# = i.bo#
    AND  i.obj# = ou.obj#
    AND  io.owner# = u.user#
    AND  io.name = 'TB_MAIL_INFO_BINDEX'; -- 인덱스명을 대문자로 입력하세요
  

4. 인덱스 모니터링 종료

• NOMONITORING_DDL을 실행하여 인덱스의 사용여부 모니터링를 종료합니다. V$OBJECT_USAGE뷰의 END_MONITORING 컬럼에 모니터링 종료시간이 기록됩니다.

  ALTER INDEX 스키마.인덱스 NOMONITORING USAGE;